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]")
//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 + ")"
//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 )
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
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.
freelance simulation specialist
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!