PostgreSQL - Binary Blob Insert Error

1

I am getting an error trying to insert a Binary Blob CW 11 / PostgreSQL ODBC - Any help/suggestions would be appreciated.

Driver Trace shows it is trying to do the insert

INSERT INTO aa_.ticksignature_ (guid_,ticket_no_,typeof_,signame_,sigdate_,sigtime_,edittech_,editdate_,edittime_,sigimage_)

An error was experienced during the update of record.  
Error: 01080E4D  0BF9F008  0001:0007FE4D C:\Ragazzi\Clients\Cornerstone\AFW.v41A\ClaRUN.dll
          00F6821D  0BF9F0A8  0001:0000721D C:\Ragazzi\Clients\Cornerstone.

In CW DCT defined as

   SigImage     BLOB,BINARY,NAME('sigimage_ | JsonName(sigimage)')

Here is the SQL definition

CREATE TABLE ticksignature_
            (
             guid_                     CHAR(32)        NOT NULL DEFAULT REPLACE( gen_random_uuid()::text, '-', '' ),                           
             ticket_no_                INT             NOT NULL DEFAULT 0 , 
             typeof_                   CHAR(1)         NOT NULL DEFAULT '', 
             signame_                  VARCHAR(80)     NOT NULL DEFAULT '', 
             sigdate_                  INT             NOT NULL DEFAULT 0 , 
             sigtime_                  INT             NOT NULL DEFAULT 0 , 
             edittech_                 CHAR(15)        NOT NULL DEFAULT '', 
             editdate_                 INT             NOT NULL DEFAULT 0 , 
             edittime_                 INT             NOT NULL DEFAULT 0 , 
             sigimage_                 BYTEA           NOT NULL DEFAULT '', --- BLOB,BINARY 
             row_created_on_           TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT       current_timestamp , 
             row_created_by_           CHAR(31)                 NOT NULL DEFAULT UPPER(current_user     ), 
             row_modified_on_          TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT       current_timestamp , 
             row_modified_by_          CHAR(31)                 NOT NULL DEFAULT UPPER(current_user     ), 

             CONSTRAINT sig_key_guid_
                 PRIMARY KEY (guid_)
            );
2

What file definition do you get if you import a sample postgres table with a blob in it, into the clarion dct?

I remember when doing a UKCUG years ago showing how to import and export ms sql tables into a clarion dct and showing there was a mismatch with the data types imported and exported, so this might be an ODBC thing in general.

1 Like
3

Good idea. I did that but it still came in as BLOB,BINARY which is what I have

I also added to my Connect string

DRIVER={PostgreSQL ODBC Driver(UNICODE)};Server=localhost;Port=5432;Database=XXXXXX;Uid=XXXXXX;Pwd=XXXXXX;UseDeclareFetch=1;ByteaAsLongVarBinary=1;LFConversion=0;

No luck

4

On the IBM website that string attribute suggests its handled differently.
PostgreSQL configuration and data type considerations (ibm.com)

The following data types are treated as LONG VARCHAR, and their space allocation is affected by the Max LongVarChar setting:

  • bytea

A (binary) blob is variable length so they need to reside outside of the main fixed length record structure and further down in that IBM link they class a ByteA as a LOB which I guess is what we call a BLOB. So have you tried your connection string without the ByteaAsLongVarBinary=1 or as ByteaAsLongVarBinary=0 when using SigImage BLOB,BINARY,NAME(ā€˜sigimage_ | JsonName(sigimage)ā€™) ! and have you put the blob as the first field in the clarion dct?

Have you checked off the clarion basics like blobs & memos have to be declared first in the dct file/table before the rest of the fields which would make up the file structure? In your example its shown in the middle of the fields making the record structure.

These links might give you some insights Storing Binary Data | pgJDBC (postgresql.org)

If that driver string ByteaAsLongVarBinary=1 is returning an address into a long, have you tried changing the data type ot a long ie
SigImage Long,NAME(ā€˜sigimage_ | JsonName(sigimage)ā€™) !

and then you might be able to use these apiā€™s

Since 7.2, the methods getBytes() , setBytes() , getBinaryStream() , and setBinaryStream() operate on the BYTEA data type

in much the same way its possible to call stored procedures in MS SQL server.

Another thought is, can you make postgres resort back to 7.1 and then try Clarion with it? I dont know if there is some sort of configuration setting which lets you regress behaviour to an earlier version?

The ADO class also handles blobsā€™s its called ADOblob so maybe having a look at that class might give some insights as well.

Other than that Iā€™m out of ideas at this stage. Maybe someone who has used Postgres can throw some light on whats best to do?

5

Bytea will work fine. look at the code in CWUTIL.clw for BlobToFile and FileToBlob. That should help

6

Yes, i have the ByteaAsLongVarBinary in my connection string.

