sqLite vs MySQL
lazarus / 16-Databases

----------------------------------------------------------------------
MainMeat · 2009-04-16 16:26

Hi Everyone,

I will admit upfront that I am pretty new to sqLite but perhaps
someone has some answers for me. Basically, I am trying to develop an
application that can easily be ported between sqLite and MySQL. I have
managed to get the MySQL database connectivity to work without issues
but now am stuck with the sqLite part of it.

I am making use of the TMySQL41Connection, TSQLTransaction and
TSQLQuery components. In MySQL, I was able to simple specify the
properties for the TMySQL41Connection for example:
Code: [Select]mySQLConnObj.HostName := 'localhost';
mySQLConnObj.Username := 'test';
mySQLConnObj.Password := 'testpwd';
mySQLConnObj.DatabaseName := 'test_db';
then I was able to set the TMySQL41Connection.Transaction property,
for example:
Code: [Select]mySQLConnObj.Transaction := sqlTransObj;
Lastly, when executing queries I would simply use the TSQLQuery
component, for example:
Code: [Select]sqlQueryObj.Transaction := sqlTransObj;
sqlQueryObj.Database := mySQLConnObj;

and executing a query was as simple as specifying the SQL query and
executing it, for example:

Code: [Select]SQLQuery.SQL.Text := 'select firstname, lastname from
user_info'
SQLQuery.Open;
this would then return the data, or for an insert statement I simply
would call:

Code: [Select]SQLQuery.ExecSQL;
which then executed the query and returned result.

Now I want to use the same principles on sqLite but am not having
great success. For example using the TSQLite3Connection,
TSQLTransaction and TSQLQuery classes again, as example:

  Code: [Select]SQLConn := TSQLite3Connection.Create(nil);
  SQLConn.DatabaseName := 'test.db';
  SQLTrans := TSQLTransaction.Create(nil);
  SQLQuery := TSQLQuery.Create(nil);

  Writeln('Opening database');
  SQLConn.Open;
  SQLQuery.Transaction := SQLTrans;
  SQLQuery.DataBase := SQLConn;

  Writeln('Constructing Query');
  SQLQuery.SQL.Text := 'select firstname, lastname from user_info';
  Writeln('Execting Query');
  SQLQuery.Open;

  Writeln('Found : ', SQLQuery.RecordCount, ' Records');
  for nCntr := 0 to SQLQuery.RecordCount -1 do
  begin
       Writeln(SQLQuery.FieldByName('firstname').AsString + ' :: ' +
               SQLQuery.FieldByName('lastname').AsString);
  end;
But with the above example I get a runtime error: EDatabaseError:
Database not assigned!

Does anyone have an idea what I might be doing wrong or am I completly
off the track here?

Thanks for the help and advice!

----------------------------------------------------------------------
Loesje · 2009-04-16 20:29

You didn't set the connection/database of the transaction.

----------------------------------------------------------------------
MainMeat · 2009-04-17 06:02

Hi Loesje,

thanks for the help - I missed it indeed and after setting this is
worked fine!

Just on last question: When trying to create a table from the Lazarus
application, thid does not seem to work - is there another method of
creating a table that I should be aware off? As an example I have been
trying to execute:

Code: [Select]SQLQuery.SQL.Text := 'create table test (a integer, b
integer);';
SQLQuery.ExecSQL;
But this does not return an error, nor does it create the table.

Lastly, is it possible to execute multiple insert command in one
insert query such as:
insert into users (firstname, lastname) values (('bob',
'smit'),('tricia','jones'),('julian','james'));

Thanks again!