Laravel Query Builder / How to stop it from adding square [brackets]?

Question

Im trying to get laravel 7x to work with impala/kudu provided by cloudera. Ive got the ODBC connection which connects fine and if you run raw sql query it also works fine.

Database connection entry:

'impala_connect' => [
    'driver' => 'sqlsrv',
    'odbc' => true,
    'odbc_datasource_name'=> 'cloudera_odbc_connection',
    'host' => '',
    'port' => '',
    'database' => '',
    'username' => '',
    'password' => '',
    'pooling'  => false,
],

Raw queries work fine:

DB::connection('impala_connect')->raw("insert into test1 values(4, 'z')");
DB::connection('impala_connect')->select(DB::raw('select * from test1'));

Query builder fails:

However, when Im using the laravel's query builder to run some queries they all fail with brackets that laravel adds on its own.

DB::connection('impala_connect')->table('test1')->get(['id', 'val']);

//or
DB::connection('impala_connect')->table('test1')->insert([
  'id'=> 5,
  'val'=> 'z'
]);

This is the error I get:

SQLSTATE: General error: 0 [Cloudera][ImpalaODBC] (110) Error while executing a query in Impala : ParseException: Syntax error in line 1: select [id], [val] from [test1] ^ 
Encountered: COMMA Expected: CASE, CAST, DEFAULT, EXISTS, FALSE, IF, INTERVAL, LEFT, NOT, NULL, REPLACE, RIGHT, TRUNCATE, TRUE, IDENTIFIER 
CAUSED BY: Exception: Syntax error (SQLPrepare[0] at pdo_odbc\odbc_driver.c:206) (SQL: select [id], [val] from [test1])

You can see the actual query became a bracket for each item and thats where it failed.

So how do you make laravel NOT wrap brackets?!

---

EDIT:

I did figure out how to get table name at least without brackets.

->table(db::raw('test1'))->

However, I cant get the column names in insert and update.

Answer 1

Try using select and then get:

DB::connection('impala_connect')->table('test1')->select('id', 'val')->get();

Answer 2

You're using the SQL Server driver. 'driver' => 'sqlsrv' I do not know anything about impala but is it compatible with SQL Server's syntax in the first place?

Columns wrapped in square brackets ([id]) are usually keywords or contain special characters or spaces. Laravel wraps everything in brackets by default while using the sqlsrv driver. This is common behavior.

While using the mysql driver, it does the same thing by wrapping everything in backticks ``.

This is the result of using the query builder to create the simple SQL Query SELECT id, name FROM table using the different drivers. As you can see, it treats each selected column and the table name as if they had special characters in them with each of the drivers. This is done for consistency.

>>> DB::connection('mysql')->table('table')->select('id', 'name')->toSql()
=> "select `id`, `name` from `table`"
>>> DB::connection('pgsql')->table('table')->select('id', 'name')->toSql()
=> "select "id", "name" from "table""
>>> DB::connection('sqlite')->table('table')->select('id', 'name')->toSql()
=> "select "id", "name" from "table""
>>> DB::connection('sqlsrv')->table('table')->select('id', 'name')->toSql()
=> "select [id], [name] from [table]"

I conclude that the sqlsrv driver is not compatible with Impala's syntax. The wrapping is done by the function wrapValue located in vendor\laravel\framework\src\Illuminate\Database\Query\Grammars\SqlServerGrammar.php.

Not recommended at all, but you could edit it if you have no other choice.

Another option if you don't mind raw queries is to simply use the DB facade's statement methods: insert, update, delete.

// returns bool
DB::insert('insert into users (id, name) values (?, ?)', [1, 'Dayle']);
// returns int (number of rows affected)
DB::update('update users set name = ? where id = ?', ['Dayle', 1]);
DB::delete('delete from users where name = ?', ['Dayle']);