Re: Need assistance with ODBC bytea data please

Hi Ben,

On 2017/01/21 8:05, Ben Postma wrote:
>
> Hi,
>
>
> Please help me resolve this issue, as we have a client who wants to
> use PostgreSQL.
>
>
> ODBC Driver version: pslODBC 09_05_0499-x86
>
> PostgreSQL version: 9.6.1.1
>
> Windows OS 64 bit.
>
>
> I have a table created with this statement:
>
>
> CREATE TABLE PS_BLOB (CollectionID BIGINT, BLOBID BIGINT, BLOBType
> VARCHAR(255), BLOBName VARCHAR(255), TheBLOB BYTEA);
>
>
> Note the last column which is of type BYTEA. Then I am preparing an
> insert statement and binding columns. The last column is bound as
> follows:
>
>
> ret = SQLBindParameter(ist, paramNumber, SQL_PARAM_INPUT,
> SQL_C_BINARY, SQL_LONGVARBINARY, columnSize, 0, (void*)(i+1), 0,
> &myBlobSizeMacro);
>
>
> myBlobSIzeMacro gets set to SQL_LEN_DATA_AT_EXEC(myBlobSize);
>
>
> SQLExecute correctly returns 99 asking for data for the last column.
> And SQLPutData puts the correct amount of data, no issue. No errors.
> And you can see the blob size in the log file below.
>
>
> And yet, if you look in pgAdmin4 then for the BLOB column it shows a
> number like 124618 or something. And in my code when you try and read
> the blob again, SQLGetData returns a size of 5 in the last argument,
> as if the blob is only 5 bytes, when it much larger than that.
>
>
> The log details are below. Please note:
>
> - The exact same blob code works perfectly fine with SQL Server,
> MariaDB etc. So it is unlikely to be my code I hope.
>
> - The wrong blob data issue issue manifests only the very first time
> the blob is written, subsequent calls to write another blob succeeds
> and the correct data gets written and returned again.
>
>
> I have tried calling SQLPutData with smaller chunks, but that has no
> impact.
>
>
> Any ideas? Please advise!
>
>
> I have looked at the relevant psqlODBC code but I do not see any
> obvious issue. One option would be for me to compile the source code
> and overwrite the installed DLLs and then step through SQLPutData in
> odbcapi.c and see what PGAPI_PutData in execute.c actually does. But
> the log file below is fairly detailed, maybe someone more familiar
> with it can go "Oh yes, for larger blobs we need to fix x, or you need
> to y". It looks like the code just ends up in SOCK_put_n_char which
> has to be doing the right thing. So I do not see any potential issues
> in the psqlODBC source code, it all looks good.
>

Please try to turn on ByteaAsLongVarBinary(bytea as LO) option.

regards,
Hiroshi Inoue