mORMot Open Source

EvaF

Member

Registered: 2014-07-19

Posts: 40

SynOleDB and ExecutePrepared

Hi,
While upgrading of my program with  the latest (unstable) version of mormot, I ran into following problem:

I need to insert/update data on server (MSSQL 2012) from client dbs (sqlite) and  for a specific reasons I need to do it in general way.

First I retrieve a table structure (field names, field types, nullable, keys,...)

the code segment looks like this:

lSQL := StringToUTf8( ' INSERT INTO ' + lTableName + '(' + lAddFields + 'changedAt,changedBy ) Values ('+  lParameters + 'getDate(), '+quotedStr(fSiteUser)+')');
// lParameters ='?,?,?,...'
// lAddFields comma delimited list of fieldnames of lTablename 
with fprops.MainConnection.NewStatementPrepared(lSql , false) do
begin
   lFieldCount := 0;
   for i := 0 to lField.count - 1 do
   begin
      lfn := lField.Names[i];
      if (lSQLTable.FieldIndex(lfn)>-1) then
      begin
         inc(lFieldCount);                                            
         lDataType := lField.objects[lfn].strings['DATA_TYPE'];
         if  not(lKey.has[lfn] ) and (lField.Objects[lfn].strings['NULLABLE']='YES') and (lSQLTable.Get(lSQLTable.stepRow,lSQLTable.FieldIndex(lfn)) = '') then
            BindNull(lFieldCount)
         else  if endsText('int',lDataType) then
            Bind(lFieldCount,lSQLTable.GetAsInt64(lSQLTable.stepRow,lSQLTable.FieldIndex(lfn)))
        ...
        else  if endsText('binary',lDataType)  then
            BindBlob(lFieldCount,lSQLTable.GetBlob(lSQLTable.stepRow,lSQLTable.FieldIndex(lfn)))
        ...
      end;
   end;
   try
     ExecutePrepared;
   except
      on E:Exception do
      begin
       ......      
      end;
   end;

   

and I receive the exception whenever I try to insert Null value into a varbinary(MAX) field

Project .. raised exception class EOleDBException with message 'TOleDBConnection:
OLEDB Error 80040E14 -   (line 1): Implicit conversion from data type nvarchar to varbinary(max) is not allowed. Use
the CONVERT function to run this query.

I debugged it and I suspect that the problem is in TOleDBStatement.ExecutePrepared procedure
First I think ( and I may be wrong) that there is a typo error in this procedure and instead of BI.pwszDataSourceType there  should be BI^.pwszDataSourceType
and the same goes for BI.dwFlags

case P^.VType of
ftNull: begin
  P^.VStatus := ord(stIsNull);
  BI.pwszDataSourceType := 'DBTYPE_WVARCHAR';    
  BI.dwFlags := BI^.dwFlags or DBPARAMFLAGS_ISNULLABLE;

but the reason  for  my exception is the following line:

 BI.pwszDataSourceType := 'DBTYPE_WVARCHAR';  

when I modify the this small part of ExecutePrepared procedure:

case P^.VType of
ftNull: begin
  P^.VStatus := ord(stIsNull);
  BI^.dwFlags := BI^.dwFlags or DBPARAMFLAGS_ISNULLABLE;

then I am able to successfully insert  null value into a varbinary field

EvaF

Member

Registered: 2014-07-19

Posts: 40

Re: SynOleDB and ExecutePrepared

Again TOleDBStatement.ExecutePrepared procedure

I ran into next problem related to Blob parameters - whenever I try to store a picture into varbinary(max) field ( using BindBlob - see code above ), in MSSQL database is stored only first 8 bytes.

0x89504E47

the reason is imho that wrong ParamBindInfo.ulParamSize is set for blob field.

procedure TOleDBStatement.ExecutePrepared;
...
...
  ftBlob: begin
    B^.dwPart := DBPART_VALUE or DBPART_LENGTH or DBPART_STATUS;
    B^.obValue := PAnsiChar(@P^.VBlob)-pointer(fParams);
    B^.cbMaxLen := sizeof(Pointer);
    P^.VInt64 := length(P^.VBlob);
    B^.obLength := PAnsiChar(@P^.VInt64)-pointer(fParams);
// ---------------ParamBindInfo.ulParamSize-------------------
// For parameters that use a variable-length data type
// The maximum length of the data type in characters (for DBTYPE_STR and DBTYPE_WSTR) or in bytes (for DBTYPE_BYTES and DBTYPE_VARNUMERIC),
    BI^.ulParamSize := P^.VInt64;                       // <--- added the setting of length                
  end;
  ftUTF8: begin
    B^.obValue := PAnsiChar(@P^.VText)-pointer(fParams);
    ...
  end;
  end;
end;
if BI^.ulParamSize = 0  then
  BI^.ulParamSize := B^.cbMaxLen;                   //   <--- Here is set default length  - but it is valid only
                                                    //        for parameters that use a fixed-length data type

EvaF

Member

Registered: 2014-07-19

Posts: 40

Re: SynOleDB and ExecutePrepared

Was I mistaken?

houdw2006

Member

Registered: 2015-05-23

Posts: 48

Re: SynOleDB and ExecutePrepared

It seems to me that you are right. @EvaF.

@ab, Could you please check the SynOleDB unit？ Thank you.

procedure TOleDBStatement.ExecutePrepared;                                    // line 1818 in unit SynOleDB

    BI.pwszDataSourceType := 'DBTYPE_WVARCHAR';                 // line 1919 in unit SynOleDB,  BI.  --> BI^.
    BI.dwFlags := BI^.dwFlags or DBPARAMFLAGS_ISNULLABLE; // line  1920 in unit SynOleDB, BI.  --> BI^.

    // To Correct the Blob contents being truncated problem, insert the following line after line 1931 in unit SynOleDB
    BI^.ulParamSize := length(P^.VBlob);     // by-pass line 1948 when field type is fBlob: BI^.ulParamSize := B^.cbMaxLen;

Last edited by houdw2006 (2016-05-25 22:40:52)

zed

Member

From: Belarus

Registered: 2015-02-26

Posts: 105

Re: SynOleDB and ExecutePrepared

I ran into next problem related to Blob parameters - whenever I try to store a picture into varbinary(max) field ( using BindBlob - see code above ), in MSSQL database is stored only first 8 bytes.

This is old bug: http://synopse.info/forum/viewtopic.php?id=3009

Last edited by zed (2016-05-25 15:42:44)

houdw2006

Member

Registered: 2015-05-23

Posts: 48

Re: SynOleDB and ExecutePrepared

@zed, yes, I have read that as well, thank you. I met the truncation problem when I try the third party demo, FishFactSyn, given by EMartin.

Stores 4 bytes or 8 bytes to the Blob field, it actually depends on the server is a 32-bit or 64-bit program, that is the sizeof(Pointer). So it seems to me that EvaF's solution is better.

Here I just want ab to pay attention to this problem and give it a solution, that's it. :=)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: SynOleDB and ExecutePrepared

Please check http://synopse.info/fossil/info/e44c083b7d

Any feedback is welcome!