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.

score 0 · Answer 1 · answered Oct 15 '20 at 07:12

0

Try using select and then get:

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

answered Oct 15 '20 at 07:12

Alberto Sinigaglia

12,097
2
20
48

@RaheelHasan can you please post the current code somewhere? – Alberto Sinigaglia Oct 15 '20 at 07:18

IGP · Answer 2 · 2020-10-15T15:09:37.480

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']);

sqlsrv in laravel provides connection to odbc via `odbc_datasource_name` connection setting. This only available in sqlsrv driver and not any other driver. — Raheel Hasan, Oct 15 '20 at 08:13
I tried to look into `wrapValue`, however I dont want to update the vendor code manually as then it will be a massive problem when I upgrade the framework — Raheel Hasan, Oct 15 '20 at 08:14
using `->table(db::raw('test1'))->` I got at least the table name without brackets. But cant figure out the columns in update and insert calls. — Raheel Hasan, Oct 15 '20 at 08:17
I think the odbc provided by the sqlsrv driver is very specific to SQL Server itself. — IGP, Oct 15 '20 at 14:52
If you don't have a problem running raw sql queries, you could try `DB::insert` and `DB::update`. Their syntax is as follows `DB::insert(sql-query, placeholders)`. You use ? as placeholders to sanitize inputs. For example: `DB::insert('insert into users (id, name) values (?, ?)', [1, 'Dayle']);` `DB::update` has the same syntax. The difference between them: `DB::insert` returns boolean and `DB::update` returns int (the number of rows updated). There's also `DB::delete` that behaves exactly like `DB::update`. — IGP, Oct 15 '20 at 15:02
yea you are right, thats what I might end up doing is using raw sql for all operations with this connection — Raheel Hasan, Oct 16 '20 at 00:05

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

2 Answers2