• sls1j
  • New member
  • Offline

Topic: Having some beginners troubles

I'm having some problem getting the fields to show up in my queries with the following code:

// defined in the unit
var
  DB TSqlitePassDatabase;
  Cur TSqlitePassDataset;

// function

procedure TForm1.FormCreate(Sender: TObject);
     DB.Open;
     Cur.SQL.Text := 'SELECT name,id,username,salt,passhash FROM Runners';
     Cur.Open;
     Cur.First;
     while not Cur.EOF do begin
           Item := UserList.Items.Add;
           try
              Memo1.Lines.Add( IntToStr( Cur.Fields.Count ) );
              Cur.Active := true;
              Item.Caption := Cur.FieldByName( 'name' ).AsString;
              Item.SubItems.Add( Cur.FieldByName( 'id' ).AsString );
              Item.SubItems.Add( Cur.FieldByName( 'username' ).AsString );
              Item.SubItems.Add( Cur.FieldByName( 'salt' ).AsString );
              Item.SubItems.Add( Cur.FieldByName( 'passhash' ).AsString );
           except
           end;
           Cur.Next;
     end;
     
     Cur.Close;
     FieldNames.Free
end;

Basically it's retrieving the expected number of records but for some reason it isn't getting any of the fields.  I've double checked my names with my sqlite db and they match.  Is there something I'm missing?

Thanks for your help

Barefoot Brian

Re: Having some beginners troubles

Thanks for your post.

Your code seems to be OK. Unfortunately, there is a bug in the TSqlitePassDataset.InternalInitFieldDefs regarding the use / not use of the rowid in the SQL statement. It should be solved in the 0.22 version, available by the end of this week (2006-09-21).

Then, let me know if you still have trouble.

This new version will also include some infos about indexes defs and improvements in demo.

  • sls1j
  • New member
  • Offline

Re: Having some beginners troubles

Thanks for you response,
I found the problem.  I didn't have the DatabaseType set correctly.  When I set it to dtSqliteAdmin and it started working.  I noticed that there are two different sqlite type 'dtSqliteAdmin' and 'dtSqlitePass' what's the difference? Also discovered that when I  tried to access a field that came back NULL it would throw an exception.  So I'll add some exception handlers. 

I quite like this control you've written you've done a good job.  I'd be happy to help out with testing and expanding the example base.

Thanks,

Barefoot Brian

  • sls1j
  • New member
  • Offline

Re: Having some beginners troubles

Ok some more beginners troubles.

I'm trying to insert a record into a simple table.  I was able to do it with the following code:

var
  DB TSqlitePassDatabase;
  SQL : String;

begin
// insert new record into database
SQL := 'INSERT INTO Runners ( username, name, salt, passhash ) values(''' + UserName + ''',''' + FullName + ''',''' + Salt + ''',''' + PassHash + ''')';
DB.Engine.Exec( SQL, nil  );
end

For some reason I've a feeling this isn't the best way to do it.  Do you have any other suggestions on how to insert a record in a table?

Re: Having some beginners troubles

Hi Brian,

v0.21 -> 0.22


I noticed that there are two different sqlite type 'dtSqliteAdmin' and 'dtSqlitePass' what's the difference?

The TSqlitePassDatabaseType = (dtSqlitePass, dtKexi, dtSqliteAdmin, dtFpc, dtUnknown); is used by the TSqlitePassDatabase to find out (from the file extension) the program used to create the sqlite database file.

Then, the Procedure TSqlitePassDatabase.Open takes care of creating the right translator for this database type, because Kexi or SqliteAdiministrator don't handle things the same way, especially with date/time/index/fieldtypes...


Procedure TSqlitePassDatabase.Open;
begin
....
{ Create the appropriate FieldTranslator }
// TODO : continu Translator implementation...
Case FDatabaseType of
  dtSqlitePass  : Translator := TSqlitePassTranslator.Create(Self);
  dtKexi        : Translator := TSqlitePassTranslator_Kexi.Create(Self);
  dtSqliteAdmin : Translator := TSqlitePassTranslator.Create(Self);
  dtFpc         : Translator := TSqlitePassTranslator.Create(Self);
  end;
....
end;

For some reason I've a feeling this isn't the best way to do it.  Do you have any other suggestions on how to insert a record in a table?

Your code is ok. In fact, the SqlitePassdataset uses quite the same code to insert/append records.
But, the goal of the component, even if you can use directly a call to the DB.Engine.Exec( SQL, nil  ) is to avoid such annoying writting...
Furthermore, it doesn't take care of correctly binding fields values (look at the TSqlitePassTranslator.BindValue) and doesn't use transactions which are very important to speed up operations.

The classic way would be to use a TSqlitePassDataset linked to a TSqlitePassDatabase, open and use a DbGrid or other DataAware controls to do the job.

Another way is to use code like in the Demo program :


{ ------- Testing ------- }
procedure MainForm_DoSbAppendRecordsClick;
var
i, j, TickCount, RecordCount: Integer;
begin
// Inserting Records - Speed Test.
With MainForm do
begin
MyDataset.DisableControls;
TickCount := Integer(GetTickCount);
RecordCount := StrToInt(EditRecordCount.Text);
if CheckBoxUseTransaction.Checked
   then MyDataset.Database.Engine.Transaction.Start;

for i := 0 to RecordCount do
begin
MyDataset.Append;
for j := 0 to Pred(MyDataset.Fields.count) do
Case MyDataset.Fields[j].DataType of
     ftString : MyDataset.Fields[j].Value := 'Enre num : ' + IntToStr(i) + '-T' + IntToStr(TickCount);
     ftInteger: MyDataset.Fields[j].Value := TickCount + j;
     ftFloat  : MyDataset.Fields[j].Value := TickCount + j + 0.57;
     end;
MyDataset.Post;
end;
if CheckBoxUseTransaction.Checked
   then MyDataset.Database.Engine.Transaction.Commit;
MyDataset.EnableControls;
MainForm_UpdateStatusBar;
end;
end;

Your contribution is welcome for expanding database examples, help file, testing, coding or wathever you wish.

Just let me know if you use fpc/delphi, the versions...etc.

Luc