problem with postgresql inserts

[marko]

Hi, I've just upgraded to zeos 8 on lazarus. I'm using postgresql on kubuntu 23.10.

My application does multiple paramatised inserts as it parses a csv file. Everything works ok on 7.2.14, inserts repeatably fail at random using 8.0.0.

The error message is: zeosdbo error incorrect binary data format in bind parameter. similar to bug 599.

I've checked the actual data going in and it is ok.

For the moment I have downgraded to 7.2

[marsupilami]

Hello marko,

the PostgreSQL driver changed from scharacter bindings to binary bindings. There may be a problem when converting some data to the Postgres binary formats.

One idea might be to first switch from inserting multiple rows to single rows, so we have a chance to figure out which value actualy triggers the problem.

You could check if adding 'emulate_prepares=true' helps for your use case

Code: Select all

Query.Properties.Add('emulate_prepares=true');

As far as I understand this will switch everything back to Character based operations.

Let me know, what you find out.

Best reards,

Jan

[MJFShark]

I think it might be:

Query.Properties.Add('BinaryWireResultMode=false');

to force the character mode data handling. I haven't need to use it though so it would be great to find out the data or type that causes the issue.

-Mark

[marko]

Thanks, I will try the suggestions and also try to get some confirmed failing test data in a usable form.

with kind regards
marko

[marko]

thanks,

Query.Properties.Add('emulate_prepares=true');

solves the problem

[marko]

'BinaryWireResultMode=false'

does not solve the problem, same error on the same field

[marko]

Here is a test lazarus 3.2 project with a similar structure to my main app

The inserts fail for me on line 3. Sql is provided for a database called bcsq

cheers
marko

[marsupilami]

Hello marko,

unfortunately there is nothing attached to your last post.

Best regards,

Jan

[marko]

Hi Jan,

phpbb gives me an 'http error' when attaching files. here is the code:

CREATE TABLE public."BCOrderTest" (
"OrderID" integer NOT NULL,
"CustomerID" integer,
"OrderDate" date,
"Subtotal" money,
"TaxTotal" money,
"OrderStatus" text NOT NULL
);

ALTER TABLE ONLY public."BCOrderTest"
ADD CONSTRAINT "BCOrderTest_pkey" PRIMARY KEY ("OrderID");

I've used a TZQuery and TZConnection component on the main form:

procedure TForm1.Button1Click(Sender: TObject);
begin
InsertOrder(strToInt('14'),strToint('9'),ScanDateTime('dd?mm?yyyy','28/06/2010'),'Completed',StrToCurr('71.50'),StrToCurr('6.50'));
InsertOrder(strToInt('10134'),strToint('10'),ScanDateTime('dd?mm?yyyy','02/07/2010'),'Completed',StrToCurr('836.00'),StrToCurr('83.50'));
InsertOrder(strToInt('10135'),strToint('11'),ScanDateTime('dd?mm?yyyy','07/07/2010'),'Completed',StrToCurr('379.50'),StrToCurr('40.00'));
InsertOrder(strToInt('10137'),strToint('34'),ScanDateTime('dd?mm?yyyy','08/07/2010'),'Completed',StrToCurr('220.00'),StrToCurr('23.50'));
InsertOrder(strToInt('10140'),strToint('35'),ScanDateTime('dd?mm?yyyy','08/07/2010'),'Completed',StrToCurr('484.00'),StrToCurr('46.80'));
end;

function TForm1.InsertOrder(OrderID,CustomerID:integer;OrderDate:TDateTime;OrderStatus:String;Subtotal,TaxTotal:currency):boolean;
begin
try
zBcOrderIns.Properties.add('emulate_prepares=true');
zBcOrderIns.Properties.add('BinaryWireResultMode=false');
zBcOrderIns.ParamByName('OrderID').AsInteger:=OrderID;
zBcOrderIns.ParamByName('CustomerID').AsInteger:=CustomerID;
zBcOrderIns.ParamByName('OrderDate').AsDate:=OrderDate;
zBcOrderIns.ParamByName('OrderStatus').AsString:=OrderStatus;
zBcOrderIns.ParamByName('Subtotal').AsCurrency:=Subtotal;
zBcOrderIns.ParamByName('TaxTotal').AsCurrency:=TaxTotal;
zBcOrderIns.ExecSQL;
result:=true;
except
result:=false;
end;
end;