I've got a question about parameter handling in firebird 2.5 used in PHP5 using PDO
I have the following query that DOES work, but as you can see i need to name each parameter (which is exactly the same).
$sql = 'INSERT INTO orderregels
(order_ref, product_ref, variant_ref, magazijn_ref, aantal, code, omschrijving, maat, picklocatie, ean, ehprijs, korting, brutoprijs, regelbedrag, vvp, merk_ref, eenheid_ref, eenheid,
status_ref, btwtarief_ref, btwtarief, gewicht, hscode, atype)
VALUES (
:ORDER_REF,
(select product_ref from productvariant where id=:VARIANT_REF),
:VARIANT_REF2,
:MAGAZIJN_REF,
:AANTAL,
(select code from productvarianten where id=(select variant_ref from productvariant where id=:VARIANT_REF3)),
((SELECT omschrijving FROM producten WHERE id=(SELECT product_ref FROM productvariant WHERE id=:VARIANT_REF4))||\' \'||
(SELECT omschrijving FROM productvariant WHERE id=:VARIANT_REF5)),
(SELECT maat FROM productvariant WHERE id=:VARIANT_REF6),
(select magazijnlocatie from productvariant where id=:VARIANT_REF7),
(select ean from productvariant where id=:VARIANT_REF8),
(select ehprijs from productvariant where id=:VARIANT_REF9),
:KORTING,
((select ehprijs from productvariant where id=:VARIANT_REF10)*(1-(cast(:KORTING2 as numeric(18,3))/100))),
(:AANTAL2 *
((select ehprijs from productvariant where id=:VARIANT_REF11)*(1-(cast(:KORTING3 as numeric(18,3))/100)))),
(select vvp from productvariant where id=:VARIANT_REF12),
(select merk_ref from producten where id=(select product_ref from productvariant where id=:VARIANT_REF13)),
(select eenheid_ref from producten where id=(select product_ref from productvariant where id=:VARIANT_REF14)),
(select omschrijving from eenheden where id=(select eenheid_ref from producten where id=(select product_ref from productvariant where id=:VARIANT_REF15))),
1,
(select btwtarief_ref FROM orders WHERE id=:ORDER_REF2),
(SELECT percentage FROM btwtarieven where id=(select btwtarief_ref FROM orders WHERE id=:ORDER_REF3)),
(select gewicht from productvariant where id=:VARIANT_REF16),
(select hscode from producten where id=(select product_ref from productvariant where id=:VARIANT_REF17)),
1)';
$params = Array(
':ORDER_REF' => $order_ref,
':VARIANT_REF' => $variant_ref,
':VARIANT_REF2' => $variant_ref,
':MAGAZIJN_REF' => 1,
':AANTAL'=> $aantal,
':VARIANT_REF3' => $variant_ref,
':VARIANT_REF4' => $variant_ref,
':VARIANT_REF5' => $variant_ref,
':VARIANT_REF6' => $variant_ref,
':VARIANT_REF7' => $variant_ref,
':VARIANT_REF8' => $variant_ref,
':VARIANT_REF9' => $variant_ref,
':KORTING'=> $korting,
':VARIANT_REF10' => $variant_ref,
':KORTING2'=> $korting,
':AANTAL2' => $aantal,
':VARIANT_REF11' => $variant_ref,
':KORTING3'=> $korting,
':VARIANT_REF12' => $variant_ref,
':VARIANT_REF13' => $variant_ref,
':VARIANT_REF14' => $variant_ref,
':VARIANT_REF15' => $variant_ref,
':ORDER_REF2' => $order_ref,
':ORDER_REF3' => $order_ref,
':VARIANT_REF16' => $variant_ref,
':VARIANT_REF17' => $variant_ref
);
The simplified version (not naming each parameter uniquely) runs fine in a query editor like Database Workbench or when used in Delphi.
$sql = 'INSERT INTO orderregels
(order_ref, product_ref, variant_ref, magazijn_ref, aantal, code, omschrijving, maat, picklocatie, ean, ehprijs, korting, brutoprijs, regelbedrag, vvp, merk_ref, eenheid_ref, eenheid,
status_ref, btwtarief_ref, btwtarief, gewicht, hscode, atype)
VALUES (
:ORDER_REF,
(select product_ref from productvariant where id=:VARIANT_REF),
:VARIANT_REF,
:MAGAZIJN_REF,
:AANTAL,
(select code from productvarianten where id=(select variant_ref from productvariant where id=:VARIANT_REF)),
((SELECT omschrijving FROM producten WHERE id=(SELECT product_ref FROM productvariant WHERE id=:VARIANT_REF))||\' \'||
(SELECT omschrijving FROM productvariant WHERE id=:VARIANT_REF)),
(SELECT maat FROM productvariant WHERE id=:VARIANT_REF),
(select magazijnlocatie from productvariant where id=:VARIANT_REF),
(select ean from productvariant where id=:VARIANT_REF),
(select ehprijs from productvariant where id=:VARIANT_REF),
:KORTING,
((select ehprijs from productvariant where id=:VARIANT_REF)*(1-(cast(:KORTING as numeric(18,3))/100))),
(:AANTAL *
((select ehprijs from productvariant where id=:VARIANT_REF)*(1-(cast(:KORTING as numeric(18,3))/100)))),
(select vvp from productvariant where id=:VARIANT_REF),
(select merk_ref from producten where id=(select product_ref from productvariant where id=:VARIANT_REF)),
(select eenheid_ref from producten where id=(select product_ref from productvariant where id=:VARIANT_REF)),
(select omschrijving from eenheden where id=(select eenheid_ref from producten where id=(select product_ref from productvariant where id=:VARIANT_REF))),
1,
(select btwtarief_ref FROM orders WHERE id=:ORDER_REF),
(SELECT percentage FROM btwtarieven where id=(select btwtarief_ref FROM orders WHERE id=:ORDER_REF)),
(select gewicht from productvariant where id=:VARIANT_REF),
(select hscode from producten where id=(select product_ref from productvariant where id=:VARIANT_REF)),
1)';
$params = Array(
':ORDER_REF' => $order_ref,
':VARIANT_REF' => $variant_ref,
':MAGAZIJN_REF' => 1,
':AANTAL'=> $aantal,
':KORTING'=> $korting
);
it gives me the following error:
Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: -804 Dynamic SQL Error SQL error code = -804 Incorrect values within SQLDA structure '
Is it true that each time I use a parameter (even if it has the same name) I have to uniquely name them and place them in the exact order in which they are used in the statement?? It takes all the advantages of named parameters away...
Thanks for any info,
Christiaan
Yes, it's true. See this answer for details.
Not at all, since their main advantage is to prevent SQL injection.
The linked answer also presents a proposal how to add "homemade" support for the kind of bindings you want.
BTW I'm surprised you can connect to firebird at all.