php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #80790 PDO ODBC cannot insert BLOB records to Oracle database
Submitted: 2021-02-23 11:48 UTC Modified: 2021-02-23 17:37 UTC
From: horvath dot szabolcs at t-systems dot hu Assigned: cmb (profile)
Status: Not a bug Package: PDO ODBC
PHP Version: 7.3.27 OS: Red Hat Enterprise Linux Server
Private report: No CVE-ID: None
View Developer Edit
Welcome! If you don't have a Git account, you can't do anything here.
If you reported this bug, you can edit this bug over here.
(description)
Block user comment
Status: Assign to:
Package:
Bug Type:
Summary:
From: horvath dot szabolcs at t-systems dot hu
New email:
PHP Version: OS:

 

 [2021-02-23 11:48 UTC] 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)

Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2021-02-23 11:49 UTC] horvath dot szabolcs at t-systems dot hu
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
 [2021-02-23 11:50 UTC] horvath dot szabolcs at t-systems dot hu
pdo-100.log (BLOB column 0 bytes long after insert)

[ODBC][9810][1614079785.407589][SQLConnect.c][3721]
                Entry:
                        Connection = 0x55e8d4313ea0
                        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][9810][1614079785.532017][SQLConnect.c][4299]
                Exit:[SQL_SUCCESS]
[ODBC][9810][1614079785.532353][SQLAllocHandle.c][540]
                Entry:
                        Handle Type = 3
                        Input Handle = 0x55e8d4313ea0
[ODBC][9810][1614079785.532611][SQLAllocHandle.c][1085]
                Exit:[SQL_SUCCESS]
                        Output Handle = 0x55e8d442c780
[ODBC][9810][1614079785.532881][SQLPrepare.c][196]
                Entry:
                        Statement = 0x55e8d442c780
                        SQL = [INSERT INTO HSZ(ID, PDF) VALUES(SEQ_HSZ.NEXTVAL, EMPTY_BLOB()) RETURNING PDF INTO ?][length = 83 (SQL_NTS)]
[ODBC][9810][1614079785.534935][SQLPrepare.c][377]
                Exit:[SQL_SUCCESS]
[ODBC][9810][1614079785.535794][SQLDescribeParam.c][185]
                Entry:
                        Statement = 0x55e8d442c780
                        Parameter Number = 1
                        SQL Type = 0x7ffcaf547152
                        Param Def = 0x7ffcaf547158
                        Scale = 0x7ffcaf547154
                        Nullable = 0x7ffcaf547156
[ODBC][9810][1614079785.535949][SQLDescribeParam.c][338]
                Exit:[SQL_SUCCESS]
                        SQL Type = 0x7ffcaf546c40
                        Param Def = 0x7ffcaf546d30
                        Scale = 0x7ffcaf546e20
                        Nullable = 0x7ffcaf546f10
[ODBC][9810][1614079785.536076][SQLBindParameter.c][217]
                Entry:
                        Statement = 0x55e8d442c780
                        Param Number = 1
                        Param Type = 1
                        C Type = 1 SQL_C_CHAR
                        SQL Type = 12 SQL_VARCHAR
                        Col Def = 999
                        Scale = 0
                        Rgb Value = 0x7f705200b230
                        Value Max = 0
                        StrLen Or Ind = 0x7f70520013e0
[ODBC][9810][1614079785.536200][SQLBindParameter.c][434]
                Exit:[SQL_SUCCESS]
[ODBC][9810][1614079785.536339][SQLSetConnectAttr.c][399]
                Entry:
                        Connection = 0x55e8d4313ea0
                        Attribute = SQL_ATTR_AUTOCOMMIT
                        Value = (nil)
                        StrLen = -6
[ODBC][9810][1614079785.536461][SQLSetConnectAttr.c][862]
                Exit:[SQL_SUCCESS]
[ODBC][9810][1614079785.536692][SQLExecute.c][187]
                Entry:
                        Statement = 0x55e8d442c780
