trying to connect to multiple database

Applications Database Connections
#1

Hey guys working on a new project, thankfully its not to complex and havent ran into any bugs at long last, well yet. Hope I havent jinxed myself there, anyway am trying to connect to multiple db and am having some trouble hoping somebody can provide a solution…

using Form, ajax event, have a select menu when change to add data from another db into field.

this is what I have so far…

/**

  • Selecting a field from another table - db to select
    */
    $check_db = “conn_mysql_1”;

/**

  • Selecting a field from another table
    */
    $check_sql = “SELECT providerID, providerRef”
    . " FROM providers"
    . " WHERE providerID = ‘" . {propProvider} . "’";
    sc_lookup(rs, $check_sql, $check_db);

if (isset({rs[0][0]})) // Row found
{
{propProviderRef} = {rs[0][1]};
}
else // No row found
{
{propProviderRef} = “Nothing”;
}

its just throwing errors, from what I have gathered from other examples this should be possible, or am I mistaken???

Any help would be aprechiated

#2

as much as I am not surprised that there is no reply yet, I could really REALLY do with one :slight_smile:
Would make my day if I could actually complete a project with this darn tool :stuck_out_tongue:

#3

Sorry Jamie, I can’t answer this question as I never have used more connections in the same project.

#4

Cant you uses federated tables? That would keep you in one single connection.
I havent tried it myself yet but apparent that is just a remote database link (as it is called in oracle).
https://dev.mysql.com/doc/refman/5.0/en/federated-use.html

#5

Hi,
there is no problem using multiple connections in a project.
I think the quotation marks probably don’t match, i didn’t count though.
Try it plain and simple:
I suppose {propProvider} is an integer field. If not your have to use ‘{propProvider}’

sc_lookup(rs,“SELECT providerID, providerRef FROM providers WHERE providerID = {propProvider}”,“conn_mysql_1”);

That should do it.

jsb

#6

Be careful to use the automatic lookup to decode fields … if you are using a database other than the one associated with the main form will have errors in compilation. A ticket was opened months ago but no solution so far…

#7

Hi JamieO,

I resolve this problem using MySql database,

I develop my project using ONLY two pre-defined connections, one for login and other for other applications (in develop environment)

In production environment
in my login form I choose the company number, ( 01 02 03 etc…) then I construct global variables as
“db_NN.tb_” as [gl_tb] for tables where NN is the company number
“db_NN.cs_” as [dl_cs] for querys
“db_NN” as [gl_db] for code

My tables are named as tb_clients, tb_items, etc…
My querys are named as cs_clients, cs_items, etc…

THEN in every scriptcase applications, in SQL tab I put by example the table tb_clients, the variable for replace table name [gl_tb] and table name part to replace “tb_”
scriptcase replace the word “tb_clients” by “db_NN.tb_clients”

this work in scriptcase applications

BUT in php code you use the other global variable, by example

select * from [gl_db].tb_clients (mysql execute as “select * from db_NN.tb_clients”)

update name in [gl_db].tb_clients where cli_id = 5

with this feature you do not define any other connection, only must be create the physical databases in the server, its work !!

Aquiles

#8

[QUOTE=jsbinca;22153]Hi,
there is no problem using multiple connections in a project.
I think the quotation marks probably don’t match, i didn’t count though.
Try it plain and simple:
I suppose {propProvider} is an integer field. If not your have to use ‘{propProvider}’

sc_lookup(rs,“SELECT providerID, providerRef FROM providers WHERE providerID = {propProvider}”,“conn_mysql_1”);

That should do it.

jsb[/QUOTE]

Nice will give that a try :slight_smile:

To others thanks, but I have to use multiple DB, the platform I am working on will eventually manage the database for 10 different websites including one main site and DB which will contain relevant information to be shared between all sites and dbs.

Will update the thread once and if I have it solved for others in the future :slight_smile:
thanks again!

#9

sweet working a treat! thanks very much!!!

Will finish my first platform nearly one year after purchase lol

Thanks again!

#10

How do you make query with 2 different database and 2 different host ?

#11

sorry saohong, am not sure I have all database on the same server, as I host all sites and database just with different domains which is how I am doing this. sorry I cant help much, I havent seen any options either to actualy connect to another server other than the one deployed on.
Would be a nice feature thou to have added, maybe a request to scriptcase to actually manage multiple db on different server, so being able to define the server IP alone should do the trick and I would imagine should be easier enough for them to apply.
No idea how long that would take thou, am still waiting on silly things to be added like jquery number/item sliders…