|
|
php.net | support | documentation | report a bug | advanced search | search howto | statistics | random bug | login |
[2006-06-20 05:40 UTC] ikhatib at imail dot de
Description:
------------
I'm trying to insert data into a CLOB field with PDO, but I always get an error (working with a BLOB instead of a CLOB works, though):
OCIStmtExecute: ORA-00932: Inkonsistente Datentypen: BLOB erwartet, CLOB erhalten (ext\pdo_oci\oci_statement.c:142)
That would be in english:
OCIStmtExecute: ORA-00932: inconsistent datatypes: expected BLOB got CLOB (ext\pdo_oci\oci_statement.c:142)
I'm using PHP 5.1.4 and a full Oracle 10g client under Windows XP.
Reproduce code:
---------------
php:
/* TABLE STRUCTURE:
================
TEXT CLOB
DIGIT NUMBER(10) */
try { $db = new PDO('oci:dbname=oracle_test;charset=UTF-8', 'scott', 'tiger'); }
catch (PDOException $e) { echo 'Failed to obtain database handle ' . $e->getMessage(); exit; }
$stmt = $db->prepare("insert into UTF8TEST (text, digit) " .
"VALUES (EMPTY_CLOB(), ?) ".
"RETURNING text INTO ?");
$fp = fopen('utf8text.txt', 'rb');
$digit = '12345';
$stmt->bindParam(1, $digit);
$stmt->bindParam(2, $fp, PDO::PARAM_LOB);
$db->beginTransaction();
if ( $stmt->execute() ) { echo "Successfully inserted UTF-8 into table\n"; } else { print_r($stmt->errorInfo()); }
$db->commit();
Expected result:
----------------
The CLOB should be inserted into the field without problems.
Actual result:
--------------
Error:
OCIStmtExecute: ORA-00932: Inkonsistente Datentypen: BLOB erwartet, CLOB erhalten (ext\pdo_oci\oci_statement.c:142)
That would be in english:
OCIStmtExecute: ORA-00932: inconsistent datatypes: expected BLOB got CLOB (ext\pdo_oci\oci_statement.c:142)
PatchesPull RequestsHistoryAllCommentsChangesGit/SVN commits
|
|||||||||||||||||||||||||||||||||||||
Copyright © 2001-2024 The PHP GroupAll rights reserved. |
Last updated: Sat Apr 20 00:01:27 2024 UTC |
Hello, I got the same problem on RedHat Linux, too. I know this BLOB/CLOB problem is written in Roadmap for PDO, but PDO_OCI has not been upgraded since last year. I really need PDO_OCI works fine with CLOB columns right now. Since I don't know how you will implement PDO::PARAM_CLOB, I decided to make a patch for PDO_OCI using new attribute as 'PDO::OCI_ATTR_USE_BLOB_FOR_CLOB'. But I still need official fixed PDO_OCI, so could you merge fixing to cvs? I hope my patch helps you. The patch I made is following: ----^ php-5.2.0.PDO_OCI_ATTR_USE_BLOB_FOR_CLOB.patch ----8<----8<----8<---- BEGIN ----8<----<< cut here diff -ur php-5.2.0.orig/ext/pdo_oci/oci_statement.c php-5.2.0.PDO_OCI_ATTR_USE_BLOB_FOR_CLOB/ext/pdo_oci/oci_statement.c --- php-5.2.0.orig/ext/pdo_oci/oci_statement.c 2006-03-19 07:06:30.000000000 +0900 +++ php-5.2.0.PDO_OCI_ATTR_USE_BLOB_FOR_CLOB/ext/pdo_oci/oci_statement.c 2006-12-13 19:16:26.000000000 +0900 @@ -265,7 +265,11 @@ case PDO_PARAM_LOB: /* P->thing is now an OCILobLocator * */ - P->oci_type = SQLT_BLOB; + if ( pdo_attr_lval(param->driver_params, PDO_OCI_ATTR_USE_BLOB_FOR_CLOB, 0 TSRMLS_CC) ){ + P->oci_type = SQLT_BLOB; + } else { + P->oci_type = SQLT_CLOB; + } value_sz = sizeof(OCILobLocator*); break; diff -ur php-5.2.0.orig/ext/pdo_oci/pdo_oci.c php-5.2.0.PDO_OCI_ATTR_USE_BLOB_FOR_CLOB/ext/pdo_oci/pdo_oci.c --- php-5.2.0.orig/ext/pdo_oci/pdo_oci.c 2006-01-01 21:50:12.000000000 +0900 +++ php-5.2.0.PDO_OCI_ATTR_USE_BLOB_FOR_CLOB/ext/pdo_oci/pdo_oci.c 2006-12-13 17:35:57.000000000 +0900 @@ -90,6 +90,8 @@ */ PHP_MINIT_FUNCTION(pdo_oci) { + REGISTER_PDO_CLASS_CONST_LONG("OCI_ATTR_USE_BLOB_FOR_CLOB", (long)PDO_OCI_ATTR_USE_BLOB_FOR_CLOB); + php_pdo_register_driver(&pdo_oci_driver); #if HAVE_OCIENVCREATE diff -ur php-5.2.0.orig/ext/pdo_oci/php_pdo_oci_int.h php-5.2.0.PDO_OCI_ATTR_USE_BLOB_FOR_CLOB/ext/pdo_oci/php_pdo_oci_int.h --- php-5.2.0.orig/ext/pdo_oci/php_pdo_oci_int.h 2006-01-01 21:50:12.000000000 +0900 +++ php-5.2.0.PDO_OCI_ATTR_USE_BLOB_FOR_CLOB/ext/pdo_oci/php_pdo_oci_int.h 2006-12-13 20:19:40.000000000 +0900 @@ -92,3 +92,7 @@ extern struct pdo_stmt_methods oci_stmt_methods; +enum { + PDO_OCI_ATTR_USE_BLOB_FOR_CLOB = PDO_ATTR_DRIVER_SPECIFIC, +}; + ----$ php-5.2.0.PDO_OCI_ATTR_USE_BLOB_FOR_CLOB.patch ----8<----8<----8<---- END ----8<----<< cut here The sample script using 'PDO::OCI_ATTR_USE_BLOB_FOR_CLOB' is following: ----^ sample.php ----8<----8<----8<---- BEGIN ----8<----<< cut here <?php // create db object for pdo_oci $db = new PDO('oci:dbname=ORCL', 'scott', 'tiger'); // prepare statement $stmt = $db->prepare('INSERT INTO tbtemp ' . ' ( id , clobdata , blobdata ) ' . 'VALUES ( :id , EMPTY_CLOB() , EMPTY_BLOB() ) ' . 'RETURNING clobdata , blobdata ' . 'INTO :clobdata , :blobdata ' ); // make test data $id = 1; $fp = fopen(__FILE__, 'rb'); // bind params $stmt->bindParam(':id', $id); $stmt->bindParam(':clobdata', $fp, PDO::PARAM_LOB, 1, array(PDO::OCI_ATTR_USE_BLOB_FOR_CLOB => false)); $stmt->bindParam(':blobdata', $fp, PDO::PARAM_LOB, 1, array(PDO::OCI_ATTR_USE_BLOB_FOR_CLOB => true)); // execute statement $db->beginTransaction(); $stmt->execute(); $db->commit(); ?> ----$ sample.php ----8<----8<----8<---- END ----8<----<< cut hereI'm sorry that my message has been turned up. I'll try once again. Hello, I got the same problem on RedHat Linux, too. I know this BLOB/CLOB problem is written in Roadmap for PDO, but PDO_OCI has not been upgraded since last year. I really need PDO_OCI works fine with CLOB columns right now. Since I don't know how you will implement PDO::PARAM_CLOB, I decided to make a patch for PDO_OCI using new attribute as 'PDO::OCI_ATTR_USE_BLOB_FOR_CLOB'. But I still need official fixed PDO_OCI, so could you merge fixing to cvs? I hope my patch helps you. The patch I made is following: ----^ php-5.2.0.PDO_OCI_ATTR_USE_BLOB_FOR_CLOB.patch ----8<----8<----8<---- BEGIN ----8<----<< cut here diff -ur php-5.2.0.orig/ext/pdo_oci/oci_statement.c php-5.2.0.PDO_OCI_ATTR_USE_BLOB_FOR_CLOB/ext/pdo_oci/oci_statement.c --- php-5.2.0.orig/ext/pdo_oci/oci_statement.c 2006-03-19 07:06:30.000000000 +0900 +++ php-5.2.0.PDO_OCI_ATTR_USE_BLOB_FOR_CLOB/ext/pdo_oci/oci_statement.c 2006-12-13 19:16:26.000000000 +0900 @@ -265,7 +265,11 @@ case PDO_PARAM_LOB: /* P->thing is now an OCILobLocator * */ - P->oci_type = SQLT_BLOB; + if ( pdo_attr_lval(param->driver_params, PDO_OCI_ATTR_USE_BLOB_FOR_CLOB, 0 TSRMLS_CC) ){ + P->oci_type = SQLT_BLOB; + } else { + P->oci_type = SQLT_CLOB; + } value_sz = sizeof(OCILobLocator*); break; diff -ur php-5.2.0.orig/ext/pdo_oci/pdo_oci.c php-5.2.0.PDO_OCI_ATTR_USE_BLOB_FOR_CLOB/ext/pdo_oci/pdo_oci.c --- php-5.2.0.orig/ext/pdo_oci/pdo_oci.c 2006-01-01 21:50:12.000000000 +0900 +++ php-5.2.0.PDO_OCI_ATTR_USE_BLOB_FOR_CLOB/ext/pdo_oci/pdo_oci.c 2006-12-13 17:35:57.000000000 +0900 @@ -90,6 +90,8 @@ */ PHP_MINIT_FUNCTION(pdo_oci) { + REGISTER_PDO_CLASS_CONST_LONG("OCI_ATTR_USE_BLOB_FOR_CLOB", (long)PDO_OCI_ATTR_USE_BLOB_FOR_CLOB); + php_pdo_register_driver(&pdo_oci_driver); #if HAVE_OCIENVCREATE diff -ur php-5.2.0.orig/ext/pdo_oci/php_pdo_oci_int.h php-5.2.0.PDO_OCI_ATTR_USE_BLOB_FOR_CLOB/ext/pdo_oci/php_pdo_oci_int.h --- php-5.2.0.orig/ext/pdo_oci/php_pdo_oci_int.h 2006-01-01 21:50:12.000000000 +0900 +++ php-5.2.0.PDO_OCI_ATTR_USE_BLOB_FOR_CLOB/ext/pdo_oci/php_pdo_oci_int.h 2006-12-13 20:19:40.000000000 +0900 @@ -92,3 +92,7 @@ extern struct pdo_stmt_methods oci_stmt_methods; +enum { + PDO_OCI_ATTR_USE_BLOB_FOR_CLOB = PDO_ATTR_DRIVER_SPECIFIC, +}; + ----$ php-5.2.0.PDO_OCI_ATTR_USE_BLOB_FOR_CLOB.patch ----8<----8<----8<---- END ----8<----<< cut here The sample script using 'PDO::OCI_ATTR_USE_BLOB_FOR_CLOB' is following: ----^ sample.php ----8<----8<----8<---- BEGIN ----8<----<< cut here <?php // create db object for pdo_oci $db = new PDO('oci:dbname=ORCL', 'scott', 'tiger'); // prepare statement $stmt = $db->prepare('INSERT INTO tbtemp ' . ' ( id , clobdata , blobdata ) ' . 'VALUES ( :id , EMPTY_CLOB() , EMPTY_BLOB() ) ' . 'RETURNING clobdata , blobdata ' . 'INTO :clobdata , :blobdata ' ); // make test data $id = 1; $fp = fopen(__FILE__, 'rb'); // bind params $stmt->bindParam(':id', $id); $stmt->bindParam(':clobdata', $fp, PDO::PARAM_LOB, 1, array(PDO::OCI_ATTR_USE_BLOB_FOR_CLOB => false)); $stmt->bindParam(':blobdata', $fp, PDO::PARAM_LOB, 1, array(PDO::OCI_ATTR_USE_BLOB_FOR_CLOB => true)); // execute statement $db->beginTransaction(); $stmt->execute(); $db->commit(); ?> ----$ sample.php ----8<----8<----8<---- END ----8<----<< cut hereJust to ensure that my patch does not get lost, I am copying it below. This patch goes further than the one above by allowing IN BLOB/CLOBs to work on stored procedures. It also allows BLOBs/CLOBs to be inserted in a table without using the RETURNING clause trick. SAMPLE CODE ----------- -- ORACLE CREATE OR REPLACE PROCEDURE TEST_PDO(value2 IN OUT CLOB) AS BEGIN insert into pdoclob(value) values( dbms_lob.substr( value2, 50 ) ); dbms_lob.writeAppend( value2, 8, ' worked!' ); END; -- PHP <?php $connection = new PDO( 'oci:dbname=mydb', 'user', 'pass' ); $clob = fopen( '/tmp/testpdo.txt', 'r+b' ); /**** * testpdo.txt contains the string "clob input/output" **** $s = $connection->prepare( 'begin test_pdo( ? ); end;' ); $s->bindParam( 1, $clob, PDO::PARAM_LOB | PDO::PARAM_INPUT_OUTPUT, 0, array( PDO::OCI_PARAM_B_CLOB => true, PDO::OCI_PARAM_CREATE_TEMP_LOB => true ) ); $connection->beginTransaction(); $s->execute(); $connection->commit(); echo stream_get_contents( $clob ); // will print "clob input/ouput worked!" ?> PATCH ----- diff -u pdo_oci_bak/oci_statement.c pdo_oci/oci_statement.c --- pdo_oci_bak/oci_statement.c 2007-01-02 18:16:52.000000000 -0500 +++ pdo_oci/oci_statement.c 2007-01-03 16:12:51.229160712 -0500 @@ -235,6 +235,70 @@ return OCI_CONTINUE; } /* }}} */ +static void oci_write_lob_from_stream(pdo_oci_stmt *S, pdo_oci_bound_param *P, php_stream *stm, char open_lob, char close_lob) +{ + size_t n; + ub4 amt, offset = 1; + char *consume; + + if (open_lob) { + OCILobOpen(S->H->svc, S->err, (OCILobLocator*)P->thing, OCI_LOB_READWRITE); + } + + do { + char buf[8192]; + n = php_stream_read(stm, buf, sizeof(buf)); + if ((int)n <= 0) { + break; + } + consume = buf; + do { + amt = n; + OCILobWrite(S->H->svc, S->err, (OCILobLocator*)P->thing, + &amt, offset, consume, n, + OCI_ONE_PIECE, + NULL, NULL, 0, SQLCS_IMPLICIT); + offset += amt; + n -= amt; + consume += amt; + } while (n); + } while (1); + + if (close_lob) { + OCILobClose(S->H->svc, S->err, (OCILobLocator*)P->thing); + } + + OCILobFlushBuffer(S->H->svc, S->err, (OCILobLocator*)P->thing, 0); +} + +static void oci_write_lob_from_string(pdo_oci_stmt *S, pdo_oci_bound_param *P, zval *value, char open_lob, char close_lob) +{ + /* stick the string into the LOB */ + ub4 amt, offset = 1; + char *consume = Z_STRVAL_P(value); + size_t n = Z_STRLEN_P(value); + + if (n) { + if (open_lob) { + OCILobOpen(S->H->svc, S->err, (OCILobLocator*)P->thing, OCI_LOB_READWRITE); + } + + while (n) { + amt = n; + OCILobWrite(S->H->svc, S->err, (OCILobLocator*)P->thing, + &amt, offset, consume, n, + OCI_ONE_PIECE, + NULL, NULL, 0, SQLCS_IMPLICIT); + consume += amt; + n -= amt; + } + + if (close_lob) { + OCILobClose(S->H->svc, S->err, (OCILobLocator*)P->thing); + } + } +} + static int oci_stmt_param_hook(pdo_stmt_t *stmt, struct pdo_bound_param_data *param, enum pdo_param_event event_type TSRMLS_DC) /* {{{ */ { pdo_oci_stmt *S = (pdo_oci_stmt*)stmt->driver_data; @@ -243,7 +307,13 @@ if (param->is_param) { pdo_oci_bound_param *P; sb4 value_sz = -1; - + sb2 lob_alloc_type = SQLT_BLOB; + + /* make sure any driver parameters are passed as an array */ + if (param->driver_params) { + convert_to_array_ex(¶m->driver_params); + } + P = (pdo_oci_bound_param*)param->driver_data; switch (event_type) { @@ -264,8 +334,12 @@ return 0; case PDO_PARAM_LOB: + if (pdo_attr_lval(param->driver_params, PDO_OCI_PARAM_B_CLOB, 0 TSRMLS_CC) == 1) { + lob_alloc_type = SQLT_CLOB; + } + /* P->thing is now an OCILobLocator * */ - P->oci_type = SQLT_BLOB; + P->oci_type = lob_alloc_type; value_sz = sizeof(OCILobLocator*); break; @@ -305,9 +379,43 @@ P->used_for_output = 0; if (PDO_PARAM_TYPE(param->param_type) == PDO_PARAM_LOB) { ub4 empty = 0; + char created_temp = 0; + STMT_CALL(OCIDescriptorAlloc, (S->H->env, &P->thing, OCI_DTYPE_LOB, 0, NULL)); - STMT_CALL(OCIAttrSet, (P->thing, OCI_DTYPE_LOB, &empty, 0, OCI_ATTR_LOBEMPTY, S->err)); S->have_blobs = 1; + + /* check if a temporary LOB should be created */ + if (pdo_attr_lval(param->driver_params, PDO_OCI_PARAM_CREATE_TEMP_LOB, 0 TSRMLS_CC) == 1) { + ub1 lob_type = OCI_TEMP_BLOB; + php_stream *stm = NULL; + + /* ensure that the parameter is already a stream */ + php_stream_from_zval_no_verify(stm, ¶m->parameter); + + if (stm || Z_TYPE_P(param->parameter) == IS_STRING) { + char close_lob = ((param->param_type & PDO_PARAM_INPUT_OUTPUT) != PDO_PARAM_INPUT_OUTPUT); + + if (pdo_attr_lval(param->driver_params, PDO_OCI_PARAM_B_CLOB, 0 TSRMLS_CC) == 1) { + lob_type = OCI_TEMP_CLOB; + } + + STMT_CALL(OCILobCreateTemporary, (S->H->svc, S->err, + (OCILobLocator*)P->thing, OCI_DEFAULT, OCI_DEFAULT, + lob_type, OCI_ATTR_NOCACHE, OCI_DURATION_SESSION)); + + if (stm) { + oci_write_lob_from_stream(S, P, stm, 1, close_lob); + } + else /* IS_STRING */ { + oci_write_lob_from_string(S, P, param->parameter, 1, close_lob); + } + + created_temp = 1; + } + } + + if (!created_temp) + STMT_CALL(OCIAttrSet, (P->thing, OCI_DTYPE_LOB, &empty, 0, OCI_ATTR_LOBEMPTY, S->err)); } return 1; @@ -351,52 +459,32 @@ } } else { /* we're a LOB being used for insert; transfer the data now */ - size_t n; - ub4 amt, offset = 1; - char *consume; + char is_temp = pdo_attr_lval(param->driver_params, PDO_OCI_PARAM_CREATE_TEMP_LOB, 0 TSRMLS_CC); - php_stream_from_zval_no_verify(stm, ¶m->parameter); - if (stm) { - OCILobOpen(S->H->svc, S->err, (OCILobLocator*)P->thing, OCI_LOB_READWRITE); - do { - char buf[8192]; - n = php_stream_read(stm, buf, sizeof(buf)); - if ((int)n <= 0) { - break; - } - consume = buf; - do { - amt = n; - OCILobWrite(S->H->svc, S->err, (OCILobLocator*)P->thing, - &amt, offset, consume, n, - OCI_ONE_PIECE, - NULL, NULL, 0, SQLCS_IMPLICIT); - offset += amt; - n -= amt; - consume += amt; - } while (n); - } while (1); - OCILobClose(S->H->svc, S->err, (OCILobLocator*)P->thing); - OCILobFlushBuffer(S->H->svc, S->err, (OCILobLocator*)P->thing, 0); - } else if (Z_TYPE_P(param->parameter) == IS_STRING) { - /* stick the string into the LOB */ - consume = Z_STRVAL_P(param->parameter); - n = Z_STRLEN_P(param->parameter); - if (n) { - OCILobOpen(S->H->svc, S->err, (OCILobLocator*)P->thing, OCI_LOB_READWRITE); - while (n) { - amt = n; - OCILobWrite(S->H->svc, S->err, (OCILobLocator*)P->thing, - &amt, offset, consume, n, - OCI_ONE_PIECE, - NULL, NULL, 0, SQLCS_IMPLICIT); - consume += amt; - n -= amt; + if (!is_temp) { + php_stream_from_zval_no_verify(stm, ¶m->parameter); + if (stm) { + oci_write_lob_from_stream(S, P, stm, 1, 1); + } else if (Z_TYPE_P(param->parameter) == IS_STRING) { + oci_write_lob_from_string(S, P, param->parameter, 1, 1); + } + + OCIDescriptorFree(P->thing, OCI_DTYPE_LOB); + } else { + /* free this temporary LOB if it was just an IN parameter */ + if ((param->param_type & PDO_PARAM_INPUT_OUTPUT) != PDO_PARAM_INPUT_OUTPUT) { + OCILobFreeTemporary(S->H->svc, S->err, (OCILobLocator*)P->thing); + } else { + /* create a brand new stream and replace the former stream parameter with it */ + + stm = oci_create_lob_stream(stmt, (OCILobLocator*)P->thing TSRMLS_CC); + if (stm) { + OCILobOpen(S->H->svc, S->err, (OCILobLocator*)P->thing, OCI_LOB_READWRITE); + php_stream_to_zval(stm, param->parameter); } - OCILobClose(S->H->svc, S->err, (OCILobLocator*)P->thing); } } - OCIDescriptorFree(P->thing, OCI_DTYPE_LOB); + P->thing = NULL; } } diff -u pdo_oci_bak/pdo_oci.c pdo_oci/pdo_oci.c --- pdo_oci_bak/pdo_oci.c 2007-01-02 18:17:13.000000000 -0500 +++ pdo_oci/pdo_oci.c 2007-01-03 16:12:51.229160712 -0500 @@ -90,6 +90,8 @@ */ PHP_MINIT_FUNCTION(pdo_oci) { + REGISTER_PDO_CLASS_CONST_LONG("OCI_PARAM_B_CLOB", (long)PDO_OCI_PARAM_B_CLOB); + REGISTER_PDO_CLASS_CONST_LONG("OCI_PARAM_CREATE_TEMP_LOB", (long)PDO_OCI_PARAM_CREATE_TEMP_LOB); php_pdo_register_driver(&pdo_oci_driver); #if HAVE_OCIENVCREATE diff -u pdo_oci_bak/php_pdo_oci_int.h pdo_oci/php_pdo_oci_int.h --- pdo_oci_bak/php_pdo_oci_int.h 2007-01-02 18:17:05.000000000 -0500 +++ pdo_oci/php_pdo_oci_int.h 2007-01-03 16:14:40.992474152 -0500 @@ -92,3 +92,8 @@ extern struct pdo_stmt_methods oci_stmt_methods; +enum pdo_oci_param { + PDO_OCI_PARAM_B_CLOB = PDO_ATTR_DRIVER_SPECIFIC, + PDO_OCI_PARAM_CREATE_TEMP_LOB, +}; +A coworker discovered the solution. When dealing with CLOBs in Oracle using PDO, don't treat it as a LOB. You need to bind it as a PDO::PARAM_STR, and give it the length of the string (that 4th parameter is the key, it fails with an error message about LONG type otherwise). Here is an example of how to successfully insert into a CLOB in Oracle: <?php /* CREATE TABLE clob_test (my_clob CLOB) */ $big_string = ""; for ($i=0; $i < 10000; $i++) $big_string .= rand(100000,999999)."\n"; try { $pdo = new PDO("oci:dbname=TESTDB", "TESTUSER", "TESTPW"); $stmt = $pdo->prepare("INSERT INTO healthbit.clob_test (my_clob) VALUES (:cl)"); $stmt->bindParam(":cl", $big_string, PDO::PARAM_STR, strlen($big_string)); $pdo->beginTransaction(); if (!$stmt->execute()) { echo "ERROR: ".print_r($stmt->errorInfo())."\n"; $pdo->rollBack(); exit; } $pdo->commit(); $stmt = $pdo->prepare("SELECT my_clob FROM healthbit.clob_test"); $stmt->execute(); $row = $stmt->fetch(); $str = ""; while ($tmp = fread($row[0],1024)) $str .= $tmp; echo strlen($str); // prints 70000 } catch (Exception $e) { echo "ERROR: "; echo $e->getMessage(); $pdo->rollBack(); }