[ODBC][9810][1614079785.536850][SQLExecute.c][357]
                Exit:[SQL_NEED_DATA]
[ODBC][9810][1614079785.536973][SQLParamData.c][166]
                Entry:
                        Statement = 0x55e8d442c780
                        Value = 0x7ffcaf5472d0
[ODBC][9810][1614079785.537218][SQLParamData.c][362]
                Exit:[SQL_NEED_DATA]
                        Value = 0x7f705200b230
[ODBC][9810][1614079785.537349][SQLPutData.c][148]
                Entry:
                        Statement = 0x55e8d442c780
                        Data = 0x7f7052070098
                        StrLen = 100
[ODBC][9810][1614079785.537608][SQLPutData.c][315]
                Exit:[SQL_SUCCESS]
[ODBC][9810][1614079785.537729][SQLParamData.c][166]
                Entry:
                        Statement = 0x55e8d442c780
                        Value = 0x7ffcaf5472d0
[ODBC][9810][1614079785.539905][SQLParamData.c][362]
                Exit:[SQL_SUCCESS]
                        Value = 0x7f705200b230
[ODBC][9810][1614079785.540037][SQLRowCount.c][173]
                Entry:
                        Statement = 0x55e8d442c780
                        Row Count = 0x7ffcaf5472c8
[ODBC][9810][1614079785.540156][SQLRowCount.c][247]
                Exit:[SQL_SUCCESS]
                        Row Count = 0x7ffcaf5472c8 -> 1
[ODBC][9810][1614079785.540272][SQLNumResultCols.c][156]
                Entry:
                        Statement = 0x55e8d442c780
                        Column Count = 0x7ffcaf5472d0
[ODBC][9810][1614079785.540398][SQLNumResultCols.c][251]
                Exit:[SQL_SUCCESS]
                        Count = 0x7ffcaf5472d0 -> 0
[ODBC][9810][1614079785.540571][SQLEndTran.c][417]
                Entry:
                        Connection = 0x55e8d4313ea0
                        Completion Type = 0
[ODBC][9810][1614079785.543363][SQLGetInfo.c][236]
                Entry:
                        Connection = 0x55e8d4313ea0
                        Info Type = SQL_CURSOR_COMMIT_BEHAVIOR (23)
                        Info Value = 0x55e8d4315318
                        Buffer Length = 2
                        StrLen = 0x7ffcaf547270
[ODBC][9810][1614079785.543505][SQLGetInfo.c][236]
                Entry:
                        Connection = 0x55e8d4313ea0
                        Info Type = SQL_CURSOR_ROLLBACK_BEHAVIOR (24)
                        Info Value = 0x55e8d431531a
                        Buffer Length = 2
                        StrLen = 0x7ffcaf547270
[ODBC][9810][1614079785.543625][SQLEndTran.c][566]
                Exit:[SQL_SUCCESS]
[ODBC][9810][1614079785.543743][SQLSetConnectAttr.c][399]
                Entry:
                        Connection = 0x55e8d4313ea0
                        Attribute = SQL_ATTR_AUTOCOMMIT
                        Value = 0x1
                        StrLen = -6
[ODBC][9810][1614079785.543895][SQLSetConnectAttr.c][862]
                Exit:[SQL_SUCCESS]
[ODBC][9810][1614079785.544108][SQLCloseCursor.c][139]
                Entry:
                        Statement = 0x55e8d442c780
[ODBC][9810][1614079785.544302][SQLCloseCursor.c][234]
                Exit:[SQL_ERROR]
[ODBC][9810][1614079785.544428][SQLFreeHandle.c][387]
                Entry:
                        Handle Type = 3
                        Input Handle = 0x55e8d442c780
[ODBC][9810][1614079785.544591][SQLFreeHandle.c][490]
                Exit:[SQL_SUCCESS]
