Data types are incompatible in the equal to operator

[jdavidbakr]

I am getting the following error when doing a query:

>>> \App\FiscalYear::where('FiscalYearName','2016-2017')->get();
Illuminate\Database\QueryException with message 'SQLSTATE[42000]: Syntax error or access violation: 402 [FreeTDS][SQL Server]The data types nvarchar and text are incompatible in the equal to operator. (SQLExecute[402] at /builddir/build/BUILD/php-5.5.35/ext/pdo_odbc/odbc_stmt.c:254) (SQL: select * from "FiscalYear" where "FiscalYearName" = 2016-2017)'

It appears that the default binding is doing it as text, but the data in the database is nvarchar - and apparently MSSQL doesn't like that. How do I define my columns to correctly type themselves in the query bindings?

[sabas]

I hit the same problem when deploying code using a query built with Capsule (so same case as yours). On sqlsrv works normally, but with odbc it doesn't, the solution is to use raw queries for the time being, It should work if you write it as whereRaw ("FiscalYearName = '2016-2017'")

[jdavidbakr]

Thanks @sabas, that's what I ended up doing. It would be great to be able to use Eloquent queries, though.

[ezescordamaglia]

Hola. Tuve el mismo problema. Dejo para otros usuarios que les pueda servir.

Hay que configurar correctamente el archivo de database.php. En mi caso:

    'pgsql' => [
        'driver' => 'pgsql',
        'host' => env('DB_HOST', '127.0.0.1'),
        'port' => env('DB_PORT', '5432'),
        'database' => env('DB_DATABASE', 'forge'),
        'username' => env('DB_USERNAME', 'forge'),
        'password' => env('DB_PASSWORD', ''),
        'charset' => 'utf8',
        'prefix' => '',
        'schema' => 'public',
        'sslmode' => 'prefer',
    ],

    'odbc'   => [
        'driver'   => 'odbc',
        'dsn'      => 'odbc:Sybase_ODBC',
        'host'     => '10.0.2.15',
        'database' => 'databaseName',
        'username' => 'usuario1234',
        'password' => 'password',
        'grammar' => [  
                        'query' => Illuminate\Database\Query\Grammars\SqlServerGrammar::class,
                        'schema' => Illuminate\Database\Schema\Grammars\SqlServerGrammar::class
                        ]  
    ],        

],

Con eso bien aplicado, creando un modelo asi en App/Models/Provincias:

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Provincias extends Model { protected $connection = 'odbc'; protected $table = 'provincias'; }

Después en el controlador puedo usar lo siguiente:

namespace App\Http\Controllers; use Illuminate\Support\Facades\DB; use App\Models\Provincias;

class Controlador extends Controller {

public function index(){ $prov = Provincias::where ('provincia', '01')->get(); return response()->json($prov); }

}

Asi funcionó bien.

Tambien funciona: $prov = Provincias::whereRaw ("provincia = '01'")->get(); $prov = Provincias::all()

Saludos.