Accessing tables i different schema
Accessing tables i different schema
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.
-
- Posts: 1462
- Joined: Tue Jan 24, 2017 5:51 am
- Location: 'Stralya
Re: Accessing tables i different schema
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.
Re: Accessing tables i different schema
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?
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?
-
- Posts: 2413
- Joined: Mon Jul 02, 2012 12:24 am
- Location: Ulaanbaatar, Mongolia
Re: Accessing tables i different schema
Why don't you just write an API to GET or PUSH the data you want between the apps?
Cheers,
Mark
_________________
AwareIM 6.0, 8.7, 8.8, 9.0 , MariaDB, Windows 10, Ubuntu Linux. Theme: Default, Browser: Arc
Upcloud, Obsidian....
Mark
_________________
AwareIM 6.0, 8.7, 8.8, 9.0 , MariaDB, Windows 10, Ubuntu Linux. Theme: Default, Browser: Arc
Upcloud, Obsidian....
Re: Accessing tables i different schema
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
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
-
- Posts: 2413
- Joined: Mon Jul 02, 2012 12:24 am
- Location: Ulaanbaatar, Mongolia
Re: Accessing tables i different schema
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.
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.
Cheers,
Mark
_________________
AwareIM 6.0, 8.7, 8.8, 9.0 , MariaDB, Windows 10, Ubuntu Linux. Theme: Default, Browser: Arc
Upcloud, Obsidian....
Mark
_________________
AwareIM 6.0, 8.7, 8.8, 9.0 , MariaDB, Windows 10, Ubuntu Linux. Theme: Default, Browser: Arc
Upcloud, Obsidian....
Re: Accessing tables i different schema
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
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
Re: Accessing tables i different schema
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
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
Re: Accessing tables i different schema
Thank you all so much! I will check out the options, this gives me hope