[ODBC][9810][1614079785.544720][SQLEndTran.c][417]
                Entry:
                        Connection = 0x55e8d4313ea0
                        Completion Type = 1
[ODBC][9810][1614079785.546258][SQLEndTran.c][566]
                Exit:[SQL_SUCCESS]
[ODBC][9810][1614079785.546390][SQLDisconnect.c][208]
                Entry:
                        Connection = 0x55e8d4313ea0
[ODBC][9810][1614079786.515560][SQLDisconnect.c][379]
                Exit:[SQL_SUCCESS]
[ODBC][9810][1614079786.515803][SQLFreeHandle.c][290]
                Entry:
                        Handle Type = 2
                        Input Handle = 0x55e8d4313ea0
[ODBC][9810][1614079786.515925][SQLFreeHandle.c][339]
                Exit:[SQL_SUCCESS]
[ODBC][9810][1614079786.516044][SQLFreeHandle.c][220]
                Entry:
                        Handle Type = 1
                        Input Handle = 0x55e8d43138a0

pdo-3996.log (BLOB column 0 bytes long after insert)

[ODBC][9789][1614079850.559366][SQLConnect.c][3721]
                Entry:
                        Connection = 0x55e8d43da090
                        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][9789][1614079850.657020][SQLConnect.c][4299]
                Exit:[SQL_SUCCESS]
[ODBC][9789][1614079850.657240][SQLAllocHandle.c][540]
                Entry:
                        Handle Type = 3
                        Input Handle = 0x55e8d43da090
[ODBC][9789][1614079850.657334][SQLAllocHandle.c][1085]
                Exit:[SQL_SUCCESS]
                        Output Handle = 0x55e8d43cd4c0
[ODBC][9789][1614079850.657399][SQLPrepare.c][196]
                Entry:
                        Statement = 0x55e8d43cd4c0
                        SQL = [INSERT INTO HSZ(ID, PDF) VALUES(SEQ_HSZ.NEXTVAL, EMPTY_BLOB()) RETURNING PDF INTO ?][length = 83 (SQL_NTS)]
[ODBC][9789][1614079850.658933][SQLPrepare.c][377]
                Exit:[SQL_SUCCESS]
[ODBC][9789][1614079850.659389][SQLDescribeParam.c][185]
                Entry:
                        Statement = 0x55e8d43cd4c0
                        Parameter Number = 1
                        SQL Type = 0x7ffcaf547152
                        Param Def = 0x7ffcaf547158
                        Scale = 0x7ffcaf547154
                        Nullable = 0x7ffcaf547156
[ODBC][9789][1614079850.659458][SQLDescribeParam.c][338]
                Exit:[SQL_SUCCESS]
                        SQL Type = 0x7ffcaf546c40
                        Param Def = 0x7ffcaf546d30
                        Scale = 0x7ffcaf546e20
                        Nullable = 0x7ffcaf546f10
[ODBC][9789][1614079850.659517][SQLBindParameter.c][217]
                Entry:
                        Statement = 0x55e8d43cd4c0
                        Param Number = 1
                        Param Type = 1
                        C Type = 1 SQL_C_CHAR
                        SQL Type = 12 SQL_VARCHAR
                        Col Def = 999
                        Scale = 0
                        Rgb Value = 0x7f705200b230
                        Value Max = 0
                        StrLen Or Ind = 0x7f70520013e0
[ODBC][9789][1614079850.659575][SQLBindParameter.c][434]
                Exit:[SQL_SUCCESS]
[ODBC][9789][1614079850.659639][SQLSetConnectAttr.c][399]
                Entry:
                        Connection = 0x55e8d43da090
                        Attribute = SQL_ATTR_AUTOCOMMIT
                        Value = (nil)
                        StrLen = -6
[ODBC][9789][1614079850.659701][SQLSetConnectAttr.c][862]
                Exit:[SQL_SUCCESS]
[ODBC][9789][1614079850.659816][SQLExecute.c][187]
                Entry:
                        Statement = 0x55e8d43cd4c0
