Grid across two databases

Applications Database Connections
#1

Good morning,

I’m trying to link a table in Database 1 to a table in Database 2.

DB1 has Table1:

  • Phone_Number
  • Active (“y” or “n”)

DB2 has Table2:

  • Phone_Number
  • Subscriber Information

I need all info from DB2.Table2 where the phone number is active in DB1.Table1.

Creating a grid with the wizard doesn’t allow (or I can’t find a way) to link across the two databases. The SQL builder doesn’t seem to allow this either.
So after multiple attempts, I thought it may be easier to just copy DB1.Table1 to a new table in DB2 each time the grid is called. Not pretty I know, but both of these databases are only updated once a day and we won’t be accessing this grid more than a few times a week.

So in “OnScriptInit” in my grid that’s just tied to DB2.Table2, I have:

sc_exec_sql (“CREATE TABLE SubCopy SELECT Phone_Number, Active FROM DB1.Table1”);

and it fails with:
Error while accessing the database:
ERROR: syntax error at or near “SELECT” LINE 1: CREATE TABLE SubCopy SELECT Phone, … ^
CREATE TABLE SubCopy SELECT Phone_Number, Active FROM DB1.Table1

So I thought maybe it was something to do with trying to connect to the other database, so as a baby step, I tried to just create a table:

sc_exec_sql (“CREATE TABLE SubCopy”);

and it fails with:
Error while accessing the database:
ERROR: syntax error at end of input LINE 1: CREATE TABLE SubCopy ^
CREATE TABLE SubCopy

So I must be missing something here. Maybe there’s a better way to do this? I’m still very new at this and would appreciate any available help with joining tables across two databases.

Thanks in advance!

Mark.

#2

Hi,
I suppose you have working connections to both of the databases (i.e. conn_db1, conn_db2).

Create a grid with all the information you need based on db2-table2, regardless if the phone number is active or not.
In the onScriptInit event of your grid get the data from db1-table1

$phone_sql = “SELECT GROUP_CONCAT(Phone_Number SEPARATOR ‘,’) WHERE Active = ‘y’”;

sc_lookup(phones,$phone_sql,“conn_db1”); // this is where you get the data from the other db.
if(isset({phones[0][0]}))
{
$active_list = “(”.{phones[0][0]}.")";
}

sc_select_where(add) = “WHERE Phone_Number IN $active_list”;

Hope this helps.

jsb

#3

Hi Jsb,

Thanks SO much for your suggestion. I’ve put the code into the OnScriptInit, and I get an error:

Error
Undefined variable: active_list
Error
Error while accessing the database:
ERROR: syntax error at end of input LINE 1: select count() from “conn_db1”.Phone_Number WHERE Phone_Number IN ^
select count() from “conn_db1”.Phone_Number WHERE Phone_Number IN

Using baby steps, I used the first line of your suggestion, and get no errors - reg grid shows fine.
Used the sc_lookup down to the closing } and get no errors - reg grid shows fine
But when I add the last line “sc_select_where(add)”, it throws the above errors.

It may just be a formatting issue on my end, so I’ll play with the database / table names and see if it makes a difference…because I obviously changed the naming convention on this request to simplify it all =)

Thanks again for the quick response / suggestion and I hope you have a great weekend!

Mark.

[QUOTE=jsbinca;22238]Hi,
I suppose you have working connections to both of the databases (i.e. conn_db1, conn_db2).

Create a grid with all the information you need based on db2-table2, regardless if the phone number is active or not.
In the onScriptInit event of your grid get the data from db1-table1

$phone_sql = “SELECT GROUP_CONCAT(Phone_Number SEPARATOR ‘,’) WHERE Active = ‘y’”;

