Two tables on different servers

[Atta]

I have a table: table1 from server 1 where I get information from. Another table: table2 from server2 this is a view with totals how many times an item is found in table2

So in table 1, I have MachineName, field2 and field3
In table 2 I have MachineName and Total field.

I need to be able to link these two tables. The field that is similar is machineName.

The issue is that these 2 tables are on different machines with different credentials.
Can anybody give me some hints of how to do this.
Thanks

[Steve Wood]

Re: Two tables on different servers

I don't think you can merge the two databases into a single select statement using Alpha's syntax because you need to use the connection to execute the select statement, as in

dim cn as sql::connection
vSelect = "select * from users"
cn.execute(select)

But you can open each database in turn, execute a select on each, store the results in an array, and then use that array in normal xbasic to 'merge' your information. At the end of the script below you will have your result set in array1 and array2 (arr1, arr2).

dim cn as sql::Connection
dim rs as sql::resultset
dim arr1[0] as p
dim arr2[0] as p

cn.open("::name::conn") ' connection to my first database
vselect = "select * from users"
cn.execute(vselect)
rs = cn.resultset
rs.toPropertyArray(arr1)
cn.close()

cn.open("::name::conn2") ' connection to my second database
vselect = "select * from cart"
cn.execute(vselect)
rs = cn.resultset
rs.toPropertyArray(arr2)
cn.close()

[Atta]

Re: Two tables on different servers

I thought I had it. I have another grid with a SQL view and totals, I drop that into the field section with a linked Grid.

In the bottom of the Linked Content I see a Show/Hide Expression, how can I edit the grid just to show if the field TOTAL is bigger than 0.
Picture to link.PNG
TOTAL is the field in the linked grid.

The grid is linked by Name = MachineName

[Atta]

Re: Two tables on different servers

I don't think you can merge the two databases into a single select statement using Alpha's syntax because you need to use the connection to execute the select statement, as in

dim cn as sql::connection
vSelect = "select * from users"
cn.execute(select)

But you can open each database in turn, execute a select on each, store the results in an array, and then use that array in normal xbasic to 'merge' your information. At the end of the script below you will have your result set in array1 and array2 (arr1, arr2).

dim cn as sql::Connection
dim rs as sql::resultset
dim arr1[0] as p
dim arr2[0] as p

cn.open("::name::conn") ' connection to my first database
vselect = "select * from users"
cn.execute(vselect)
rs = cn.resultset
rs.toPropertyArray(arr1)
cn.close()

cn.open("::name::conn2") ' connection to my second database
vselect = "select * from cart"
cn.execute(vselect)
rs = cn.resultset
rs.toPropertyArray(arr2)
cn.close()

Steve how can I use the variable in the array and link it / show it in the grid?