B4J Question JRDC2 query and parameter design for 2 databases

[PumaCyan]

I have 2 databases
let's assume databases A and B

if I have table data in database A and I want to relate it to table data in database B, how do I design the query in the jrdc2 config file and the parameters that I create in the client application?

[CODE lang="sql" title="Sample Query"]SELECT a.field1, a.field2, a.field3
FROM database_A.table A, database_B.table B
WHERE a.field1 = b.field1;[/CODE]

 

[DonManfred]

Sounds like a bad Databasedesign.

I don´t know if it is possible mit SQLite. With any other Database i´m positive that it will not work (not sure though).

You need to have one odbc-driver which is able to connect to two different databases. I never saw such one.

 

[DarkoT]

I have 2 databases
let's assume databases A and B

if I have table data in database A and I want to relate it to table data in database B, how do I design the query in the jrdc2 config file and the parameters that I create in the client application?

[CODE lang="sql" title="Sample Query"]SELECT a.field1, a.field2, a.field3
FROM database_A.table A, database_B.table B
WHERE a.field1 = b.field1;[/CODE]

Which database will you use? In MS Sql server you need to have code like this:

[CODE lang="sql" title="Example code"]select *
from [database1].dbo.mytable a inner join [database2].dbo.maytable b on a.myfield = b.myfield [/CODE]

 

[DonManfred]

Which database will you use? In MS Sql server you need to have code like this

you can use this in B4J too?

 

[EnriqueGonzalez]

you can use this in B4J too?

yes it is possible, if both (or any number) of databases lives in the same instance is trivial to connect them.

 

[DonManfred]

if both (or any number) of databases lives in the same instance is trivial to connect them

if they are not in the same instance it does not work i guess.
Same is true for any MySQL-Server where the Databases are on the same machine (and have the rights to access the databases).

 

[EnriqueGonzalez]

if they are not in the same instance it does not work i guess.

in theory it is possible if you link them:

sys.sp_addlinkedserver (Transact-SQL) - SQL Server

sp_addlinkedserver creates a linked server, providing access to distributed, heterogeneous queries against OLE DB data sources.

learn.microsoft.com

Same is true for any MySQL-Server where the Databases are on the same machine (and have the rights to access the databases).

yes thats correct, all major databases can do it.

 

[PumaCyan]

Sounds like a bad Databasedesign.

I don´t know if it is possible mit SQLite. With any other Database i´m positive that it will not work (not sure though).

You need to have one odbc-driver which is able to connect to two different databases. I never saw such one.

Yes..
that doesn't sound good, right now I haven't fully mastered proper database design yet..

because right now I need the fastest solution to manage data traffic for a company that has several branches, so there you have it

 

[PumaCyan]

Which database will you use? In MS Sql server you need to have code like this:

[CODE lang="sql" title="Example code"]select *
from [database1].dbo.mytable a inner join [database2].dbo.maytable b on a.myfield = b.myfield [/CODE]

i am using mysql as database

means for the [database] to be created in the form of a parameter?

 

[EnriqueGonzalez]

i am using mysql as database

means for the [database] to be created in the form of a parameter?

No. Just use the name of the database directly. You can't parametize it

 

[DarkoT]

in theory it is possible if you link them:

sys.sp_addlinkedserver (Transact-SQL) - SQL Server

sp_addlinkedserver creates a linked server, providing access to distributed, heterogeneous queries against OLE DB data sources.

learn.microsoft.com

yes thats correct, all major databases can do it.

Just for info... You can basically link as linked server also Ms sql with MySql server over OLE ODBC database. If someone will need instruction, I can help... When is mysql as linked server accessible from ms sql server it’s easy to run query from ms sql to my sql database... Like this:

[CODE lang="sql" title="Example"]SELECT *
FROM openquery
(
MYSQL,
'
SELECT *
FROM [mysqlDb].[myTable]
'
)[/CODE]

 

[PumaCyan]

thanks all for the help, problem solved