TLiteLoader No such table
TLiteLoader No such table
I want to use TLiteLoader to create a SQlite database by loading data from a Delphi TDataSet using the LoadFromDataSet() method. My initial attempt failed returning an exception with message 'Table name must be defined'. After making the addition of setting the TableName property of the loader I get an exception with the message 'No such table:<mytable name here>' but I don't understand the cause of the message. Is there supposed to be a correspondence between the table name given to the loader and the dataset? I cant find an example of using LoadFromDataSet() in the demo programs. Can you provide an example or suggest how to use LoadFromDataSet() correctly?
Re: TLiteLoader No such table
I have found that it is not possible to use TLiteLoader to create a SQlite database from a TDataSet even when the ForceCreateDatabase option is set on TLiteConnection. Without a pre-existing database TLiteLoader will produce an exception "Table name must be defined" if its TableName property is not set, and "No such table:<your table name here>" if you do set the table name property.
TLiteLoader must be supplied with a connection to an existing SQlite database. Creating a SQlite database can be done with TLiteSql. However as a SQlite database cannot be created without columns, a table and columns must be created. I assume the columns must be the columns of the TDataSet supplied to the loader's LoadFromDataSet() method but I have not verified this.
Examining the speed of TLiteLoader I compared a method taking a TDataSet, which used TLiteSql to create table, columns and rows, with an alternative using TLiteSQL to create table and columns, and TLiteLoader to create rows. For a data set of ~3000 rows the version using TLiteLoader was hundreds of times slower e.g. 1s vs 400s. The TLiteLoader AutoCommit and AutoCommitRowCount properties were left at their default values.
For this particular case where no database exists, and given that to use TLiteLoader involves the use of TLiteSQL to create table and columns, it makes sense to use TLiteSQL to also create the rows and avoid the poor performance of TLiteLoader.
TLiteLoader must be supplied with a connection to an existing SQlite database. Creating a SQlite database can be done with TLiteSql. However as a SQlite database cannot be created without columns, a table and columns must be created. I assume the columns must be the columns of the TDataSet supplied to the loader's LoadFromDataSet() method but I have not verified this.
Examining the speed of TLiteLoader I compared a method taking a TDataSet, which used TLiteSql to create table, columns and rows, with an alternative using TLiteSQL to create table and columns, and TLiteLoader to create rows. For a data set of ~3000 rows the version using TLiteLoader was hundreds of times slower e.g. 1s vs 400s. The TLiteLoader AutoCommit and AutoCommitRowCount properties were left at their default values.
For this particular case where no database exists, and given that to use TLiteLoader involves the use of TLiteSQL to create table and columns, it makes sense to use TLiteSQL to also create the rows and avoid the poor performance of TLiteLoader.
Re: TLiteLoader No such table
You are absolutely right. The TLiteLoader component is designed to load data into the current database table. We will test the TLiteLoader performance when using the LoadFromDataSet method and inform you about results.
Re: TLiteLoader No such table
Thank you for the information. We have reproduced the problem and fixed the bug. The fix will be included in the next LiteDAC build. Currently, we can send you a night build of LiteDAC with the fix. For this, please specify your license number and the exact version of Delphi you are using via the e-support form ( https://www.devart.com the "Support"\"Request Support" menu)