Work at SourceForge, help us to make it a better place! We have an immediate need for a Support Technician in our San Francisco or Denver office.

Close

#31 Postgres string parameter with odd size

open
nobody
None
5
2008-10-22
2008-10-22
Mozart Hasse
No

When I use a parameterized query (e.g. "SELECT * FROM TABLE WHERE FIELD=?") the parameter is of type
string and my value has an *odd* number of characters, the value sent to the server is
incorrect. Looks like a rounding problem around line 11793 of dbxopenodbc.pas (v.3.201).
I´m using a Postgres 8.3.4 server with a UTF8 database with ODBC driver psqlodbc_08_03_0200. I'm connecting to it using Delphi 2007. My ODBC DataSource is configured to PostgreSQL Unicode as shown in the attachment.
Other connection options:

DriverName=PostgresODBC
GetDriverFunc=getSQLDriverODBCW
LibraryName=dbxoodbc.dll
VendorLib=psqlodbc35w.dll
DriverUnit=DBXDynalink
DriverPackageLoader=TDBXDynalinkDriverLoader
DriverPackage=DBXCommonDriver110.bpl
DbxWOterroRBase TransIsolation=ReadCommited
RowsetSize=1
BlobSize=-1
Trim Char=True
Custom String=coLockMode=-1;coCatalog=0;coMapInt64ToBcd=1;coMapCharAsBDE=1

Discussion

  • Mozart Hasse
    Mozart Hasse
    2008-10-22

    ODBC configuration

     
    Attachments
  • need checking
    DbxOpenOdbc.inc and _FIX_PostgreSQL_ODBC_
    and debug "function TSqlCommandOdbc.setParameter" if it needed.
    For debug need added to project DbxOpenOdbcStatic.pas

     
  • Mozart Hasse
    Mozart Hasse
    2008-11-17

    Without _FIX_PostgreSQL_ODBC_, many things stop working, all parameters seem to be truncated. I don't think turning _FIX_PostgreSQL_ODBC_ off would solve anything.
    This problem appears to be solved when I change something around the lines I pointed in the details. It calculated the wrong size when I debugged it as it is in version 3.201.

     
  • 1)
    > Length := Length div SizeOf(WideChar) * SizeOf(WideChar);

    After line specified by you importances Length/iPrecision must not change.
    This code produces check for avoiding the overflow of the buffer. If input parameter incorrect, that then really on server will is sent on one symbol less.

    For "_FIX_PostgreSQL_ODBC_" importance "Length" will be recalculated, see (2).

    2)
    Have forgotten to calculate iPrecision :(

    Need check the next fix (look "NEW:"):
    function TSqlCommandOdbc.setParameter;
    ...
    {$IFDEF _FIX_PostgreSQL_ODBC_}
    // Driver supported only utf8 charsets.
    if (bIsNull = 0) and (fOwnerDbxConnection.fOdbcDriverType = eOdbcDriverTypePostgreSQL) then
    begin
    if bIsParamIn then
    begin
    LDo_PostgreSQL_Make_Buffer;
    pBuffer := PAnsiChar(sUTF8Buffer);
    Length := System.Length(sUTF8Buffer);
    // NEW:
    iPrecision := Length + 1;
    // NEW.
    end;

    3)
    If above does not work, then:
    Possible for bUnicodeString is not need call LDo_PostgreSQL_Make_Buffer. Need check the next fix:

    {$IFDEF _FIX_PostgreSQL_ODBC_}
    // Driver supported only utf8 charsets.
    if (bIsNull = 0) and (fOwnerDbxConnection.fOdbcDriverType = eOdbcDriverTypePostgreSQL) then
    // NEW:
    if not bUnicodeString then
    // NEW.
    begin
    ...

    4)
    If something from will above help, but error "Postgres UTF-8 parameter handling" will not disappear then for "Postgres UTF-8 parameter handling" it is necessary to check the following:

    Added to connection string ioption coOBPBPL:
    coOBPBPL=1
    It option is use in "procedure ProcessVarDataLength"

    5)
    If above (4) does not work, then:
    - need set breakpoint in position:
    function TSqlCommandOdbc.setParameter;
    ...
    OdbcRetcode := SQLBindParameter(
    ...
    when debugpoint of the stop will operate you may in calculator (Crtl+F7) select/update necessary importances for next values:
    aOdbcBindParam.fOdbcParamCbColDef
    aOdbcBindParam.fBindOutputBufferLength