[ODBC][9789][1614079850.659877][SQLExecute.c][357]
                Exit:[SQL_NEED_DATA]
[ODBC][9789][1614079850.659931][SQLParamData.c][166]
                Entry:
                        Statement = 0x55e8d43cd4c0
                        Value = 0x7ffcaf5472d0
[ODBC][9789][1614079850.660068][SQLParamData.c][362]
                Exit:[SQL_NEED_DATA]
                        Value = 0x7f705200b230
[ODBC][9789][1614079850.660129][SQLPutData.c][148]
                Entry:
                        Statement = 0x55e8d43cd4c0
                        Data = 0x7f7052085018
                        StrLen = 3996
[ODBC][9789][1614079850.660251][SQLPutData.c][315]
                Exit:[SQL_SUCCESS]
[ODBC][9789][1614079850.660294][SQLParamData.c][166]
                Entry:
                        Statement = 0x55e8d43cd4c0
                        Value = 0x7ffcaf5472d0
[ODBC][9789][1614079850.662195][SQLParamData.c][362]
                Exit:[SQL_SUCCESS]
                        Value = 0x7f705200b230
[ODBC][9789][1614079850.662266][SQLRowCount.c][173]
                Entry:
                        Statement = 0x55e8d43cd4c0
                        Row Count = 0x7ffcaf5472c8
[ODBC][9789][1614079850.662324][SQLRowCount.c][247]
                Exit:[SQL_SUCCESS]
                        Row Count = 0x7ffcaf5472c8 -> 1
[ODBC][9789][1614079850.662368][SQLNumResultCols.c][156]
                Entry:
                        Statement = 0x55e8d43cd4c0
                        Column Count = 0x7ffcaf5472d0
[ODBC][9789][1614079850.662414][SQLNumResultCols.c][251]
                Exit:[SQL_SUCCESS]
                        Count = 0x7ffcaf5472d0 -> 0
[ODBC][9789][1614079850.662499][SQLEndTran.c][417]
                Entry:
                        Connection = 0x55e8d43da090
                        Completion Type = 0
[ODBC][9789][1614079850.665256][SQLGetInfo.c][236]
                Entry:
                        Connection = 0x55e8d43da090
                        Info Type = SQL_CURSOR_COMMIT_BEHAVIOR (23)
                        Info Value = 0x55e8d43db508
                        Buffer Length = 2
                        StrLen = 0x7ffcaf547270
[ODBC][9789][1614079850.665345][SQLGetInfo.c][236]
                Entry:
                        Connection = 0x55e8d43da090
                        Info Type = SQL_CURSOR_ROLLBACK_BEHAVIOR (24)
                        Info Value = 0x55e8d43db50a
                        Buffer Length = 2
                        StrLen = 0x7ffcaf547270
[ODBC][9789][1614079850.665406][SQLEndTran.c][566]
                Exit:[SQL_SUCCESS]
[ODBC][9789][1614079850.665486][SQLSetConnectAttr.c][399]
                Entry:
                        Connection = 0x55e8d43da090
                        Attribute = SQL_ATTR_AUTOCOMMIT
                        Value = 0x1
                        StrLen = -6
[ODBC][9789][1614079850.665542][SQLSetConnectAttr.c][862]
                Exit:[SQL_SUCCESS]
[ODBC][9789][1614079850.665666][SQLCloseCursor.c][139]
                Entry:
                        Statement = 0x55e8d43cd4c0
[ODBC][9789][1614079850.665779][SQLCloseCursor.c][234]
                Exit:[SQL_ERROR]
[ODBC][9789][1614079850.665837][SQLFreeHandle.c][387]
                Entry:
                        Handle Type = 3
                        Input Handle = 0x55e8d43cd4c0
[ODBC][9789][1614079850.665937][SQLFreeHandle.c][490]
                Exit:[SQL_SUCCESS]
