Bug: [QueryBuilder] Error when fetching data from SQLSRV

[psuplat]

PHP Version

7.4

CodeIgniter4 Version

4.1.5

CodeIgniter4 Installation Method

Composer (using codeigniter4/appstarter)

Which operating systems have you tested for this bug?

Windows

Which server did you use?

cli

Database

SQL Server

What happened?

When I try to fetch data from SQL Server DB using a QueryBuilder I get errors. Running the exact same command as a simple query works perfectly fine.

Steps to Reproduce

This works just fine:

$db = \Config\Database::connect('intranet');
$query = $db->query("SELECT * FROM gross_contribution ")->getResult();

This fails:

$db = \Config\Database::connect('intranet');
$builder = $db->table('gross_contribution');
$query = $builder->get()->getResult();

with the following error: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as \"\" or [] are not allowed. Change the alias to a valid name.

Expected Output

data array should be returned

Anything else?

No response

[psuplat]

Had a closer look, and inside the vendor\codeigniter4\framework\system\Database\SQLSRV\Builder.php if I dump the $sql variable on line 655 I get this:

SELECT * FROM "intranet".""."gross_contribution"

Running the above command directly inside SQL Server result it the error mention in report above. Howeve if I tweak the above sql to one of the following I get the expected results: SELECT * FROM "intranet".."gross_contribution" SELECT * FROM intranet..gross_contribution

so the issue is caused by the double-quotation characters between the database & table name - ."".

[qury]

did you set schema in your database config? could it be set to an empty string by any chance?

[psuplat]

I have added the schema value (set to dbo) inside the .env file and then things work. However this is still a bug.

Inside the system/Database/SQLSRV/Connection.php on line 64 the schema is set to a default value of dbo:

public $schema = 'dbo';

However, inside the system/Database/SQLSRV/Builder.php on lines 313 & 316, where the full name of the table is supposed to be returned, the $this->db->schema returns empty string, instead of the default value:

return '"' . $this->db->getDatabase() . '"."' . $this->db->schema . '"."' . str_replace('"', '', $table) . '"' . $alias;

Somehow the default schema value is not passed from connection into the builder.

[kenjis]

Pull Request to fix a bug is welcome. https://github.com/codeigniter4/CodeIgniter4/blob/develop/contributing/pull_request.md

[nicojmb]

Hi, i've similiar problema with ActiveRecord dn SQLSrv.

For example, in MySQL this query works:

$model->where('date >=', '2021-01-01');
$model->where('date <=', '2021-12-31');
$model->where('action', 'CC_END');
$model->orderBy('date', 'desc');
$model->groupBy('callid');
$model->findAll();

And SQLServer with exaclty same table escruture do not work, getting this error:

ERROR - 2021-12-15 12:45:48 --> [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Column "database.dbo.table.date" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.

Also, the method: $model->countAllResults() it doesn't work either in SQLSrv.

[qury]

@nicojmb Requiring the date in group by or in aggregate function is requirement in SQL Server, nothing to do with the query builder. Have you tried your sql in on the database directly?

[kenjis]

Cannot reproduce.

The following test passes: https://github.com/codeigniter4/CodeIgniter4/blob/219011070afea4c4e2401075b92a975ac3db490d/tests/system/Database/Live/GetTest.php#L31-L40

SELECT *
FROM "test"."dbo"."db_job"

[kenjis]

@psuplat If you still have this issue, feel free to reopen or create a new issue.