sc_lookup(phones,$phone_sql,“conn_db1”); // this is where you get the data from the other db.
if(isset({phones[0][0]}))
{
$active_list = “(”.{phones[0][0]}.")";
}

sc_select_where(add) = “WHERE Phone_Number IN $active_list”;

Hope this helps.

jsb[/QUOTE]

#4

Hi, please put the last line of code inside the if structure to make sure we have something for the WHERE clause.
Also the error message suggests that there are no values returned by the lookup statement. To check on this put the following line before the if statement.

print_r($phones);

if(isset({phones[0][0]}))
{
$active_list = “(”.{phones[0][0]}.")";
sc_select_where(add) = “WHERE Phone_Number IN $active_list”;
}

jsb

#5

Hey jsb,

I truly appreciate your help and feel a little guilty with the quick responses - thank you!

Ok, so I moved that last line inside the if, and also added the print line just before it. Now I don’t get any errors (great!), but see the regular grid with “all” the Phone_Numbers - whether they’re active or not. At least I’m not getting an error now…but I didn’t see anything different from the print command you had me add - not sure if I was supposed to or not? I “kind” of understand what you’re doing, so I’ll keep trying this and that with your code and see if I can get it to pull correctly. Thanks again for the help!

EDIT: I changed the database name in the first line, and get the same thing - so I probably have the database name formatted incorrectly. I’ll play with that some more =) Cheers!

Mark.

[QUOTE=jsbinca;22240]Hi, please put the last line of code inside the if structure to make sure we have something for the WHERE clause.
Also the error message suggests that there are no values returned by the lookup statement. To check on this put the following line before the if statement.

print_r($phones);

if(isset({phones[0][0]}))
{
$active_list = “(”.{phones[0][0]}.")";
sc_select_where(add) = “WHERE Phone_Number IN $active_list”;
}

jsb[/QUOTE]

#6

Ok, quick question …on your code:

sc_lookup(phones,$phone_sql,“conn_db1”);

is the “conn_db1” the name that I gave Scriptcase for the connection to the database, or is it the name of the database itself? I’ve tried both and still am getting all active numbers - so I suspect I’m not formatting it correctly. No rush, if at all! You’ve been very helpful already so thank you.

Also, as I’ve researched it more, it may be a Postgres issue with the Group_Concat command. I found on StackOverflow where someone was asking for the equivalent of Group_Concat in Postgres. If that’s the case, I’m sorry for not mentioning that both databases are Postgres…argh!

#7

As of your first post, conn_db1 is the connection for Scriptcase to Database 1, you also should have a second connection conn_db2 to Database 2.
Since you are using Postgres we’ll take a different approach.

$phone_sql = “SELECT Phone_number FROM Table1 WHERE Active = ‘y’”;
sc_lookup(phones,$phone_sql,“conn_db1”);
if(count($phones) > 0)
{
$a_phones = array_map(function($item) { return $item[0]; } , $phones);
$active_list = “(”.implode(’,’,$a_phones).")";
sc_select_where(add) = “WHERE Phone_Number IN $active_list”;
}

jsb

#8

Wow, from a noob’s standpoint, it’s amazing at how you come up with that. I am still getting an error, but completely different and gives me hope again <grin> It includes a loooong string of phone numbers, and when I “search” the string, it only has active ones!

Error
Error while accessing the database:
ERROR: column “Phone_number” does not exist LINE 1: select count() from “public”.Phone_number WHERE Phone_number IN (999999… ^
select count() from “public”.Phone_number WHERE Phone_number IN (9999990037, …[and lists hundreds of numbers…but all active ones!]

Again, I’m guessing I’ve formatted something wrong in the query - especially with the “public.” prefix in front of all the tables (which make no sense to me). I have an appointment I have to get to, so will continue playing over the weekend or on Monday. Thanks again for your help jsb, and I hope you have a great weekend!

Mark.

EDIT: Got it! I know it’s Saturday, but it’s been hanging over my head - I hate problems that I know can be fixed… I wasn’t seeing that the last reference to Phone_Number should actually be referencing DB2…duh! So I edited that field to include the table name too (Table2.Phone_Number) and voila…works like a champ. Thanks again for all your help! Now on to making this thing pretty…

[QUOTE=jsbinca;22244]As of your first post, conn_db1 is the connection for Scriptcase to Database 1, you also should have a second connection conn_db2 to Database 2.
Since you are using Postgres we’ll take a different approach.

$phone_sql = “SELECT Phone_number FROM Table1 WHERE Active = ‘y’”;
sc_lookup(phones,$phone_sql,“conn_db1”);
if(count($phones) > 0)
{
$a_phones = array_map(function($item) { return $item[0]; } , $phones);
$active_list = “(”.implode(’,’,$a_phones).")";
sc_select_where(add) = “WHERE Phone_Number IN $active_list”;
}

jsb[/QUOTE]