PDO火鸟和参数

Question

我有一个关于使用PDO 在PHP5中使用的firebird 2.5中的参数处理的问题

我有以下查询确实有效，但正如你所看到的，我需要命名每个参数（完全相同）。

$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
                    );

简化版本（不是唯一命名每个参数）在数据库工作台这样的查询编辑器中运行良好，或者在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
                    );

它会给我以下错误：

致命错误：未捕获异常"PDOException"，消息为'SQLSTATE[HY000]：常规错误：-804动态SQL错误SQL错误代码=-804 SQLDA结构中的值不正确

每次我使用一个参数（即使它有相同的名称）时，我都必须唯一地命名它们，并按照它们在语句中使用的确切顺序放置它们，这是真的吗？？它消除了命名参数的所有优点。。。

感谢提供任何信息，

Christiaan

Answer 1

每次我使用一个参数（即使它有相同的名称）时，我都必须唯一地命名它们，并按照它们在语句中使用的确切顺序放置它们，这是真的吗？

是的，这是真的。有关详细信息，请参阅此答案。

它消除了命名参数的所有优点。。。

一点也不，因为它们的主要优点是可以防止SQL注入。

链接的答案还提供了一个建议，如何为您想要的绑定类型添加"自制"支持。

顺便说一句，我很惊讶你能连接到火鸟。