Eloquent on MSSQL return error SQLSTATE[IMSSP]: Field 2 returned no data.

[ngochangngo52]

Laravel Version

10.23.1

PHP Version

8.1.23

Database Driver & Version

php-sqlsrv-5.11.0-1.el9

Description

OS: Alma 9

After minor update to PHP php-8.1.23-1.el9.remi.x86_64 and SQL_SRV php-sqlsrv-5.11.1-1.el9.remi.8.1.x86_64, my app broke, throw error for simple Eloquent call:

   $client= BusinessPartner::first();

Model

namespace App;

use Illuminate\Database\Eloquent\Model;

class BusinessPartner extends Model {
    protected $connection = 'sqlsrv';
    protected $table = 'dbo.OCRD';
    protected $primaryKey = 'DocEntry';
    public $timestamps = false;
}

Error:

SQLSTATE[IMSSP]: Field 2 returned no data.

select top 1 * from [dbo].[OCRD]

The above raw query (generated by Laravel) works perfectly if I run in a native SQL client.

Before upgrading, my app works perfectly fine.

Steps To Reproduce

Model

namespace App;

use Illuminate\Database\Eloquent\Model;

class BusinessPartner extends Model {
    protected $connection = 'sqlsrv';
    protected $table = 'dbo.OCRD';
    protected $primaryKey = 'DocEntry';
    public $timestamps = false;
}

(or whatever model, it will break anyway)

Tinker (or controller)

$client = BusinessPartner::first();

This line will throw error.

[driesvints]

Gonna go on a hunch and say it's related to #47937. I'm not sure why things broke for you but if it broke while updating mssql dependencies then it doesn't looks like a Laravel issue to me.

[github-actions[bot]]

Thank you for reporting this issue!

As Laravel is an open source project, we rely on the community to help us diagnose and fix issues as it is not possible to research and fix every issue reported to us via GitHub.

If possible, please make a pull request fixing the issue you have described, along with corresponding tests. All pull requests are promptly reviewed by the Laravel team.

Thank you!

[ngochangngo52]

Gonna go on a hunch and say it's related to #47937.

#47937 was fixed by php-sqlsrv-5.11.1-1.el9.remi.8.1.x86_64 therefore I updated to PHP 8.1.23 and php-sqlsrv-5.11.1 to see whether my app having any comparability issue. It must be difference.

I'm not sure why things broke for you but if it broke while updating mssql dependencies then it doesn't looks like a Laravel issue to me.

To be fair, error like this can be caused by:

• PHP 8.1.23 itself; OR
• PHP-Sqlsrv extension; OR
• Laravel ORM that rely on some presumed/default return from PHP and when PHP updated it was broken.

[ngochangngo52]

To clarify, it seem only throw error on ->first() method call, others Eloquent methods work just fine for me.

[crynobone]

Can you test with Laravel Framework version before the whole PDO issue solved the problem? This can eliminate if any recent changes would cause any issue.

[ngochangngo52]

Can you test with Laravel Framework version before the whole PDO issue solved the problem? This can eliminate if any recent changes would cause any issue.

I just revert to Laravel version 10.0.0, but it's still not working :(

[omaromp2]

Just out of curiosity: try removing the dbo from your $table var on your model

[ngochangngo52]

Update:
If I select some fields, it works

$client = BusinessPartner::where('CardCode', $CardCode)->select('CardName')->first() works (added ->select('CardName')). Why is that?

[ngochangngo52]

Just out of curiosity: try removing the dbo from your $table var on your model

thanks, I tried but unfortunately it still not work.
As I just updated above, it will work if I SELECT some fields, but if don't call ->select(...) (meaning select *), OR if I ->select('*') specifically, it will throw error: Field (number) returned no data.

[omaromp2]

ok, try adding 'charset' => 'utf8' to your database connection config. It might not work, but the error should change.

[ngochangngo52]

ok, try adding 'charset' => 'utf8' to your database connection config. It might not work, but the error should change.

thanks, it's in my config/database.php already

[omaromp2]

ok, try adding 'charset' => 'utf8' to your database connection config. It might not work, but the error should change.

thanks, it's in my config/database.php already

One more: Are you able to perform the query in your MsSQL client app?

[ngochangngo52]

ok, try adding 'charset' => 'utf8' to your database connection config. It might not work, but the error should change.

thanks, it's in my config/database.php already

One more: Are you able to perform the query in your MsSQL client app?

Yes, works on native sqlcmd on same machine

Edit: it show results in sqlcmd, but also error at the end:

Sqlcmd: Error: Internal error at ReadAndHandleColumnData (Reason: Error reading column data).
Unicode conversion failed.

To clarify: my webapp run in WSL (Alma), and this sqlcmd run on same WSL. If I copy the same query to run on Heidi on Windows, it works perfectly fine.

[ngochangngo52]

Just out of curiosity: try removing the dbo from your $table var on your model

thanks, I tried but unfortunately it still not work. As I just updated above, it will work if I SELECT some fields, but if don't call ->select(...) (meaning select *), OR if I ->select('*') specifically, it will throw error: Field (number) returned no data.

Update: if I select certain fields, it will have this error:
SQLSTATE[HY000]: [Microsoft][ODBC Driver 18 for SQL Server]Unicode conversion failed

ok, try adding 'charset' => 'utf8' to your database connection config. It might not work, but the error should change.

It seem somehow related to encoding.

[ngochangngo52]

I open an issue in sqlsrv repo microsoft/msphpsql#1479, will close this if can confirm it's other side's issue

[SakiTakamachi]

I tried to reproduce it, but no matter what I did, I couldn't get the error to occur.

---

