-
Notifications
You must be signed in to change notification settings - Fork 7.7k
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
odbc_fetch_*() fails to fetch SQL Server text fields with SQL_CUR_USE_ODBC #15219
Comments
The SQL Server table can be created with this query:
|
I get the expected result with the ODBC Driver 17 for SQL Server. Note that the Installing SQL Server Native Client documentation states that this driver isn't recommended for new application development, and that user should use the ODBC Driver for SQL Server instead. Can you at least test whether this driver would solve the issue for you? Also, consider to switch to PECL/sqlsrv instead of ext/odbc, since the former is likely way better supported for SQL Server. |
@cmb69 Thank you very much for the fast reply! I have now tested "SQL Server" (Generation 1), "SQL Server Native Client 11.0" (Generation 2), and "ODBC Driver 17 for SQL Server" (Generation 3), all have the same behavior. I could reproduce it on two computers (I will test a 3rd computer tomorrow):
<?php
echo PHP_VERSION."\n";
echo (PHP_INT_SIZE == 4 ? '32' : '64')." Bit\n";
$conn = odbc_connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=ELY2\TEST;DATABASE=TEST', 'sa', '.......', SQL_CUR_USE_ODBC);
$res = odbc_exec($conn, "select id, description from my_objects");
if (odbc_fetch_object($res) === false) {
echo "NOT GOOD\n";
} else {
echo "GOOD\n";
}
$res = odbc_exec($conn, "select description from my_objects");
if (odbc_fetch_object($res) === false) {
echo "NOT GOOD\n";
} else {
echo "GOOD\n";
}
odbc_close($conn); returns:
Is there anything I can do to help finding the issue? (PS: Regarding alternatives to ext/odbc, I am building a OpenSource app which should connect to as many different database technologies as possible, so I implemented ODBC, PDO, ADO, MySQLi, PgSQL, SQLite3, ... so that end-users hopefully can keep their system and don't need to reconfigure/install something) |
I tested it on a 3rd computer. Also the same result. This time, I had a 32-bit PHP instead of 64-bit.
Computer 3
|
Actually, I can reproduce the reported behavior now (I might have made a mistake while adjusting the script to my environment). A quick look at the ODBC trace reveals:
I'll have a closer look. |
D'oh! Apparently yet another (?) manifestation of using |
That wasn't that bad, after realizing that this already caused when calling
And if we had proper error handling patch ext/odbc/php_odbc.c | 1 +
1 file changed, 1 insertion(+)
diff --git a/ext/odbc/php_odbc.c b/ext/odbc/php_odbc.c
index 579b5e989b..0eacbc848c 100644
--- a/ext/odbc/php_odbc.c
+++ b/ext/odbc/php_odbc.c
@@ -1326,6 +1326,7 @@ static void php_odbc_fetch_hash(INTERNAL_FUNCTION_PARAMETERS, int result_type)
rc = SQLFetch(result->stmt);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) {
+ odbc_sql_error(result->conn_ptr, result->stmt, "SQLFetch");
RETURN_FALSE;
} We would have seen something like this:
That would have at least saved some time. Now the question is what to do about this. Maybe @NattyNarwhal has an idea. |
For what it's worth, pdo_odbc has the same issue (but at least would show the error): $pdo = new PDO("odbc:$dsn", $user, $pass, [PDO::ODBC_ATTR_USE_CURSOR_LIBRARY => PDO::ODBC_SQL_USE_ODBC]);
$stmt = $pdo->query("select description from my_objects");
var_dump($stmt->fetchAll()); outputs:
|
A small thing I have tested:
Issue also happens for Ansi This also fails: My theory is that the amount of columns is counted, and if it is 0, then error SL009 is thrown. Maybe the [text] and [ntext] types are excluded from the counting because they cannot be used for binding? (I remember that there are a lot of things you cannot do with text/memo columns). It's still weird because I just want to execute a SQL statement and not prepare/bind a statement. |
Not quite. The problem is that some column types are bound (i.e. fetched into a variable), and some other types are not bound (their value is retrieved by an extra call); the former cause no issues, but the latter do, if only columns of the latter type are requested. If there was a description2 column of the same type as description, a So the problem is clear; the solution isn't, because not all column types can be bound (well, they could, but the length of the required buffer is hard to know; that's what I meant with "yet another (?) manifestation of using SQLBindCol() vs. SQLGetData()"). A hypothetical solution would be to alter the given query (e.g. instead of |
Make a PR for that error reporting, it's definitely useful. I'm not very familiar with SQL Server. Is the problem the same with |
@NattyNarwhal I have just checked that |
See PR #15256.
Not SQLServer specific, but probably a good reference: https://learn.microsoft.com/en-us/sql/odbc/reference/appendixes/sql-data-types?view=sql-server-ver16 |
The
ext/odbc relies on this behavior. The
That is something we should consider doing anyway. However, I have not tried that, because the ODBC Cursor Library documentation states:
So it seems to me that we should possibly deprecate
Now reading the Anyhow, @danielmarschall, did you use |
The problem I am facing with the ODBC plugin of my software is that it is ultra slow, compared to the other plugins like PDO or ADO. If I iterate over a result set, the server is contacted for each fetch_*() command, so a single query+fetchAll requires approximately 5 seconds, while in PDO it is <0,1 seconds (since it supports FetchAll). SQL_CUR_USE_ODBC is recommended a lot in internet forums, so I guess I give it a try. It reduces the 5 seconds to approximately 2 seconds (which is still bad). It is also recommended to solve rare problems with stored procedures. So, everybody says A naïve question...: if there is such a problem with ODBC and the "no columns are bound" problem - how are vendors other than PHP handling this? Do they also fail? |
Frankly, I don't know. I guess that issue is related to the SQLFetch()/SQLGetData() issue. "Normal" usage is probably to bind all columns to buffers, and call SQLFetch() to fill these buffers with the required data. Now for some column types it is inherently hard to know in advance how large these buffers need to be (different drivers appear to report different values for SQL_COLUMN_DISPLAY_SIZE/SQL_DESC_OCTET_LENGTH regarding charset encoding), so for these column types the implementation falls back on SQLGetData(), which appears to work fine in most cases. This might cause a performance penalty (maybe it's even the sole reason for the performance issues you've mentioned), and obviously breaks down in this case if no columns are bound. Now, it would be possible to bind all columns, and check whether truncation occured, on only use SQLGetData() additionally in this case, but if I remember correctly, we also had issues with this approach. There are simply so many ODBC drivers which handle the details differently, and since we apparently even support so many different ODBC implementations (I count 12! known implementations in pdo_odbc_includes.h), which may even support only very old ODBC versions, it is hard to find solutions which won't break other existing code. And to my knowledge, on CI we only test ext/odbc and ext/pdo_odbc against SQL Server with a single driver (ODBC Driver 17 for SQL Server). And since the introduction of PDO, the focus of most developers likely shifted away from ODBC – different databases are different beasts anyway, and trying to put a compatibility layer in between might make things even worse (or at least harder to deal with at the implementation level). |
Description
The following code:
Resulted in this output:
But I expected this output instead:
The bug only happens if
odbc_connect()
hasSQL_CUR_USE_ODBC
and it ONLY fails if you fetch exactly one column with the text data type. If you fetch a text row and another row, then it works!select description from my_objects
does NOT workselect id, description from my_objects
WORKSselect id from my_objects
WORKSSystem:
PHP Version
PHP 8.3.6 (also tested with PHP 8.4 Alpha 4)
Operating System
Windows 10 x64
The text was updated successfully, but these errors were encountered: