Re: Re: Binding LOB input value to stored procedure with PDO_OCI

From: Date: Wed, 03 Jan 2007 03:16:34 +0000
Subject: Re: Re: Binding LOB input value to stored procedure with PDO_OCI
References: 1 2  Groups: php.pecl.dev 
Request: Send a blank email to pecl-dev+get-4432@lists.php.net to get a copy of this message
Hi Jessie, PDO deliberately has only one type for large objects, so the driver will be responsible for handling the textual nature of CLOBs internally, something made easier with the unicode support I'm currently developing for PDO. I'd like to see your patch; please resend it as a MIME attachment. Thanks! --Wez. On 1/2/07, Jessie Hernandez <jrhernandez05@gmail.com> wrote:
Hello, The current code had PARAM_LOB values hardcoded as BLOBs (hence not allowing CLOBs to be bound) and did not allow LOBs to be passed as input to stored procedures. The attached patch fixes these two issues (please let me know if it should instead be attached to the bug report described below). The fix was done by adding three new class constants, PDO_OCI_PARAM_B_BLOB, PDO_OCI_PARAM_B_CLOB, and PDO_OCI_PARAM_CREATE_TEMP_LOB. PDO_OCI_PARAM_B_CLOB indicates that the value that will be bound is a CLOB instead of a BLOB. PDO_OCI_PARAM_CREATE_TEMP_LOB indicates that a temporary LOB should be created and passed as input (instead of waiting for the LOB to be created and returned via a RETURNING clause). Since a new flag is needed to create a temporary LOB, there are no BC breaks for existing Oracle code. The following is a sample of how calling a stored procedure with an input CLOB would look like: -- ORACLE CREATE OR REPLACE PROCEDURE TEST_PDO(value IN CLOB) AS BEGIN insert into pdoclob values( dbms_lob.substr( value, 50 ) ); END; -- PHP <?php $connection = new PDO( 'oci:dbname=mydb', 'user', 'pass' ); $clob = fopen( '/tmp/testpdo.txt', 'rb' ); $s = $connection->prepare( 'begin test_pdo( ? ); end;' ); $s->bindParam( 1, $clob, PDO::PARAM_LOB, 0, PDO::OCI_PARAM_B_CLOB | PDO::OCI_PARAM_CREATE_TEMP_LOB ); $connection->beginTransaction(); $s->execute(); $connection->commit(); ?> Also, if these same flags are used, you can even insert a clob into a table using "insert into clob_table values( ? )" syntax instead of using "insert into clob_table values(empty_clob()) returning clob_column into ?" syntax! Please let me know if there is anything that needs to be improved in the attached patch (BTW, this patch would also close this bug: http://pecl.php.net/bugs/bug.php?id=7943 ). Thanks! Regards, Jessie Hernandez ""Jessie Hernandez"" <jrhernandez05@gmail.com> wrote in message news:90.10.10210.C8E13854@pb1.pair.com... I'm trying to bind a LOB value to a stored procedure call in Oracle. Since there is no RETURNING clause for a stored procedure call, I cannot use the EMPTY_CLOB() trick that's in the documentation. I've tried several things (including creating a CLOB in another statement using the DBMS_LOB.CREATETEMPORARY method), but nothing has worked. Here's some sample code of what I'm trying to do. With the oic8 extension, one would first have to create a BLOB using the oci_new_descriptor function, then bind that to the statement. Since there's no "oci_new_descriptor" equivalent in PDO_OCI, I cannot create the CLOB beforehand. Is the following possible for PDO_OCI? --- STORED PROCEDURE --- create procedure test_clob(value in clob); --- PHP CODE --- <?php $connection = new PDO( 'oci:dbname=mydb' ); $clob = fopen( 'myfile', 'r' ); $statement = $connection->prepare( 'begin test_clob( ? ); end;' ); $statement->bindParam( 1, $clob, PDO::PARAM_LOB ); $statement->execute(); /* above generates an error: SQLSTATE[HY000]: General error: 6550 OCIStmtExecute: ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'TEST_CLOB' */ ?> If the above is not possible with Oracle, can a method be provided to create descriptors only for the PDO_OCI extension? I'm willing to help implement this, as the logic would be the same as the one in the oci8 extension. Please let me know. Thanks. Regards, Jessie Hernandez begin 666 oci_lob.patch M9&EF9B M=2!P9&]?;V-I7V)A:R]O8VE?<W1A=&5M96YT+F,@<&1O7V]C:2]O M8VE?<W1A=&5M96YT+F,*+2TM('!D;U]O8VE?8F%K+V]C:5]S=&%T96UE;G0N M8PDR,# W+3 Q+3 R(#$X.C$V.C4R+C4X.38Y.#0T." M,#4P, HK*RL@<&1O M7V]C:2]O8VE?<W1A=&5M96YT+F,),C P-RTP,2TP,B Q.#HQ-SHR,RXP,# P M-S4S-C@@+3 U,# *0$ @+3(S-2PV("LR,S4L,S8@0$ *( ER971U<FX@3T-) M7T-/3E1)3E5%.PH@?2 O*B!]?7T@*B\*( HK<W1A=&EC('9O:60@;V-I7W=R M:71E7VQO8E]F<F]M7W-T<F5A;2AP9&]?;V-I7W-T;70@*E,L('!D;U]O8VE? M8F]U;F1?<&%R86T@*E L('!H<%]S=')E86T@*G-T;2D**WL**PES:7IE7W0@ M;CL**PEU8C0@86UT+"!O9F9S970@/2 Q.PHK"6-H87(@*F-O;G-U;64["BL* M*PE/0TE,;V)/<&5N*%,M/D@M/G-V8RP@4RT^97)R+" H3T-)3&]B3&]C871O M<BHI4"T^=&AI;F<L($]#25],3T)?4D5!1%=2251%*3L**PHK"61O('L**PD) M8VAA<B!B=69;.#$Y,ET["BL)"6X@/2!P:'!?<W1R96%M7W)E860H<W1M+"!B M=68L('-I>F5O9BAB=68I*3L**PD):68@*"AI;G0I;B \/2 P*2!["BL)"0EB M<F5A:SL**PD)?0HK"0EC;VYS=6UE(#T@8G5F.PHK"0ED;R!["BL)"0EA;70@ M/2!N.PHK"0D)3T-)3&]B5W)I=&4H4RT^2"T^<W9C+"!3+3YE<G(L("A/0TE, M;V),;V-A=&]R*BE0+3YT:&EN9RP**PD)"0D))F%M="P@;V9F<V5T+"!C;VYS M=6UE+"!N+ HK"0D)"0E/0TE?3TY%7U!)14-%+ HK"0D)"0E.54Q,+"!.54Q, M+" P+"!344Q#4U])35!,24-)5"D["BL)"0EO9F9S970@*ST@86UT.PHK"0D) M;B M/2!A;70["BL)"0EC;VYS=6UE("L](&%M=#L**PD)?2!W:&EL92 H;BD[ M"BL)?2!W:&EL92 H,2D["BL)3T-)3&]B0VQO<V4H4RT^2"T^<W9C+"!3+3YE M<G(L("A/0TE,;V),;V-A=&]R*BE0+3YT:&EN9RD["BL)3T-)3&]B1FQU<VA" M=69F97(H4RT^2"T^<W9C+"!3+3YE<G(L("A/0TE,;V),;V-A=&]R*BE0+3YT M:&EN9RP@,"D["BM]"BL*('-T871I8R!I;G0@;V-I7W-T;71?<&%R86U?:&]O M:RAP9&]?<W1M=%]T("IS=&UT+"!S=')U8W0@<&1O7V)O=6YD7W!A<F%M7V1A M=&$@*G!A<F%M+"!E;G5M('!D;U]P87)A;5]E=F5N="!E=F5N=%]T>7!E(%13 M4DU,4U]$0RD@+RH@>WM[("HO"B!["B )<&1O7V]C:5]S=&UT("I3(#T@*'!D M;U]O8VE?<W1M="HI<W1M="T^9')I=F5R7V1A=&$["D! ("TR-#,L-B K,C<S M+#<@0$ *( EI9B H<&%R86TM/FES7W!A<F%M*2!["B )"7!D;U]O8VE?8F]U M;F1?<&%R86T@*E ["B )"7-B-"!V86QU95]S>B ]("TQ.PHK"0ES8C(@;&]B M7V%L;&]C7W1Y<&4@/2!344Q47T),3T(["B )"0H@"0E0(#T@*'!D;U]O8VE? M8F]U;F1?<&%R86TJ*7!A<F%M+3YD<FEV97)?9&%T83L*( I 0" M,C8T+#@@ M*S(Y-2PQ-2! 0 H@"0D)"0D)<F5T=7)N(# ["B *( D)"0D)8V%S92!01$]? M4$%204U?3$]".@HK"0D)"0D):68@*'!A<F%M+3YD<FEV97)?<&%R86US("$] M($Y53$PI('L**PD)"0D)"0EC;VYV97)T7W1O7VQO;F<H<&%R86TM/F1R:79E M<E]P87)A;7,I.PHK"BL)"0D)"0D):68@*"A:7TQ604Q?4"AP87)A;2T^9')I M=F5R7W!A<F%M<RD@)B!01$]?3T-)7U!!4D%-7T)?0TQ/0BD@/3T@4$1/7T]# M25]005)!35]"7T-,3T(I"BL)"0D)"0D)"6QO8E]A;&QO8U]T>7!E(#T@4U%, M5%]#3$]".PHK"0D)"0D)?0HK"B )"0D)"0DO*B!0+3YT:&EN9R!I<R!N;W<@ M86X@3T-)3&]B3&]C871O<B J("HO"BT)"0D)"0E0+3YO8VE?='EP92 ](%-1 M3%1?0DQ/0CL**PD)"0D)"5 M/F]C:5]T>7!E(#T@;&]B7V%L;&]C7W1Y<&4[ M"B )"0D)"0EV86QU95]S>B ]('-I>F5O9BA/0TE,;V),;V-A=&]R*BD["B ) M"0D)"0EB<F5A:SL*( I 0" M,S U+#D@*S,T,RPT,R! 0 H@"0D)"5 M/G5S M961?9F]R7V]U='!U=" ](# ["B )"0D):68@*%!$3U]005)!35]465!%*'!A M<F%M+3YP87)A;5]T>7!E*2 ]/2!01$]?4$%204U?3$]"*2!["B )"0D)"75B M-"!E;7!T>2 ](# ["BL)"0D)"6-H87(@8W)E871E9%]T96UP(#T@,#L**PH@ M"0D)"0E35$U47T-!3$PH3T-)1&5S8W)I<'1O<D%L;&]C+" H4RT^2"T^96YV M+" F4"T^=&AI;F<L($]#25]$5%E015],3T(L(# L($Y53$PI*3L*+0D)"0D) M4U1-5%]#04Q,*$]#24%T=')3970L("A0+3YT:&EN9RP@3T-)7T1465!%7TQ/ M0BP@)F5M<'1Y+" P+"!/0TE?05144E],3T)%35!462P@4RT^97)R*2D["B ) M"0D)"5,M/FAA=F5?8FQO8G,@/2 Q.PHK"BL)"0D)"6EF("A:7U194$5?4"AP M87)A;2T^<&%R86UE=&5R*2 A/2!)4U].54Q,*2!["BL)"0D)"0EC:&%R(&-R M96%T95]T96UP7VQO8B ](# ["BL)"0D)"0EU8C$@;&]B7W1Y<&4@/2!/0TE? M5$5-4%]"3$]".PHK"0D)"0D)<&AP7W-T<F5A;2 J<W1M(#T@3E5,3#L**PHK M"0D)"0D):68@*%I?5%E015]0*'!A<F%M+3YD<FEV97)?<&%R86US*2 A/2!) M4U].54Q,*2!["BL)"0D)"0D)8V]N=F5R=%]T;U]L;VYG*'!A<F%M+3YD<FEV M97)?<&%R86US*3L**PHK"0D)"0D)"6EF("@H6E],5D%,7U H<&%R86TM/F1R M:79E<E]P87)A;7,I("8@4$1/7T]#25]005)!35]"7T-,3T(I(#T](%!$3U]/ M0TE?4$%204U?0E]#3$]"*0HK"0D)"0D)"0EL;V)?='EP92 ]($]#25]414U0 M7T-,3T(["BL**PD)"0D)"0EI9B H*%I?3%9!3%]0*'!A<F%M+3YD<FEV97)? M<&%R86US*2 F(%!$3U]/0TE?4$%204U?0U)%051%7U1%35!?3$]"*2 ]/2!0 M1$]?3T-)7U!!4D%-7T-214%415]414U07TQ/0BD**PD)"0D)"0D)8W)E871E M7W1E;7!?;&]B(#T@,3L**PD)"0D)"7T**PHK"0D)"0D):68@*&-R96%T95]T M96UP7VQO8BD@>PHK"0D)"0D)"7!H<%]S=')E86U?9G)O;5]Z=F%L7VYO7W9E M<FEF>2AS=&TL("9P87)A;2T^<&%R86UE=&5R*3L**PHK"0D)"0D)"6EF("AS M=&TI('L**PD)"0D)"0D)4U1-5%]#04Q,*$]#24QO8D-R96%T951E;7!O<F%R M>2P@*%,M/D@M/G-V8RP@4RT^97)R+ HK"0D)"0D)"0D)"0DH3T-)3&]B3&]C M871O<BHI4"T^=&AI;F<L($]#25]$149!54Q4+"!/0TE?1$5&055,5"P**PD) M"0D)"0D)"0D);&]B7W1Y<&4L($]#25]!5%127TY/0T%#2$4L($]#25]$55)! M5$E/3E]315-324].*2D["BL**PD)"0D)"0D);V-I7W=R:71E7VQO8E]F<F]M M7W-T<F5A;2A3+"!0+"!S=&TI.PHK"0D)"0D)"0EP<FEN=&8H(F%F=&5R(&]C M:5]W<FET95]L;V)?9G)O;5]S=')E86T@-5QN(BD["BL)"0D)"0D)"6-R96%T M961?=&5M<" ](#$["BL)"0D)"0D)?0HK"0D)"0D)?0HK"0D)"0E]"BL**PD) M"0D):68@*"%C<F5A=&5D7W1E;7 I"BL)"0D)"0E35$U47T-!3$PH3T-)071T M<E-E="P@*% M/G1H:6YG+"!/0TE?1%194$5?3$]"+" F96UP='DL(# L($]# M25]!5%127TQ/0D5-4%19+"!3+3YE<G(I*3L*( D)"0E]"B )"0D)<F5T=7)N M(#$["B *0$ @+3,U-RPR-R K-#(Y+#<@0$ *( H@"0D)"0D)<&AP7W-T<F5A M;5]F<F]M7WIV86Q?;F]?=F5R:69Y*'-T;2P@)G!A<F%M+3YP87)A;65T97(I M.PH@"0D)"0D):68@*'-T;2D@>PHM"0D)"0D)"4]#24QO8D]P96XH4RT^2"T^ M<W9C+"!3+3YE<G(L("A/0TE,;V),;V-A=&]R*BE0+3YT:&EN9RP@3T-)7TQ/ M0E]214%$5U))5$4I.PHM"0D)"0D)"61O('L*+0D)"0D)"0D)8VAA<B!B=69; M.#$Y,ET["BT)"0D)"0D)"6X@/2!P:'!?<W1R96%M7W)E860H<W1M+"!B=68L M('-I>F5O9BAB=68I*3L*+0D)"0D)"0D):68@*"AI;G0I;B \/2 P*2!["BT) M"0D)"0D)"0EB<F5A:SL*+0D)"0D)"0D)?0HM"0D)"0D)"0EC;VYS=6UE(#T@ M8G5F.PHM"0D)"0D)"0ED;R!["BT)"0D)"0D)"0EA;70@/2!N.PHM"0D)"0D) M"0D)3T-)3&]B5W)I=&4H4RT^2"T^<W9C+"!3+3YE<G(L("A/0TE,;V),;V-A M=&]R*BE0+3YT:&EN9RP*+0D)"0D)"0D)"0D))F%M="P@;V9F<V5T+"!C;VYS M=6UE+"!N+ HM"0D)"0D)"0D)"0E/0TE?3TY%7U!)14-%+ HM"0D)"0D)"0D) M"0E.54Q,+"!.54Q,+" P+"!344Q#4U])35!,24-)5"D["BT)"0D)"0D)"0EO M9F9S970@*ST@86UT.PHM"0D)"0D)"0D);B M/2!A;70["BT)"0D)"0D)"0EC M;VYS=6UE("L](&%M=#L*+0D)"0D)"0D)?2!W:&EL92 H;BD["BT)"0D)"0D) M?2!W:&EL92 H,2D["BT)"0D)"0D)3T-)3&]B0VQO<V4H4RT^2"T^<W9C+"!3 M+3YE<G(L("A/0TE,;V),;V-A=&]R*BE0+3YT:&EN9RD["BT)"0D)"0D)3T-) M3&]B1FQU<VA"=69F97(H4RT^2"T^<W9C+"!3+3YE<G(L("A/0TE,;V),;V-A M=&]R*BE0+3YT:&EN9RP@,"D["BL)"0D)"0D);V-I7W=R:71E7VQO8E]F<F]M M7W-T<F5A;2A3+"!0+"!S=&TI.PH@"0D)"0D)?2!E;'-E(&EF("A:7U194$5? M4"AP87)A;2T^<&%R86UE=&5R*2 ]/2!)4U]35%))3D<I('L*( D)"0D)"0DO M*B!S=&EC:R!T:&4@<W1R:6YG(&EN=&\@=&AE($Q/0B J+PH@"0D)"0D)"6-O M;G-U;64@/2!:7U-44E9!3%]0*'!A<F%M+3YP87)A;65T97(I.PID:69F("UU M('!D;U]O8VE?8F%K+W!D;U]O8VDN8R!P9&]?;V-I+W!D;U]O8VDN8PHM+2T@ M<&1O7V]C:5]B86LO<&1O7V]C:2YC"3(P,#<M,#$M,#(@,3@Z,3<Z,3,N.#@W M-#8P-CDV("TP-3 P"BLK*R!P9&]?;V-I+W!D;U]O8VDN8PDR,# W+3 Q+3 R M(#$X.C$W.C0P+C,Q-S0T,C<R." M,#4P, I 0" M.3 L-B K.3 L.2! 0 H@ M("HO"B!02%!?34E.251?1E5.0U1)3TXH<&1O7V]C:2D*('L**PE214=)4U1% M4E]01$]?0TQ!4U-?0T].4U1?3$].1R@B3T-)7U!!4D%-7T)?0DQ/0B(L("AL M;VYG*5!$3U]/0TE?4$%204U?0E]"3$]"*3L**PE214=)4U1%4E]01$]?0TQ! M4U-?0T].4U1?3$].1R@B3T-)7U!!4D%-7T)?0TQ/0B(L("AL;VYG*5!$3U]/ M0TE?4$%204U?0E]#3$]"*3L**PE214=)4U1%4E]01$]?0TQ!4U-?0T].4U1? M3$].1R@B3T-)7U!!4D%-7T-214%415]414U07TQ/0B(L("AL;VYG*5!$3U]/ M0TE?4$%204U?0U)%051%7U1%35!?3$]"*3L*( EP:'!?<&1O7W)E9VES=&5R M7V1R:79E<B@F<&1O7V]C:5]D<FEV97(I.PH@"B C:68@2$%615]/0TE%3E9# M4D5!5$4*9&EF9B M=2!P9&]?;V-I7V)A:R]P:'!?<&1O7V]C:5]I;G0N:"!P M9&]?;V-I+W!H<%]P9&]?;V-I7VEN="YH"BTM+2!P9&]?;V-I7V)A:R]P:'!? M<&1O7V]C:5]I;G0N: DR,# W+3 Q+3 R(#$X.C$W.C U+C$V-C<X-C0T," M M,#4P, HK*RL@<&1O7V]C:2]P:'!?<&1O7V]C:5]I;G0N: DR,# W+3 Q+3 R M(#$X.C(U.C0W+C0W-#,X,S8Q-B M,#4P, I 0" M.3(L,R K.3(L.2! 0 H@ M#0H@97AT97)N('-T<G5C="!P9&]?<W1M=%]M971H;V1S(&]C:5]S=&UT7VUE M=&AO9',[#0H@#0HK96YU;2!P9&]?;V-I7W!A<F%M('L-"BL)4$1/7T]#25]0 M05)!35]"7T),3T(@/2 P># Q+ T**PE01$]?3T-)7U!!4D%-7T)?0TQ/0B ] M(#!X,#(L#0HK"5!$3U]/0TE?4$%204U?0U)%051%7U1%35!?3$]"(#T@,'@P ,-"P-"BM].PT**PT* ` end -- PECL development discussion Mailing List (http://pecl.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php


« previous php.pecl.dev (#4432) next »