Topic: problems with basic select

hello !

first: congratulation to this product, it seems a lot easier to use than the lazarus internal sqlite components.
and i need the blob-support, this is on of the main reasons i try it out. :-)

* i use lazarus 0.9.29 (win32)
* sqlitepass 0.5.1
* my sqlite database was created with sqlite expert 3.0.33.2068, file ending is ".db3".

what i want:
1. i try to create a database and a dataset object in a simple unit, not a form.
2. connect to the database
3. make a query the the db
4. show the result in a grid.
5. close the database and free all ressources.

but i have a problem, i can´t figure out. i post my code, and please correct it at any location if it is not the "best practise" way for sqlitepass. had this code yesterday written, it was my first try.

    // creating and connecting to the db
    sqlDB:=TSqlitePassDatabase.Create(nil);
    sqlCur:=TSqlitePassDataset.Create(nil);

    sqlDB.Database:='ds_db.db3';
    sqlDB.DatabaseType:=dbtSqliteExpert;  // is this right / nessessary ?
    sqlDB.Connected:=True;
    sqlCur.Database:=sqlDB;

    //quering
    sSQL:='SELECT a.*, b.WeCl2_Name, c.WeCl1_Name '+
               'FROM Weapons a '+
               'INNER JOIN WeaponClassification2 b ON a.WeCl2_ID=b.WeCl2_ID '+
               'INNER JOIN WeaponClassification1 c ON b.WeCl1_ID=c.WeCl1_ID '+
                'ORDER BY c.WeCl1_Name, b.WeCl2_Name, a.We_Name;';
    sqlCur.SQL.Text:=sSQL;
    sqlCur.Open;
   
    // no recordset-iteration, because of an easy example
    showmessage( sqlCur.FieldByName('WeCl2_Name').AsString );
    sqlCur.Close;


    // closing and freeing objects. is this enogh?
    sqlDB.Connected:=False;

    sqlCur.Free;
    sqlDB.Free;


it crashes on the line
    >sqlCur.SQL.Text:=sSQL;

the message is: excepotion "EDatabase Error", "1: no such column: Weapons._RowID_"
have tried the rewrite the select without ".*"(naming all fields) ->same result.
the query is ok, works with sqlite expert and the lazarus sqlite-components.

what i have to do to correct this prolem?


thanks.

Last edited by Leberecht (2010-07-31 09:12:34)

Re: problems with basic select

Hi,

Thanks for trying the components.

I'm afraid your query is too complex to be correctly handled with the 0.51 package. Give a try with sqlCur.ReadOnly := True without to much hope...

The 0.55 package should solve this issue and will be available for download in a couple of days. So I would like to test your db and querie or a sample before publishing the next release. Could you send a sample by mail ?

Your code is correct. Here are some suggestions

   // creating and connecting to the db
    sqlDB:=TSqlitePassDatabase.Create(nil);
    sqlCur:=TSqlitePassDataset.Create(nil);

    sqlDB.Database:='ds_db.db3';
   
    
    -> NOT NECESSARY - DB3 EXTENSION FILE IS ENOUGH
   //sqlDB.DatabaseType:=dbtSqliteExpert;  // is this right / nessessary ? 
   

    sqlDB.Connected:=True;
    sqlCur.Database:=sqlDB;

    //quering
    sSQL:='SELECT a.*, b.WeCl2_Name, c.WeCl1_Name '+
               'FROM Weapons a '+
               'INNER JOIN WeaponClassification2 b ON a.WeCl2_ID=b.WeCl2_ID '+
               'INNER JOIN WeaponClassification1 c ON b.WeCl1_ID=c.WeCl1_ID '+
                'ORDER BY c.WeCl1_Name, b.WeCl2_Name, a.We_Name;';
    sqlCur.SQL.Text:=sSQL;
    sqlCur.Open;
   
    // no recordset-iteration, because of an easy example
    showmessage( sqlCur.FieldByName('WeCl2_Name').AsString );
   
-> NOT NECESSARY TO CLOSE SQLCUR AND SQLDB. THEY WILL BE CLOSED ANYWAY WHEN YOU FREE THEM.
    OR CLOSING SQLDB WILL CLOSE SQLCUR
   
   // sqlCur.Close;

    // closing and freeing objects. is this enogh?
    //sqlDB.Connected:=False;

    sqlCur.Free;
    sqlDB.Free;

Thanks

Re: problems with basic select

sorry, i have switched back to the lazarus internal sqlite components and use imagelists instead of blob support. it´s faster and less complicated than blobs. it was only a small test in the way "coult i get blob-images to work?".

  • eddie
  • New member
  • Offline

Re: problems with basic select

your code suggestion is excellent buddy. I've tried it and it's working fine.

  • jeba
  • New member
  • Offline

Re: problems with basic select

hi luckylazarus,..
thanks for nice code suggestion,..its very nice and useful,..

Re: problems with basic select

The code was really good, it do really help.