PureBasic Forums - English

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;"

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