Accessing tables i different schema

lueu

Is it possible for me to get access to a table on the same server as the ones made in Aware IM, but a different schema, and if so, how? It is a temp table that I am planning to use for some needed relationships. It's created directly in the database (ssms), and it does not "pop up" in the configuration tool.

PointsWell

https://www.awareim.com/dokuwiki/doku.php/docs/3400_how_to/0900_other_systems

lueu

Thanks for the link! Since it's in the same database as the tables made in Aware, I was hoping I could get to it some other way than external🙂 But then I will just have to see if I can live with the limitations.

lueu

Follow up question:
If I need to make it an existing external.. The table has been created both in test and prod AIM database - how to solve this, do I need to connect to just one of them?

customaware

Why don't you just write an API to GET or PUSH the data you want between the apps?

lueu

Mark, I am not too familiar with APIs, what benefits would I get from doing that compared to an external table?
What I am trying to achieve is to fetch som data from an external table because I need to check data in the application against information in this table. It kind of works with external table, but to make sure not to mess up anything in the external table I made a temp table with the same data. Figured I'd then might be able to get to this table without the external percistence, hoping that the table would magically show up in AIM like it does the other way when I create one i AIM :mrgreen:

customaware

Well, by and large I only use External Tables when I want to just read data from the other source.

You can also do the normal CRUD stuff but it is a bit messy for my liking and like you, I would prefer if we
could just use them like normal Aware tables. I understand some others like Bruce are a lot more progressive than
me but he is an MSQL expert so that counts me out.

I don't have an example to send you - else I would - but if I needed to do anything like this nowdays I would go
done the API only path.

We are building native Mobile apps that communicate with Aware solely via REST APIS and it is great and very responsive.
They are a bit fiddly to start with but once you work out how it all fits together then it is basically Set, Rinse and Repeat
for pretty much anything you want.

But, one question that is going to make a difference to you..... The External Table you are wanting to access.... is that in
a different Aware IM Business Space or it it in a completely different system. You mentioned it is a different Schema but I could not
tell if that also is an Aware IM table.

BLOMASKY

And, there is always another way. You can create a VIEW in SQL that combines columns from the aware table(s) and the external one. Depending on the columns you need, you can create a BO in aware with all the result columns then when you want to access / view the data you could write a stored procedure that looks like:

DELETE FROM NewTempBO

INSERT INTO NewTempBO(ID, BASVERSION, BASTIMESTAMP, col1, col2, col3

select next value for BASID_GEN, 1, getdate(), col1, col2, col3
from MyView WHERE ....

Bruce

BLOMASKY

And, of course, there is another way.

Assuming that you can modify the external table. Do the following
0). Rename the external table
1). Create a BO in Aware with the name of the external table and all the columns, When you go to test (or publish) it will create an empty table
2). Select all of the columns from your external table and add them to the new table.
2a) Aware requires 3 column, ID, BASVERSION and BASTIMESTAMP. So when you populate the new BO you will have to assign a unique # to the ID and 1 to BASVERSION and anyold date to BASTIMESTAMP

Now you can access the external table in both aware and the external program. You WILL need a trigger to update the ID column if new records are created by the external program.

Bruce

lueu

Thank you all so much! I will check out the options, this gives me hope 😃