At least php/php-src#11622 is irrelevant.

It's possible that other parts included in Microsoft's hotfix are having an effect, but I don't know because I haven't looked closely.

It may be easier to advise if you state what the original version of both php and sqlsev was.

It says it's a minor update, so are thay 8.0 and 5.10?

[ngochangngo52]

I tried to reproduce it, but no matter what I did, I couldn't get the error to occur.

At least php/php-src#11622 is irrelevant.

It's possible that other parts included in Microsoft's hotfix are having an effect, but I don't know because I haven't looked closely.

It may be easier to advise if you state what the original version of both php and sqlsev was.

It says it's a minor update, so are thay 8.0 and 5.10?

My PHP version
8.1.23

PHP SQLSRV or PDO_SQLSRV version
php-sqlsrv-5.11.1-1.el9.remi.8.1.x86_64

Microsoft ODBC Driver version
[ODBC Driver 18 for SQL Server]

SQL Server version

Microsoft SQL Server 2016

Database collation:
SQL_Latin1_General_CP850_CI_AS

I think it relates to converting Unicode.

[SakiTakamachi]

What are previous versions?

[ngochangngo52]

What are previous versions?

It was php-8.1.21 and php-sqlsrv-5.11.0

[SakiTakamachi]

Thank you.

Can you share the definition part of the Laravel migration file?

Also, I would like to know what settings are made for the sql server part of config/database.php.

[ngochangngo52]

CREATE TABLE IF NOT EXISTS "OCRD" (
"CardCode" NVARCHAR(15) NOT NULL COLLATE 'SQL_Latin1_General_CP850_CI_AS',
"CardName" NVARCHAR(100) NULL DEFAULT NULL COLLATE 'SQL_Latin1_General_CP850_CI_AS',
"CardType" CHAR(1) NULL DEFAULT NULL COLLATE 'SQL_Latin1_General_CP850_CI_AS',
"GroupCode" SMALLINT NULL DEFAULT NULL,
PRIMARY KEY ("CardCode")
);

 'sqlsrv' => [
            'driver' => 'sqlsrv',
            'host' => env('DB_HOST', ''),
            'port' => env('DB_PORT', ''),
            'database' => env('DB_DATABASE', ''),
            'username' => env('DB_USERNAME', ''),
            'password' => env('DB_PASSWORD', ''),
            'charset' => 'utf8',
            'prefix' => '',
            'trust_server_certificate' => 'true',
            'options' => [
            ],

It also throw error if I select CardType, with other error SQLSTATE[HY000]: [Microsoft][ODBC Driver 18 for SQL Server]Unicode conversion failed (expected result for CardType is 'C')

[SakiTakamachi]

Could CREATE TABLE IF NOT EXISTS be used with SQL server?

I want to know the exact contents of the migration file.

[ngochangngo52]

Could CREATE TABLE IF NOT EXISTS be used with SQL server?

I want to know the exact contents of the migration file.

It's OCRD table for SAP B1 database, you can find it here https://sap.erpref.com/?schema=BusinessOne9.3&module_id=3&table=OCRD
My app connected to existing SAP B1 database, I don't create new one, therefore no migration for this.
Above is just my extraction of structure using Heidi SQL so that can let you know the structure, I don't know the statement is weird to SQL Server :)

[SakiTakamachi]

Hmm. Apparently I don't have enough time to reproduce this.
I wish you good luck!

[ngochangngo52]

Hmm. Apparently I don't have enough time to reproduce this. I wish you good luck!

Thank you very much for your kind effort.

[SakiTakamachi]

Is it possible that this is involved?

microsoft/msphpsql#1478

[ngochangngo52]

Is it possible that this is involved?

microsoft/msphpsql#1478

Thank you, it's not. I have 'trust_server_certificate' => 'true', in config/database.php and the other functions connect to DB normally. It's just the query, not the connection.

[ngochangngo52]

Update:
It's related to this https://stackoverflow.com/a/77140830/223934
TL;DR the fix is install glibc-gconv-extra.

It's not Laravel's fault.

Thank you all for helping.

[SakiTakamachi]

How about reverting to php 8.1.21 and seeing if the error occurred?

[ngochangngo52]

Seem like the EPEL packages only keep 2 last versions, so I can only go back to PHP 8.1.22 and php-sqlsrv-5.11.0 (latest 5.11.1). With these versions and glibc-gconv-extra removed, it fail. (This is on my local machine).

However on my production server I keep it unchanged (PHP 8.1.21 and php-sqlsrv-5.11.0; and without glibc-gconv-extra) and it's still functioning normally.

[SakiTakamachi]

How about 8.1.22?

[ngochangngo52]

Still throw error SQLSTATE[HY000]: [Microsoft][ODBC Driver 18 for SQL Server]Unicode conversion failed if select CardType.
(If select *, then SQLSTATE[IMSSP]: Field 2 returned no data.)

[SakiTakamachi]

You're using laravel, right?
Could you please try a combination of php8.1.23 and sqlsrv 5.11.0?

Naturally, there will be an error due to a problem fixed in 5.11.1, but if you include this library of mine, you can avoid the error.

https://github.com/SakiTakamachi/laravel-sqlsrv-err-avoid

[ngochangngo52]

You're using laravel, right? Could you please try a combination of php8.1.23 and sqlsrv 5.11.0?

Naturally, there will be an error due to a problem fixed in 5.11.1, but if you include this library of mine, you can avoid the error.

https://github.com/SakiTakamachi/laravel-sqlsrv-err-avoid
Yes. I had error SQLSTATE[IMSSP]: An invalid attribute was designated on the PDO object and it's fixed in sqlsrv 5.11.1.