[ODBC][9789][1614079850.665999][SQLEndTran.c][417]
                Entry:
                        Connection = 0x55e8d43da090
                        Completion Type = 1
[ODBC][9789][1614079850.667235][SQLEndTran.c][566]
                Exit:[SQL_SUCCESS]
[ODBC][9789][1614079850.667298][SQLDisconnect.c][208]
                Entry:
                        Connection = 0x55e8d43da090
[ODBC][9789][1614079851.642822][SQLDisconnect.c][379]
                Exit:[SQL_SUCCESS]
[ODBC][9789][1614079851.642968][SQLFreeHandle.c][290]
                Entry:
                        Handle Type = 2
                        Input Handle = 0x55e8d43da090
[ODBC][9789][1614079851.643038][SQLFreeHandle.c][339]
                Exit:[SQL_SUCCESS]
[ODBC][9789][1614079851.643100][SQLFreeHandle.c][220]
                Entry:
                        Handle Type = 1
                        Input Handle = 0x55e8d43d9a90
 [2021-02-23 11:51 UTC] horvath dot szabolcs at t-systems dot hu
pdo-3997.log (throws  ORA-01461: can bind a LONG value only for insert into a LONG column)
[ODBC][9790][1614079877.745882][SQLConnect.c][3721]
                Entry:
                        Connection = 0x55e8d42ec7d0
                        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][9790][1614079877.851569][SQLConnect.c][4299]
                Exit:[SQL_SUCCESS]
[ODBC][9790][1614079877.851734][SQLAllocHandle.c][540]
                Entry:
                        Handle Type = 3
                        Input Handle = 0x55e8d42ec7d0
[ODBC][9790][1614079877.851847][SQLAllocHandle.c][1085]
                Exit:[SQL_SUCCESS]
                        Output Handle = 0x55e8d43a4ba0
[ODBC][9790][1614079877.851907][SQLPrepare.c][196]
                Entry:
                        Statement = 0x55e8d43a4ba0
                        SQL = [INSERT INTO HSZ(ID, PDF) VALUES(SEQ_HSZ.NEXTVAL, EMPTY_BLOB()) RETURNING PDF INTO ?][length = 83 (SQL_NTS)]
[ODBC][9790][1614079877.853683][SQLPrepare.c][377]
                Exit:[SQL_SUCCESS]
[ODBC][9790][1614079877.854132][SQLDescribeParam.c][185]
                Entry:
                        Statement = 0x55e8d43a4ba0
                        Parameter Number = 1
                        SQL Type = 0x7ffcaf547152
                        Param Def = 0x7ffcaf547158
                        Scale = 0x7ffcaf547154
                        Nullable = 0x7ffcaf547156
[ODBC][9790][1614079877.854193][SQLDescribeParam.c][338]
                Exit:[SQL_SUCCESS]
                        SQL Type = 0x7ffcaf546c40
                        Param Def = 0x7ffcaf546d30
                        Scale = 0x7ffcaf546e20
                        Nullable = 0x7ffcaf546f10
[ODBC][9790][1614079877.854244][SQLBindParameter.c][217]
                Entry:
                        Statement = 0x55e8d43a4ba0
                        Param Number = 1
                        Param Type = 1
                        C Type = 1 SQL_C_CHAR
                        SQL Type = 12 SQL_VARCHAR
                        Col Def = 999
                        Scale = 0
                        Rgb Value = 0x7f705200b230
                        Value Max = 0
                        StrLen Or Ind = 0x7f70520013e0
[ODBC][9790][1614079877.854293][SQLBindParameter.c][434]
                Exit:[SQL_SUCCESS]
[ODBC][9790][1614079877.854349][SQLSetConnectAttr.c][399]
                Entry:
                        Connection = 0x55e8d42ec7d0
                        Attribute = SQL_ATTR_AUTOCOMMIT
                        Value = (nil)
                        StrLen = -6
[ODBC][9790][1614079877.854398][SQLSetConnectAttr.c][862]
                Exit:[SQL_SUCCESS]
[ODBC][9790][1614079877.854495][SQLExecute.c][187]
                Entry:
                        Statement = 0x55e8d43a4ba0
[ODBC][9790][1614079877.854548][SQLExecute.c][357]
                Exit:[SQL_NEED_DATA]
[ODBC][9790][1614079877.854594][SQLParamData.c][166]
                Entry:
                        Statement = 0x55e8d43a4ba0
                        Value = 0x7ffcaf5472d0
[ODBC][9790][1614079877.854728][SQLParamData.c][362]
                Exit:[SQL_NEED_DATA]
                        Value = 0x7f705200b230
[ODBC][9790][1614079877.854787][SQLPutData.c][148]
                Entry:
                        Statement = 0x55e8d43a4ba0
                        Data = 0x7f7052085018
                        StrLen = 3997
[ODBC][9790][1614079877.854914][SQLPutData.c][315]
                Exit:[SQL_SUCCESS]
[ODBC][9790][1614079877.854964][SQLParamData.c][166]
                Entry:
                        Statement = 0x55e8d43a4ba0
                        Value = 0x7ffcaf5472d0
[ODBC][9790][1614079877.858501][SQLParamData.c][362]
                Exit:[SQL_ERROR]
                        Value = 0x7f705200b230
                DIAG [HY000] [Oracle][ODBC][Ora]ORA-01461: can bind a LONG value only for insert into a LONG column


[ODBC][9790][1614079877.858707][SQLGetDiagRec.c][677]
                Entry:
                        Statement = 0x55e8d43a4ba0
                        Rec Number = 1
                        SQLState = 0x7f705200c298
                        Native = 0x7f705200c4a0
                        Message Text = 0x7f705200c29e
                        Buffer Length = 511
                        Text Len Ptr = 0x7ffcaf546e4a
[ODBC][9790][1614079877.858789][SQLGetDiagRec.c][726]
                Exit:[SQL_SUCCESS]
                        SQLState = HY000
                        Native = 0x7f705200c4a0 -> 1461
                        Message Text = [[Oracle][ODBC][Ora]ORA-01461: can bind a LONG value only for insert into a LONG column
]
[ODBC][9790][1614079877.858839][SQLGetDiagRec.c][677]
                Entry:
                        Statement = 0x55e8d43a4ba0
                        Rec Number = 2
                        SQLState = 0x7ffcaf546e50
                        Native = 0x7ffcaf546e4c
                        Message Text = 0x7ffcaf546e60
                        Buffer Length = 1023
                        Text Len Ptr = 0x7ffcaf546e4a
[ODBC][9790][1614079877.858885][SQLGetDiagRec.c][726]
                Exit:[SQL_NO_DATA]
[ODBC][9790][1614079877.858957][SQLEndTran.c][417]
                Entry:
                        Connection = 0x55e8d42ec7d0
                        Completion Type = 0
[ODBC][9790][1614079877.860414][SQLGetInfo.c][236]
                Entry:
                        Connection = 0x55e8d42ec7d0
                        Info Type = SQL_CURSOR_COMMIT_BEHAVIOR (23)
                        Info Value = 0x55e8d42edc48
                        Buffer Length = 2
                        StrLen = 0x7ffcaf547270
[ODBC][9790][1614079877.860489][SQLGetInfo.c][236]
                Entry:
                        Connection = 0x55e8d42ec7d0
                        Info Type = SQL_CURSOR_ROLLBACK_BEHAVIOR (24)
                        Info Value = 0x55e8d42edc4a
                        Buffer Length = 2
                        StrLen = 0x7ffcaf547270
[ODBC][9790][1614079877.860541][SQLEndTran.c][566]
                Exit:[SQL_SUCCESS]
