query building error, need quotes around table field's name

[Aminot1]

Hello, I am using this package with Laravel 5 and Oracle 11.

The thing is, when I use the ORM, OracleElequent, and try to insert, I get Invalid Identifier around the field name itself, example:

$user = new User(); $user->name = 'Amin'; $user->save();

The query produced, and sent to Oracle is: "Insert into user(name) values('Amin'); And Oracle returns an error, invalid identifier NAME, meaning it wants 'name' instead of name.

Writing Raw queries works fine as long as I add the quotations myself, but Query Builder isn't easy to modify.

Can you please help ?

[Aminot1]

Actually I just tried it with Laravel 4, used "laravel-admin-template-master", connected to same Oracle server and got same error. I'm starting to think something is wrong on my side, PHP configurations or Oracle settings? I'm using xampp v3.2.1, PHP 5.5 x86 ts, Windows 7 x64.

Could use some help.

[yajra]

I am working on a Laravel 5 starter template and having no issue like this. So I assume that the error must be something on your configurations. Can you please attach here the error stack trace so we can further check.

[Aminot1]

Should I take a screen shot of this ?

Error Code : 904 Error Message : ORA-00904: "EMAIL": invalid identifier Position : 28 Statement : insert into wts_user (name, email) values (:autoparam0, :autoparam1) returning id into :autoparam2 Bindings : []

1. yajra\Pdo\Oci8\Exceptions\Oci8Exception …\vendor\yajra\laravel-pdo-via-oci8\src\yajra\Pdo\Oci8\Statement.php153
2. yajra\Pdo\Oci8\Statement execute …\vendor\yajra\laravel-oci8\src\yajra\Oci8\Query\Processors\OracleProcessor.php54
3. yajra\Oci8\Query\Processors\OracleProcessor processInsertGetId …\vendor\laravel\framework\src\Illuminate\Database\Query\Builder.php1910
4. Illuminate\Database\Query\Builder insertGetId <#unknown>0
5. call_user_func_array …\vendor\laravel\framework\src\Illuminate\Database\Eloquent\Builder.php972
6. Illuminate\Database\Eloquent\Builder __call …\vendor\yajra\laravel-oci8\src\yajra\Oci8\Eloquent\OracleEloquent.php213
7. Illuminate\Database\Eloquent\Builder insertGetId …\vendor\yajra\laravel-oci8\src\yajra\Oci8\Eloquent\OracleEloquent.php213
8. yajra\Oci8\Eloquent\OracleEloquent insertAndSetId …\vendor\yajra\laravel-oci8\src\yajra\Oci8\Eloquent\OracleEloquent.php167
9. yajra\Oci8\Eloquent\OracleEloquent performInsert …\bootstrap\compiled.php6856
10. Illuminate\Database\Eloquent\Model save …\app\routes.php135
11. {closure} <#unknown>0
12. call_user_func_array …\bootstrap\compiled.php5394
13. Illuminate\Routing\Route run …\bootstrap\compiled.php5061
14. Illuminate\Routing\Router dispatchToRoute …\bootstrap\compiled.php5049
15. Illuminate\Routing\Router dispatch …\bootstrap\compiled.php715
16. Illuminate\Foundation\Application dispatch …\bootstrap\compiled.php696
17. Illuminate\Foundation\Application handle …\vendor\barryvdh\laravel-debugbar\src\Middleware\Stack.php34
18. Barryvdh\Debugbar\Middleware\Stack handle …\bootstrap\compiled.php7821
19. Illuminate\Session\Middleware handle …\bootstrap\compiled.php8428
20. Illuminate\Cookie\Queue handle …\bootstrap\compiled.php8375
21. Illuminate\Cookie\Guard handle …\bootstrap\compiled.php11038
22. Stack\StackedHttpKernel handle …\bootstrap\compiled.php657
23. Illuminate\Foundation\Application run …\public\index.php49

[Aminot1]

PHP Info

oci8 OCI8 Support enabled OCI8 DTrace Support disabled OCI8 Version 2.0.8 Revision $Id: f04114d4d67cffea4cdc2ed3b7f0229c2caa5016 $ Oracle Run-time Client Library Version 11.1.0.6.0 Oracle Compile-time Instant Client Version 11.1

[yajra]

Does the table created via migration or it was already existing? I think the column names defined in your table DDL uses double quotes and thus the column name would be case sensitive.

Check this thread: http://stackoverflow.com/questions/6027961/ora-00904-invalid-identifier

[Aminot1]

Tables already existed, but I just created another table with Navicat For Oracle, still same issue.

[Aminot1]

Even if I try to insert using Navicat, still requires the quotation around column names, any suggestions ? like contacting DB administrator, or playing with the code to add the quotes ?

[yajra]

Maybe you should contact your DBA and ask him why it is required to add quotes. I do not suggest adding quotes coz it will force you to always use raw query and Eloquent will not work as well.

[Aminot1]

Alright thanks mate :)

[yajra]

Your welcome. Please post here if you find the solution for future reference. Thanks!

[Aminot1]

Hi Yajra, finally figured out what was wrong, after my DBA took a look, it seemed that Oracles standard is to have column names in CAPS not small letter, i.e. NAME, EMAIL...etc. After changing tables fields all is working well, without the quotes around field name. Thanks a lot :dancers:

It's not a Framework/package issue, because even when column names were in small letter, Oracle's client (APEX) and Navicat required them to have quotes around them.

Although, FYI, I tried CodeIgniter v2 (latest stable version), connected it to Oracles and it automatically encapsulates fields names with double quotations.

Anyways thanks again mate :)

[yajra]

Great! I didn't know that Oracle can set standard for columns to be all CAPS. Thanks for the heads up!