Cancel
Showing results for 
Search instead for 
Did you mean: 

Importing from SQL to IMDB-SQLite

Hello Community,

 

I am currently developing a model in which I need to import plant data from a SQL server using the ODBC connector. To avoid making readings constantly to the server, I'd like to load it all to an in-memory database using the SQLite object and read from there from then on, but the only way I've been able to do so is as follows:

 

var tImport: table
var sql: string

//Importing the data from SQL to a local table ODBC.login ODBC.sql(tImport,"SELECT * FROM [MyDDBB].[dbo].[MyTable]")
ODBC.logout

//Creating the table in the in-memory SQLite database
SQLite.open sql:= tImport[1,0] + "     " + tImport.getDataType(1) for var j:=2 to tImport.XDimIndex sql+= "," + tImport[j,0] + "    " + tImport.getDataType(j) next
sql:= "CREATE TABLE " + sTable + " (" + sql + ")"
SQLite.exec(sql)
//Importing the data from the local table to the SQLite table for var k:= 1 to tImport.YDim   sql:= "\"" + to_str(tImport[1,k])+ "\""     for var j:=2 to tImport.XDimIndex         sql+= ",\"" + to_str(tImport[j,k]) + "\""     next     sql:= "INSERT INTO " + sTable + " VALUES (" + sql + ")"     SQLite.exec(sql) next

 

I've found this way of working from one of @Steffen_B presentations (thank you by the way Smiley Happy )

 

My question is: is there any way to import all the data directly from SQL to SQLite without using a TableFile object or, like in my code, a local table? If not, can I directly initialize a SQLite table without having to loop through the local table to insert the data row by row? This method is quite time-consuming when importing large data tables from SQL.

 

Thank you in advance

4 REPLIES 4

Re: Importing from SQL to IMDB-SQLite

Gears Esteemed Contributor Gears Esteemed Contributor
Gears Esteemed Contributor

only one idea:

- create a dump-file in SQL server

- load the dump-file via file-interface in Plant Simulation

- run the dump-sql statement in sqlite

 

I guess, you will get some problems with the data types, they are pritty different between SQL server and MySQL.

Steffen Bangsow
freelance simulation specialist  
web: www.bangsow.eu
mail: steffen@bangsow.net

Re: Importing from SQL to IMDB-SQLite

Hello @Steffen_B ,

 

Thanks for your quick answer! Indeed that could be a good solution, but I am encountering some handicaps.

 

My method is able to import all the information from SQL server whenever I call it and without any manual step, so I can always have the most recent information. This is an important feature of my model because I'd like to launch it automatically from other platforms and get simulation studies results.

 

The question is that I've been trying to do it like you've mentioned but I could only manage to create this dump-file manually, and therefore if I want it to run automatically I can not be sure that the information is always updated. Do you know a way to create this dump-file with a sql-query for instance?

 

Thank you again for your support!

Re: Importing from SQL to IMDB-SQLite

Gears Esteemed Contributor Gears Esteemed Contributor
Gears Esteemed Contributor

the transact sql command is  BACKUP DATABASE (see documentation)

Steffen Bangsow
freelance simulation specialist  
web: www.bangsow.eu
mail: steffen@bangsow.net

Re: Importing from SQL to IMDB-SQLite

Thank you Steffen, I'll try it asap and report here the results Smiley Wink