[ODBC][9790][1614079877.860589][SQLSetConnectAttr.c][399]
                Entry:
                        Connection = 0x55e8d42ec7d0
                        Attribute = SQL_ATTR_AUTOCOMMIT
                        Value = 0x1
                        StrLen = -6
[ODBC][9790][1614079877.860636][SQLSetConnectAttr.c][862]
                Exit:[SQL_SUCCESS]
[ODBC][9790][1614079877.860731][SQLFreeHandle.c][387]
                Entry:
                        Handle Type = 3
                        Input Handle = 0x55e8d43a4ba0
[ODBC][9790][1614079877.860813][SQLFreeHandle.c][490]
                Exit:[SQL_SUCCESS]
[ODBC][9790][1614079877.860869][SQLEndTran.c][417]
                Entry:
                        Connection = 0x55e8d42ec7d0
                        Completion Type = 1
[ODBC][9790][1614079877.862398][SQLEndTran.c][566]
                Exit:[SQL_SUCCESS]
[ODBC][9790][1614079877.862453][SQLDisconnect.c][208]
                Entry:
                        Connection = 0x55e8d42ec7d0
[ODBC][9790][1614079878.832728][SQLDisconnect.c][379]
                Exit:[SQL_SUCCESS]
[ODBC][9790][1614079878.832929][SQLFreeHandle.c][290]
                Entry:
                        Handle Type = 2
                        Input Handle = 0x55e8d42ec7d0
[ODBC][9790][1614079878.832987][SQLFreeHandle.c][339]
                Exit:[SQL_SUCCESS]
[ODBC][9790][1614079878.833025][SQLFreeHandle.c][220]
                Entry:
                        Handle Type = 1
                        Input Handle = 0x55e8d42ec1d0
 [2021-02-23 15:23 UTC] cmb@php.net
-Status: Open +Status: Feedback -Assigned To: +Assigned To: cmb
 [2021-02-23 15:23 UTC] cmb@php.net
> 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.

Please note that we do no longer actively support PHP 7.3[1].

> INSERT INTO HSZ (ID, PDF) VALUES (SEQ_HSZ.NEXTVAL, ?)
> INSERT INTO HSZ(ID, PDF) VALUES(SEQ_HSZ.NEXTVAL, EMPTY_BLOB()) RETURNING PDF INTO ?

You are using rather different queries for ODBC and PDO_ODBC.  What
would happen, if you swapped the queries?

[1] <https://www.php.net/supported-versions.php>
 [2021-02-23 17:25 UTC] horvath dot szabolcs at t-systems dot hu
Thanks for the quick reply.

The doc on https://www.php.net/manual/en/pdo.lobs.php (Example #3 Inserting an image into a database: Oracle) was a bit misleading. I don't really know why empty_blob() came into play there, but that referenced oci: and we're using odbc: here.

My mistake. Please close the bug, it's not really bug.

Anyone going here for a solution, the following works in case of pdo_odbc blob upload:

$stmt = $db->prepare("INSERT INTO mytable (ID, PDF) VALUES (SEQ_MYSEQ.NEXTVAL, ?)");
$stmt->bindParam(1, $imageBlob, PDO::PARAM_LOB);
$imageBlob = file_get_contents("testfile.pdf");
$stmt->execute([$imageBlob]);

Tested with 1 Mbytes upload.

SQL> select id, length(pdf), dbms_lob.getlength(pdf) FROM mytable order by ID;
+-----------+----------------+-----------------------------------------+
| ID        | LENGTH(PDF)    | DBMS_LOB.GETLENGTH(PDF)                 |
+-----------+----------------+-----------------------------------------+
| 376706    | 1000000        | 1000000                                 |
 [2021-02-23 17:37 UTC] cmb@php.net
-Status: Feedback +Status: Not a bug
 [2021-02-23 17:37 UTC] cmb@php.net
Ah, thanks!  That example looks super fishy to me (regardless of
the driver), but since I don't know much about Oracle DB, I might
be wrong.
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Sun Nov 10 22:01:28 2024 UTC