this is the CW declaration

TickSignature        FILE,DRIVER('ODBC',SQL::DriverOptions),OWNER(SQL::ConnectString),NAME(MEM:FileName_SIG),PRE(SIG),BINDABLE,CREATE,THREAD ! Ticket Signature File
KEY_GUID                 KEY(SIG:GUID),NAME('sig_key_guid_'),PRIMARY !                     
Link2TIC_KEY             KEY(SIG:TICKET_NO,SIG:TypeOf),DUP,NAME('sig_link2tic_key_') !                     
Link2TEC_KEY             KEY(SIG:EditTech),DUP,NAME('sig_link2tec_key_') !                     
SigImage                    BLOB,BINARY,NAME('sigimage_ | JsonName(sigimage)') !                     
Record                   RECORD,PRE()
GUID                        STRING(32),NAME('guid_ | JsonName(guid)') !                     
TICKET_NO                   LONG,NAME('ticket_no_ | JsonName(ticket_no)') ! TICKET NUMBER ASSOC W/
TypeOf                      STRING(1),NAME('typeof_ | JsonName(typeof)') ! 'P' = PreAuth, 'A' = Authorization
SigName                     CSTRING(81),NAME('signame_ | JsonName(signame)') !                     
SigDate                     LONG,NAME('sigdate_ | @D02B | JsonName(sigdate)') !                     
SigTime                     LONG,NAME('sigtime_ | @T01B | JsonName(sigtime)') !                     
EditTech                    STRING(15),NAME('edittech_ | JsonName(edittech)') ! TECHNICIAN          
EditDate                    LONG,NAME('editdate_ | @D02B | JsonName(editdate)') !                     
EditTime                    LONG,NAME('edittime_ | @T01B | JsonName(edittime)') !                     
UnusedByt01                 SHORT,NAME('unusedbyt01_ | JsonName(unusedbyt01)') !                     
UnusedByt02                 SHORT,NAME('unusedbyt02_ | JsonName(unusedbyt02)') !                     
UnusedByt03                 SHORT,NAME('unusedbyt03_ | JsonName(unusedbyt03)') !                     
UnusedNum01                 LONG,NAME('unusednum01_ | JsonName(unusednum01)') !                     
UnusedNum02                 LONG,NAME('unusednum02_ | JsonName(unusednum02)') !                     
UnusedDec01                 DECIMAL(7,2),NAME('unuseddec01_ | JsonName(unuseddec01)') !                     
UnusedStr01                 STRING(20),NAME('unusedstr01_ | JsonName(unusedstr01)') !                     
UnusedStr02                 STRING(20),NAME('unusedstr02_ | JsonName(unusedstr02)') !                     
Row_Created_On              STRING(8),NAME('row_created_on_ | READONLY | JsonName(row_created_on)') !                     
Row_Created_TimeStamp       GROUP,OVER(Row_Created_On),NAME('row_created_timestamp_ | JsonName(row_created_timestamp)') !                     
Row_Created_Date              DATE,NAME('row_created_date_ | @D02B | JsonName(row_created_date)') !                     
Row_Created_Time              TIME,NAME('row_created_time_ | @T01B | JsonName(row_created_time)') !                     
                            END                            !                     
Row_Created_By              STRING(31),NAME('row_created_by_ | READONLY | JsonName(row_created_by)') !                     
Row_Modified_On             STRING(8),NAME('row_modified_on_ | READONLY | JsonName(row_modified_on)') !                     
Row_Modified_TimeStamp      GROUP,OVER(Row_Modified_On),NAME('row_modified_timestamp_ | JsonName(row_modified_timestamp)') !                     
Row_Modified_Date             DATE,NAME('row_modified_date_ | @D02B | JsonName(row_modified_date)') !                     
Row_Modified_Time             TIME,NAME('row_modified_time_ | @T01B | JsonName(row_modified_time)') !                     
                            END                            !                     
Row_Modified_By             STRING(31),NAME('row_modified_by_ | READONLY | JsonName(row_modified_by)') !                     
                         END
7

OK, so back to the begining.
What is the error youā€™re actually getting?
And how do you assign the blob?

8

As stated in my original msg here is the error

here is the generated code that works in TPS land (same program for SQL)

ImageExSignatureCapture.Buffer.SaveToBlob(QOTSIG:SigImage,ImageExJpegSaver)

9

I can read signatures already in the table, but CW is having the issues

Trace shows the blob get ok
BLOB_GET_PROP(aa_.ticksignature_:0D4A39ECH) Time Taken:0.00 secs

image
image707Ɨ262 15 KB

10

That is the SQL definition, my CW DCT has it above the record

