CrossDatabase sql query using two database handles

Just starting out? Need help? Post your questions and find answers here.
LiK137
Enthusiast
Enthusiast
Posts: 279
Joined: Wed Jun 23, 2010 5:13 pm

CrossDatabase sql query using two database handles

Post by LiK137 »

Hi,
I want to run following sql query based on two differend databases as below:

Code: Select all

hDB1Query =   
"CREATE TABLE table1 (id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, str_value nvarchar(50), comnfild varchar(8));"

hDB1Query + #CRLF$ +
"INSERT INTO table1 (id, str_value, comnfild) VALUES (1, 'Value 1', 'aaaa1111'), (2, 'Value ?', 'aaaa1122'), (3, 'Value 3', 'aaaa1145');


hDB2Query =   
"CREATE TABLE table2 (id2 INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, str_value2 nvarchar(50), comnfild2 varchar(8));"

hDB2Query + #CRLF$ +
"INSERT INTO table2 (id2, str_value2, comnfild2) VALUES (1, 'Value 1', 'aaaa1111'), (2, 'Value 2', 'aaaa1133'), (4, 'Value 4', 'aaaa1145');"

hDBCrossQery = 
"insert into table1(str_value, comnfild) 
select a.str_value2, a.comnfild2 from table2 a left join table1 b on a.comnfild2 = b.comnfild where b.comnfild is NULL; " + #CRLF$ +
"insert into table2(str_value2, comnfild2) 
select a.str_value, a.comnfild from table1 a left join table2 b on a.comnfild = b.comnfild2 where b.comnfild2 is NULL;"
hDB1Query is run on Database1 and consequently hDB2Query is run on Database2 which are opened with different database handle.

how to run hDBCrossQery which affects both databases natively based on sql without walking each record using PureBasic Database commands like "While NextDatabaseRow"
User avatar
Kiffi
Addict
Addict
Posts: 1353
Joined: Tue Mar 02, 2004 1:20 pm
Location: Amphibios 9

Re: CrossDatabase sql query using two database handles

Post by Kiffi »

Code: Select all

UseSQLiteDatabase()

#Database = 0

; Create Sample-Database No 1

Database1Filename.s = GetTemporaryDirectory() + "database1.db"
CreateFile(0, Database1Filename) : CloseFile(0)
OpenDatabase(#Database, Database1Filename, "", "", #PB_Database_SQLite)
DatabaseUpdate(#Database, "Create Table SuperHeroes (name TEXT, realname TEXT)")
DatabaseUpdate(#Database, "Insert Into SuperHeroes (name, realname) Values ('Spiderman', 'Peter Parker')")
CloseDatabase(#Database)

; Create Sample-Database No 2

Database2Filename.s = GetTemporaryDirectory() + "database2.db"
CreateFile(0, Database2Filename) : CloseFile(0)
OpenDatabase(#Database, Database2Filename, "", "", #PB_Database_SQLite)
DatabaseUpdate(#Database, "Create Table SuperHeroes (name TEXT, realname TEXT)")
DatabaseUpdate(#Database, "Insert Into SuperHeroes (name, realname) Values ('Batman', 'Bruce Wayne')")
CloseDatabase(#Database)

; Open Sample-Database No 1

OpenDatabase(#Database, Database1Filename, "", "", #PB_Database_SQLite)

; ... and attach Sample-Database No 2

DatabaseUpdate(#Database, "ATTACH DATABASE '" + Database2Filename + "' AS Database2")

; now we can do a query over both databases:

DatabaseQuery(#Database, "Select * From SuperHeroes Union All Select * From Database2.SuperHeroes")

While NextDatabaseRow(#Database)
  Debug GetDatabaseString(#Database, 1) + " is " + GetDatabaseString(#Database, 0)
Wend
Hygge
LiK137
Enthusiast
Enthusiast
Posts: 279
Joined: Wed Jun 23, 2010 5:13 pm

Re: CrossDatabase sql query using two database handles

Post by LiK137 »

Hi Kiffi,
thanx for SQLite2SQLite but what about Postgres2Postgres and Postgres2SQLite?
User avatar
Kiffi
Addict
Addict
Posts: 1353
Joined: Tue Mar 02, 2004 1:20 pm
Location: Amphibios 9

Re: CrossDatabase sql query using two database handles

Post by Kiffi »

LiK137 wrote: Fri Jul 01, 2022 10:13 pmwhat about Postgres2Postgres and Postgres2SQLite?
Unfortunately, I can't answer that because I don't use PostgreSQL.
Hygge
LiK137
Enthusiast
Enthusiast
Posts: 279
Joined: Wed Jun 23, 2010 5:13 pm

Re: CrossDatabase sql query using two database handles

Post by LiK137 »

thanx very much
infratec
Always Here
Always Here
Posts: 6817
Joined: Sun Sep 07, 2008 12:45 pm
Location: Germany

Re: CrossDatabase sql query using two database handles

Post by infratec »

I think that is not possible between different SQL-Servers.
In PostgreSQL you need dblink for that.

https://www.postgresql.org/docs/9.3/con ... ction.html

There is no SQL standard which can do such a thing.


My idea:

create a sqlite database in memory and fill it with your request fromn the different databases.
Then use the sqlite database for further requests.
percy_b
User
User
Posts: 72
Joined: Mon Jan 12, 2015 10:25 am

Re: CrossDatabase sql query using two database handles

Post by percy_b »

Hi LiK137,
Are you trying to query against different database schemas or a different database instances in PostgreSQL? If you're querying against a different database instance, you'll probably have to use a database link. To query across database links, use the PostgreSQL "dblink" function.

The following StackOverflow topic should shed some light on the topic:

https://stackoverflow.com/questions/463 ... postgresql
LiK137
Enthusiast
Enthusiast
Posts: 279
Joined: Wed Jun 23, 2010 5:13 pm

Re: CrossDatabase sql query using two database handles

Post by LiK137 »

Hi,
thanx for replies.
percy_b, using same schema on different engines. Postgres and SQLite with same structure, just one localDB and one remoteDB. I was implementing walking records one by one, check and record/update which take longer than synchronization over SQL query. Anyway, tried dblink but didn't success.

Huge Thanx
Marc56us
Addict
Addict
Posts: 1477
Joined: Sat Feb 08, 2014 3:26 pm

Re: CrossDatabase sql query using two database handles

Post by Marc56us »

(another solution)
Unless you have very large, complicated, frequently updated databases or not full access (in which cases you will also have an experienced DBA) it is often simpler to dump all or part of one database and reload it into temporary tables on the other and then do the processing. (dump and load data as CSV, not with multiples INSERT)
:wink:
LiK137
Enthusiast
Enthusiast
Posts: 279
Joined: Wed Jun 23, 2010 5:13 pm

Re: CrossDatabase sql query using two database handles

Post by LiK137 »

I appreciate all replies and thank all repliers. I ask not about alternative ways using dumps or temporary table but looking for native sql based like ATTACH applicable in two SQLites as Kiffi's solution.
User avatar
skywalk
Addict
Addict
Posts: 3972
Joined: Wed Dec 23, 2009 10:14 pm
Location: Boston, MA

Re: CrossDatabase sql query using two database handles

Post by skywalk »

Really confused?
If you have access to the 2 independent databases, then your code is the way!
The extraction of data from each database is SQL driven by you.
The nice thing about standards is there are so many to choose from. ~ Andrew Tanenbaum
Post Reply