B4J Question JRDC2 query and parameter design for 2 databases

PumaCyan · May 22, 2023

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?

Sample Query:

SELECT a.field1, a.field2, a.field3
FROM database_A.table A, database_B.table B
WHERE a.field1 = b.field1;

DarkoT · May 22, 2023

PumaCyan said:
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?

Sample Query:

SELECT a.field1, a.field2, a.field3 FROM database_A.table A, database_B.table B WHERE a.field1 = b.field1;

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

Example code:

select *
from [database1].dbo.mytable a inner join [database2].dbo.maytable b on a.myfield = b.myfield

DonManfred · May 22, 2023

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 · May 22, 2023

PumaCyan said:
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?

Sample Query:

SELECT a.field1, a.field2, a.field3 FROM database_A.table A, database_B.table B WHERE a.field1 = b.field1;

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

Example code:

select *
from [database1].dbo.mytable a inner join [database2].dbo.maytable b on a.myfield = b.myfield

DonManfred · May 22, 2023

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

you can use this in B4J too?

EnriqueGonzalez · May 22, 2023

DonManfred said:
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 · May 22, 2023

EnriqueGonzalez said:
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 · May 22, 2023

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

in theory it is possible if you link them:

sp_addlinkedserver (Transact-SQL) - SQL Server

sp_addlinkedserver (Transact-SQL)

learn.microsoft.com

DonManfred said:
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 · May 23, 2023

DonManfred said:
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 · May 23, 2023

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

Example code:

select * from [database1].dbo.mytable a inner join [database2].dbo.maytable b on a.myfield = b.myfield

i am using mysql as database

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

EnriqueGonzalez · May 23, 2023

PumaCyan said:
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 · May 23, 2023

EnriqueGonzalez said:
in theory it is possible if you link them:

sp_addlinkedserver (Transact-SQL) - SQL Server

sp_addlinkedserver (Transact-SQL)

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:

Example:

SELECT *
FROM   openquery
        (
        MYSQL,
        '
         SELECT *
         FROM [mysqlDb].[myTable]
        '
        )

PumaCyan · May 24, 2023

thanks all for the help, problem solved

B4J Question JRDC2 query and parameter design for 2 databases

PumaCyan

Member

DarkoT

DonManfred

Expert

DarkoT

Active Member

DonManfred

Expert

EnriqueGonzalez

Well-Known Member

DonManfred

Expert

EnriqueGonzalez

Well-Known Member

sp_addlinkedserver (Transact-SQL) - SQL Server

PumaCyan

Member

PumaCyan

Member

EnriqueGonzalez

Well-Known Member

DarkoT

Active Member

sp_addlinkedserver (Transact-SQL) - SQL Server

PumaCyan

Member

Similar Threads

B4J Question JRDC2 query and parameter design for 2 databases

Member

Expert

Active Member

Expert

Well-Known Member

Expert

Well-Known Member

Member

Member

Well-Known Member

Active Member

Member

Similar Threads

Privacy & Transparency

Privacy & Transparency