TickSignature        FILE,DRIVER('ODBC',SQL::DriverOptions),OWNER(SQL::ConnectString),NAME(MEM:FileName_SIG),PRE(SIG),BINDABLE,CREATE,THREAD ! Ticket Signature File
KEY_GUID                 KEY(SIG:GUID),NAME('sig_key_guid_'),PRIMARY !                     
Link2TIC_KEY             KEY(SIG:TICKET_NO,SIG:TypeOf),DUP,NAME('sig_link2tic_key_') !                     
Link2TEC_KEY             KEY(SIG:EditTech),DUP,NAME('sig_link2tec_key_') !                     
**SigImage                    BLOB,BINARY,NAME('sigimage_ | JsonName(sigimage)') !**                     
Record                   RECORD,PRE()
GUID                        STRING(32),NAME('guid_ | JsonName(guid)') !                     
TICKET_NO                   LONG,NAME('ticket_no_ | JsonName(ticket_no)') ! TICKET NUMBER ASSOC W/
TypeOf                      STRING(1),NAME('typeof_ | JsonName(typeof)') ! 'P' = PreAuth, 'A' = Authorization
SigName                     CSTRING(81),NAME('signame_ | JsonName(signame)') !                     
SigDate                     LONG,NAME('sigdate_ | @D02B | JsonName(sigdate)') !                     
SigTime                     LONG,NAME('sigtime_ | @T01B | JsonName(sigtime)') !                     
EditTech                    STRING(15),NAME('edittech_ | JsonName(edittech)') ! TECHNICIAN          
EditDate                    LONG,NAME('editdate_ | @D02B | JsonName(editdate)') !                     
EditTime                    LONG,NAME('edittime_ | @T01B | JsonName(edittime)') !                     
UnusedByt01                 SHORT,NAME('unusedbyt01_ | JsonName(unusedbyt01)') !                     
UnusedByt02                 SHORT,NAME('unusedbyt02_ | JsonName(unusedbyt02)') !                     
UnusedByt03                 SHORT,NAME('unusedbyt03_ | JsonName(unusedbyt03)') !                     
UnusedNum01                 LONG,NAME('unusednum01_ | JsonName(unusednum01)') !                     
UnusedNum02                 LONG,NAME('unusednum02_ | JsonName(unusednum02)') !                     
UnusedDec01                 DECIMAL(7,2),NAME('unuseddec01_ | JsonName(unuseddec01)') !                     
UnusedStr01                 STRING(20),NAME('unusedstr01_ | JsonName(unusedstr01)') !                     
UnusedStr02                 STRING(20),NAME('unusedstr02_ | JsonName(unusedstr02)') !                     
Row_Created_On              STRING(8),NAME('row_created_on_ | READONLY | JsonName(row_created_on)') !                     
Row_Created_TimeStamp       GROUP,OVER(Row_Created_On),NAME('row_created_timestamp_ | JsonName(row_created_timestamp)') !                     
Row_Created_Date              DATE,NAME('row_created_date_ | @D02B | JsonName(row_created_date)') !                     
Row_Created_Time              TIME,NAME('row_created_time_ | @T01B | JsonName(row_created_time)') !                     
                            END                   !                     
Row_Created_By              STRING(31),NAME('row_created_by_ | READONLY | JsonName(row_created_by)') !                     
Row_Modified_On             STRING(8),NAME('row_modified_on_ | READONLY | JsonName(row_modified_on)') !                     
Row_Modified_TimeStamp      GROUP,OVER(Row_Modified_On),NAME('row_modified_timestamp_ | JsonName(row_modified_timestamp)') !                     
Row_Modified_Date             DATE,NAME('row_modified_date_ | @D02B | JsonName(row_modified_date)') !                     
Row_Modified_Time             TIME,NAME('row_modified_time_ | @T01B | JsonName(row_modified_time)') !                     
                            END                   !                     
Row_Modified_By             STRING(31),NAME('row_modified_by_ | READONLY | JsonName(row_modified_by)') !                     
                         END
                     END
11

Iā€™m out of ideas then. Sorry!

12

Ok I thought there might be an error code or something.
Is there a crash log or anything else created?

That sort of looks like a memory fault or something.
When you compile is it in debug mode?
Do you have the checks for index out of range and Stack overflow on?
Anything in the windows event log?

13

Do you have the source code for ImageExSignatureCapture.Buffer.SaveToBlob or is it hidden in a black box dll?

If you have the source and also have StringTheory compare SaveToBlob with st.ToBlob() as the ST version has some ā€œfudgesā€ to get around various driver bugs.

14

Thereā€™s a method called ImageExBitmap.Bits() that gives you the address of the pixel array. I use that to assign to a &STRING ref, using the L*W as the size parameter (x4).

15

SOLVED: for some reason when i tested earlier I must have had the option wrong, but setting

ByteaAsLongVarBinary=0 made everything work correctly

Sorry for the red herringā€¦ I appreciate everyone lending their solutions.

2 Likes