Topic: Issue with params (String and WideString)

Hi,

Thank you for this library, I'm just starting to use it and I'm already happy with it!

I have an issue with parameters when using strings, see the code above. I'm not able to see where the bug happen.

  Dataset.Close;
  Dataset.Params.Clear;
  Dataset.ParamCheck := True;
  Dataset.SQL.Text := 'SELECT * FROM archive WHERE path=:PathAsString;';
  Dataset.Params.ParamByName('PathAsString').Value := ('path');
  Dataset.Open;

I'm getting an access violation in:

SqlitePassUtils.TSqlitePassAnsiStringList.Get(131184)
SqlitePassDbo.TSqlitePassRecordset.GetFieldAsAnsiString($91E0140)
SqlitePassDbo.BindUTF8AnsiStringFieldValueBufferToSqliteValueAsUTF16($F33290,$91E0140,$AE7098,1)
SqlitePassDbo.TSqlitePassParam.BindValue
SqlitePassDbo.TSqlitePassParams.BindValues
SqlitePassDbo.TSqlitePassDataset.InternalRefresh
SqlitePassDbo.TSqlitePassDataset.InternalOpen
:00657b3b TDataSet.DoInternalOpen + $1F
:006578f6 TDataSet.Open + $A

for the following reasons, FList is "nil" in TSqlitePassAnsiStringList.Get, and I can see that StrIndex is not valid (I'm getting a random value > 255)  in GetFieldAsAnsiString

If FList is "nil" it should mean that the parameter has not been stored correctly in the TSqlitePassAnsiStringList ?
Any help ?

Re: Issue with params (String and WideString)

I made some changes
First, here's a fix for Delphi Unicode, you have to cast string to ansistring explicitly, we need to replace:

     ftString, ftFixedChar, ftMemo, FtFmtMemo:
       begin
       StrSize := Length(StrValue)+1;
       GetMem(Buffer, StrSize);
       StrCopy(Buffer, PAnsiChar(StrValue));
       end;

with

     ftString, ftFixedChar, ftMemo, FtFmtMemo:
       begin
       StrSize := Length(StrValue)+1;
       GetMem(Buffer, StrSize);
       StrCopy(Buffer, PAnsiChar(AnsiString(StrValue)));
       end;

And about params, I don't really understand why binding is made on Recordset data for strings as it's not the case for others types like integer or date and time. So I replaced all "Recordset.GetFieldAs"

in BindAnsiStringFieldValueBufferToSqliteValueAsUTF8

UTF8StrValue := SqlitePassUtils.AnsiToUTF8(PAnsiChar(FieldValueBuffer));

in BindUTF8AnsiStringFieldValueBufferToSqliteValueAsUTF8

UTF8StrValue := SqlitePassUtils.AnsiToUTF8(PAnsiChar(FieldValueBuffer));

in BindUTF8AnsiStringFieldValueBufferToSqliteValueAsUTF16

UTF16StrValue := SqlitePassUtils.UTF8Decode(PAnsiChar(FieldValueBuffer));

in BindWideStringFieldValueBufferToSqliteValueAsUTF16

UTF16StrValue := PWideChar(FieldValueBuffer);

What do you think about this ? It is the correct way ?

Re: Issue with params (String and WideString)

Ok Yann,

Merci pour toutes ces infos. Je fais les corrections pour les AnsiStrings et reviens vers toi pour les bindings, à vérifier.

A+

Luc

Re: Issue with params (String and WideString)

Yann wrote :

And about params, I don't really understand why binding is made on Recordset data for strings as it's not the case for others types like integer or date and time. So I replaced all "Recordset.GetFieldAs"

in BindAnsiStringFieldValueBufferToSqliteValueAsUTF8

UTF8StrValue := SqlitePassUtils.AnsiToUTF8(PAnsiChar(FieldValueBuffer));

Unfortunately, you shouldn't modify this code because it is also used to bind the values to the update or insert sql statements.
I explain : When a TsqlitePassRecordset record stores a string field, it doesn't directly store a pointer to the string but an integer (index) to a stringlist item. The string itself is stored in the stringlist.

As a consequence, we are dealing here with either a pointer on a string or a stringlist  index (integer).

If the stringlist is convenient for debugging, it should be removed now (same for wideString).

I'm going to work on this implementation change.

As a quick fix, you could use :

procedure TSqlitePassParam.BindValue;
var
MasterDataset: TSqlitePassDataset;
begin
if Assigned(FMasterField)
   then begin
        MasterDataset := TSqlitePassDataset(FMasterField.Dataset);
        FValue := MasterDataset.FRecordset.GetFieldValueBuffer(MasterDataset.GetActiveRecord, FMasterField.Tag);
        if not MasterDataset.FRecordset.FieldIsNull(FValue)
           then FBindValue(FParams.FDataset.FRecordset, FValue, FParams.FDataset.FSQLSelectStmt.StmtHandle, FParamIndex);
        end
   else if Not FIsNull
           then 
// Fix
FBindValue(Nil, FValue, FParams.FDataset.FSQLSelectStmt.StmtHandle, FParamIndex);
//

end;

and

procedure BindAnsiStringFieldValueBufferToSqliteValueAsUTF8
          (Const Recordset: TSqlitePassRecordset;
           Const FieldValueBuffer: PRecBuffer;
           Const PreparedStmt: Pointer;
           Const PreparedStmtFieldNo: Integer);{$IFDEF HasInline} inline; {$ENDIF}
var
UTF8StrValue: UTF8AnsiString;
begin
// Fix
 if Assigned(Recordset)
   then UTF8StrValue := SqlitePassUtils.AnsiToUTF8(Recordset.GetFieldAsAnsiString(FieldValueBuffer))
   else  UTF8StrValue := SqlitePassUtils.AnsiToUTF8(FieldValueBuffer)
//

 With Recordset.FDataset.FDatabase
      do FEngine.CheckResult(SqliteDbv3_bind_text(PreparedStmt, PreparedStmtFieldNo, pAnsiChar(UTF8StrValue), Length(UTF8StrValue), SQLITE_TRANSIENT));
end;

Thanks for all your testing and your usefull and accurate code analysis.

By the way, I'm looking for fast string sorting with custom collation : the current sort implementation (quicksort derivated) is not too bad but kind of slow with strings comparisons and doesn't handle custom char comparison. I've already looked for a better algo, without any success (RadixSort, PostManSort, String Compression that preserves Chars comparison...etc)

Any idea ?

Best regards.

Luc

Re: Issue with params (String and WideString)

Hi,

Sorry, I don't know anything about sorting smile

About the recordset thing, when you set it to nil, you lost some functionnality like:

Recordset.FDataset.FDatabase.FTranslator.
Recordset.FDataset.FDatabase.FEngine.

Maybe you planned to change the "TSqlitePassValueBufferToSqliteValue" declaration by adding the "Database" ?

{ Procedure to bind a FieldValue Buffer to a sqlite statement }
TSqlitePassValueBufferToSqliteValue = procedure
                                              ( Const Database: TSqlitePassDatabase;
                                                Const Recordset: TSqlitePassRecordset;
                                                Const FieldValueBuffer: PRecBuffer;
                                                Const PreparedStmt: Pointer;
                                                Const PreparedStmtFieldNo: Integer);

Re: Issue with params (String and WideString)

Ok Yann,

You are right. It seems I wrote this fix too quickly and missed some important things here ... hmm

As you suggest, I could add the Database to the procedure parameters but I don't want to have too many parameters involved.
I think about it and try to be rid off the Recordest TSqlitePassStringList/TSqlitePassWideStringList calls.