Bug #80790 [Com]: PDO ODBC cannot insert BLOB records to Oracle database

Edit report at https://bugs.php.net/bug.php?id=80790&edit=1 ID: 80790 Comment by: horvath dot szabolcs at t-systems dot hu Reported by: horvath dot szabolcs at t-systems dot hu Summary: PDO ODBC cannot insert BLOB records to Oracle database Status: Open Type: Bug Package: PDO ODBC Operating System: Red Hat Enterprise Linux Server PHP Version: 7.3.27 Block user comment: N Private report: N New Comment: odbc-100.log (OK) [ODBC][9792][1614079677.410236][SQLConnect.c][3721] Entry: Connection = 0x55e8d43f3990 Server Name = [ORACLETEST][length = 11 (SQL_NTS)] User Name = [eberjegyzek][length = 11 (SQL_NTS)] Authentication = [********][length = 8 (SQL_NTS)] UNICODE Using encoding ASCII 'UTF-8' and UNICODE 'UTF16LE' [ODBC][9792][1614079677.517168][SQLConnect.c][4299] Exit:[SQL_SUCCESS] [ODBC][9792][1614079677.518316][SQLAllocHandle.c][540] Entry: Handle Type = 3 Input Handle = 0x55e8d43f3990 [ODBC][9792][1614079677.518526][SQLAllocHandle.c][1085] Exit:[SQL_SUCCESS] Output Handle = 0x55e8d43dc500 [ODBC][9792][1614079677.518650][SQLGetInfo.c][236] Entry: Connection = 0x55e8d43f3990 Info Type = SQL_FETCH_DIRECTION (8) Info Value = 0x7ffcaf54737c Buffer Length = 4 StrLen = (nil) [ODBC][9792][1614079677.518795][SQLSetStmtOption.c][197] Entry: Statement = 0x55e8d43dc500 Option = SQL_ATTR_CURSOR_TYPE Value = 3 [ODBC][9792][1614079677.519281][SQLSetStmtOption.c][477] Exit:[SQL_SUCCESS] [ODBC][9792][1614079677.519409][SQLPrepare.c][196] Entry: Statement = 0x55e8d43dc500 SQL = [INSERT INTO HSZ (ID, PDF) VALUES (SEQ_HSZ.NEXTVAL, ?)][length = 53 (SQL_NTS)] [ODBC][9792][1614079677.521374][SQLPrepare.c][377] Exit:[SQL_SUCCESS] [ODBC][9792][1614079677.521520][SQLNumParams.c][144] Entry: Statement = 0x55e8d43dc500 Param Count = 0x7f70520024e2 [ODBC][9792][1614079677.521644][SQLNumParams.c][231] Exit:[SQL_SUCCESS] Count = 0x7f70520024e2 -> 1 [ODBC][9792][1614079677.521738][SQLNumResultCols.c][156] Entry: Statement = 0x55e8d43dc500 Column Count = 0x7f70520024e0 [ODBC][9792][1614079677.522371][SQLNumResultCols.c][251] Exit:[SQL_SUCCESS] Count = 0x7f70520024e0 -> 0 [ODBC][9792][1614079677.522502][SQLDescribeParam.c][185] Entry: Statement = 0x55e8d43dc500 Parameter Number = 1 SQL Type = 0x7f7052076010 Param Def = 0x7f7052076018 Scale = 0x7f7052076012 Nullable = 0x7f7052076014 [ODBC][9792][1614079677.522627][SQLDescribeParam.c][338] Exit:[SQL_SUCCESS] SQL Type = 0x7ffcaf546e80 Param Def = 0x7ffcaf546f70 Scale = 0x7ffcaf547060 Nullable = 0x7ffcaf547150 [ODBC][9792][1614079677.522795][SQLBindParameter.c][217] Entry: Statement = 0x55e8d43dc500 Param Number = 1 Param Type = 1 C Type = -2 SQL_C_BINARY SQL Type = -4 SQL_LONGVARBINARY Col Def = 2147483647 Scale = 0 Rgb Value = 0x7f7052070098 Value Max = 0 StrLen Or Ind = 0x7f7052076020 [ODBC][9792][1614079677.522945][SQLBindParameter.c][434] Exit:[SQL_SUCCESS] [ODBC][9792][1614079677.523060][SQLFreeStmt.c][144] Entry: Statement = 0x55e8d43dc500 Option = 0 [ODBC][9792][1614079677.523234][SQLFreeStmt.c][266] Exit:[SQL_SUCCESS] [ODBC][9792][1614079677.523351][SQLExecute.c][187] Entry: Statement = 0x55e8d43dc500 [ODBC][9792][1614079677.533720][SQLExecute.c][357] Exit:[SQL_SUCCESS] [ODBC][9792][1614079677.533925][SQLFreeStmt.c][144] Entry: Statement = 0x55e8d43dc500 Option = 3 [ODBC][9792][1614079677.534414][SQLFreeStmt.c][266] Exit:[SQL_SUCCESS] [ODBC][9792][1614079677.534545][SQLNumResultCols.c][156] Entry: Statement = 0x55e8d43dc500 Column Count = 0x7f70520024e0 [ODBC][9792][1614079677.534675][SQLNumResultCols.c][251] Exit:[SQL_SUCCESS] Count = 0x7f70520024e0 -> 0 [ODBC][9792][1614079677.535041][SQLFreeStmt.c][144] Entry: Statement = 0x55e8d43dc500 Option = 1 [ODBC][9792][1614079677.535170][SQLFreeHandle.c][387] Entry: Handle Type = 3 Input Handle = 0x55e8d43dc500 [ODBC][9792][1614079677.535321][SQLFreeHandle.c][490] Exit:[SQL_SUCCESS] [ODBC][9792][1614079677.535448][SQLDisconnect.c][208] Entry: Connection = 0x55e8d43f3990 [ODBC][9792][1614079678.502042][SQLDisconnect.c][379] Exit:[SQL_SUCCESS] [ODBC][9792][1614079678.502154][SQLFreeHandle.c][290] Entry: Handle Type = 2 Input Handle = 0x55e8d43f3990 [ODBC][9792][1614079678.502207][SQLFreeHandle.c][339] Exit:[SQL_SUCCESS] [ODBC][9792][1614079678.502257][SQLFreeHandle.c][220] Entry: Handle Type = 1 Input Handle = 0x55e8d43f3390 odbc-10000.log (OK) [ODBC][9791][1614079733.784531][SQLConnect.c][3721] Entry: Connection = 0x55e8d43d9f50 Server Name = [ORACLETEST][length = 11 (SQL_NTS)] User Name = [eberjegyzek][length = 11 (SQL_NTS)] Authentication = [********][length = 8 (SQL_NTS)] UNICODE Using encoding ASCII 'UTF-8' and UNICODE 'UTF16LE' [ODBC][9791][1614079733.898430][SQLConnect.c][4299] Exit:[SQL_SUCCESS] [ODBC][9791][1614079733.899600][SQLAllocHandle.c][540] Entry: Handle Type = 3 Input Handle = 0x55e8d43d9f50 [ODBC][9791][1614079733.899794][SQLAllocHandle.c][1085] Exit:[SQL_SUCCESS] Output Handle = 0x55e8d4412450 [ODBC][9791][1614079733.899930][SQLGetInfo.c][236] Entry: Connection = 0x55e8d43d9f50 Info Type = SQL_FETCH_DIRECTION (8) Info Value = 0x7ffcaf54737c Buffer Length = 4 StrLen = (nil) [ODBC][9791][1614079733.900055][SQLSetStmtOption.c][197] Entry: Statement = 0x55e8d4412450 Option = SQL_ATTR_CURSOR_TYPE Value = 3 [ODBC][9791][1614079733.900171][SQLSetStmtOption.c][477] Exit:[SQL_SUCCESS] [ODBC][9791][1614079733.900286][SQLPrepare.c][196] Entry: Statement = 0x55e8d4412450 SQL = [INSERT INTO HSZ (ID, PDF) VALUES (SEQ_HSZ.NEXTVAL, ?)][length = 53 (SQL_NTS)] [ODBC][9791][1614079733.902089][SQLPrepare.c][377] Exit:[SQL_SUCCESS] [ODBC][9791][1614079733.902233][SQLNumParams.c][144] Entry: Statement = 0x55e8d4412450 Param Count = 0x7f70520024e2 [ODBC][9791][1614079733.902332][SQLNumParams.c][231] Exit:[SQL_SUCCESS] Count = 0x7f70520024e2 -> 1 [ODBC][9791][1614079733.902445][SQLNumResultCols.c][156] Entry: Statement = 0x55e8d4412450 Column Count = 0x7f70520024e0 [ODBC][9791][1614079733.902637][SQLNumResultCols.c][251] Exit:[SQL_SUCCESS] Count = 0x7f70520024e0 -> 0 [ODBC][9791][1614079733.902809][SQLDescribeParam.c][185] Entry: Statement = 0x55e8d4412450 Parameter Number = 1 SQL Type = 0x7f7052076010 Param Def = 0x7f7052076018 Scale = 0x7f7052076012 Nullable = 0x7f7052076014 [ODBC][9791][1614079733.902966][SQLDescribeParam.c][338] Exit:[SQL_SUCCESS] SQL Type = 0x7ffcaf546e80 Param Def = 0x7ffcaf546f70 Scale = 0x7ffcaf547060 Nullable = 0x7ffcaf547150 [ODBC][9791][1614079733.903108][SQLBindParameter.c][217] Entry: Statement = 0x55e8d4412450 Param Number = 1 Param Type = 1 C Type = -2 SQL_C_BINARY SQL Type = -4 SQL_LONGVARBINARY Col Def = 2147483647 Scale = 0 Rgb Value = 0x7f7052083018 Value Max = 0 StrLen Or Ind = 0x7f7052076020 [ODBC][9791][1614079733.903229][SQLBindParameter.c][434] Exit:[SQL_SUCCESS] [ODBC][9791][1614079733.903342][SQLFreeStmt.c][144] Entry: Statement = 0x55e8d4412450 Option = 0 [ODBC][9791][1614079733.903458][SQLFreeStmt.c][266] Exit:[SQL_SUCCESS] [ODBC][9791][1614079733.903552][SQLExecute.c][187] Entry: Statement = 0x55e8d4412450 [ODBC][9791][1614079733.913946][SQLExecute.c][357] Exit:[SQL_SUCCESS] [ODBC][9791][1614079733.914025][SQLFreeStmt.c][144] Entry: Statement = 0x55e8d4412450 Option = 3 [ODBC][9791][1614079733.914086][SQLFreeStmt.c][266] Exit:[SQL_SUCCESS] [ODBC][9791][1614079733.914135][SQLNumResultCols.c][156] Entry: Statement = 0x55e8d4412450 Column Count = 0x7f70520024e0 [ODBC][9791][1614079733.914188][SQLNumResultCols.c][251] Exit:[SQL_SUCCESS] Count = 0x7f70520024e0 -> 0 [ODBC][9791][1614079733.914322][SQLFreeStmt.c][144] Entry: Statement = 0x55e8d4412450 Option = 1 [ODBC][9791][1614079733.914377][SQLFreeHandle.c][387] Entry: Handle Type = 3 Input Handle = 0x55e8d4412450 [ODBC][9791][1614079733.914448][SQLFreeHandle.c][490] Exit:[SQL_SUCCESS] [ODBC][9791][1614079733.914507][SQLDisconnect.c][208] Entry: Connection = 0x55e8d43d9f50 [ODBC][9791][1614079734.880471][SQLDisconnect.c][379] Exit:[SQL_SUCCESS] [ODBC][9791][1614079734.880686][SQLFreeHandle.c][290] Entry: Handle Type = 2 Input Handle = 0x55e8d43d9f50 [ODBC][9791][1614079734.880859][SQLFreeHandle.c][339] Exit:[SQL_SUCCESS] [ODBC][9791][1614079734.881011][SQLFreeHandle.c][220] Entry: Handle Type = 1 Input Handle = 0x55e8d43d9950 Previous Comments: ------------------------------------------------------------------------ [2021-02-23 11:48:18] horvath dot szabolcs at t-systems dot hu Description: ------------ Hi, We're using PHP 7.3.20 from Red Hat Software Collections. Although 7.3.27 is out there, I read thoroughly the changelog and haven't found similar bug between 7.3.20 and 7.3.27. There is a BLOB column in the database (Oracle 19c) which can be inserted with php-odbc and can't with php-pdo. We have a test case (see next chapter). The Oracle ODBC trace outputs for both PDO and ODBC version have been attached. Testfile: dd if=/dev/urandom of=testfile.txt bs={desired_size} count=1 We try to upload blob content the way as outlined in the manual (https://www.php.net/manual/en/pdo.lobs.php - Example #3 Inserting an image into a database: Oracle) There are at least two different problems: 1) php-pdo blob inserts silenty fails when the blob content is smaller than 3997 bytes. The manual says "It's also essential that you perform the insert under a transaction, otherwise your newly inserted LOB will be committed with a zero-length as part of the implicit commit that happens when the query is executed" ....okay, but we inserted under a transaction. What are we missing here? 2) php-pdo blob inserts fails with "[[Oracle][ODBC][Ora]ORA-01461: can bind a LONG value only for insert into a LONG column" error messages when the blob content is greater (or equal) than 3997 bytes. Test script: --------------- -- ODBC test script: -- $conn = odbc_connect("ORACLETEST", "eberjegyzek", "secret"); $imageBlob = file_get_contents("testfile.txt"); $query = "INSERT INTO HSZ (ID, PDF) VALUES (SEQ_HSZ.NEXTVAL, ?)"; $stmt = odbc_prepare($conn, $query); odbc_execute($stmt, [$imageBlob]) or die('Error, query failed'); -- PDO test script: -- $db = new \PDO("odbc:ORACLETEST", "eberjegyzek", "secret"); $stmt = $db->prepare("INSERT INTO HSZ(ID, PDF) VALUES(SEQ_HSZ.NEXTVAL, EMPTY_BLOB()) RETURNING PDF INTO ?"); $imageBlob = file_get_contents("testfile.txt"); $stmt->bindParam(1, $imageBlob, PDO::PARAM_LOB); $db->beginTransaction(); $stmt->execute(); $db->commit(); Test cases: SQL> select id, length(pdf), dbms_lob.getlength(pdf) FROM hsz order by ID; +---------+--------------+-------------------------+ | ID | LENGTH(PDF) | DBMS_LOB.GETLENGTH(PDF) | +------------------------+-------------------------+ | 376693 | 100 | 100 | Test case #1 php-odbc with 100 bytes of input (OK) | 376694 | 10000 | 10000 | Test case #2 php-odbc with 10000 bytes of input (OK) | 376695 | 0 | 0 | Test case #3 php-pdo with 100 bytes of input (not OK, there is no data in the PDF column) | 376696 | 0 | 0 | Test case #4 php-pdo with 3996 bytes of input (not OK, there is no data in the PDF columns) Test case #5 php-pdo with 3997 bytes of input (completely missing from the table, ORA-01461: can bind a LONG value only for insert into a LONG column) +---------+--------------+-------------------------+ Expected result: ---------------- see above (Test cases) Actual result: -------------- see above (Test cases) ------------------------------------------------------------------------ -- Edit this bug report at https://bugs.php.net/bug.php?id=80790&edit=1