cooperl22 / laravel-db2 Goto Github PK

laravel-db2 is a simple DB2 service provider for Laravel. It provides DB2 Connection by extending the Illuminate Database component of the laravel framework.

License: Other

PHP 100.00%

laravel-db2 php odbc laravel-framework database driver

laravel-db2's People

Contributors

Stargazers

Watchers

Forkers

cool3663 michaelbonds bintangvirgy austinmaddox bskimball byrgenwerth-freshman jacksonwebservices caopeng86 geiger-it lucasmezencio pulasthibandara pa-m mfrancisc gabrielapg rgreer4 wlanzarin albertosabena markshust benjicoots tomloprod bironeaj mmousett markboyer iplaydu aherngithub darit thalfm deybith mikeguru auserj luepow sebastiankluth tonymorello de-soul jorgcastellano crgg sarahkemp lalbini jonkerw85 xccl karlitoxz davidany amadaukltd silvanoalda chrisdicarlo keganlester albertosabenazirak wpsinc pionect cedricgnn easi-power mrchozo adamrollinson moritzfuchsatmeinl rafa2410 resslinger yornletard mark-gregory-uk obech-hipsotech dmaya mbwilliamson chase-rains

laravel-db2's Issues

ODBC Pagination

First, thank you for writing this package! Got me up and running with Laravel much quicker than I expected. The issue I'm seeing is with pagination, it pulls the first page fine, but subsequent pages throw an error:

SQLSTATE[42000]: Syntax error or access violation: 0 [IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0104 - Token , was not valid. Valid tokens: FROM INTO. (SQLPrepare[0] at /build/php5-8gpaOy/php5-5.5.9+dfsg/ext/pdo_odbc/odbc_driver.c:206) (SQL: select * from (select *, row_number() over (order by 1) as row_num from SHPLOG where SLSHDT = 20151030) as temp_table where row_num between 21 and 40)

Trying the query myself (outside of Laravel), I can only get it to execute successfully if the table name qualifies the * selector, e.g.:

select * from (select SHPLOG.*, row_number() over (order by 1) as row_num from SHPLOG where SLSHDT = 20151030) as temp_table where row_num between 21 and 40

I'm new to Laravel, do you have any suggestions to get my pagination working? Would I be heading in the right direction to try to customize this package to prefix the selector with the table name in queries like this?

Running the ODBC Access driver on Linux, accessing data on a V7R1 machine.

laravel 5.3 Blueprint::indexCommand interface changed

Got this error when running migrations on laravel 5.3

 [ErrorException]
  Declaration of Cooperl\Database\DB2\Schema\Blueprint::indexCommand($type, $columns, $index) should be compatible with Illuminate\D
  atabase\Schema\Blueprint::indexCommand($type, $columns, $index, $algorithm = NULL)

changing Cooperl\Database\DB2\Schema\Blueprint::indexCommand($type, $columns, $index) to
Cooperl\Database\DB2\Schema\Blueprint::indexCommand($type, $columns, $index, $algorithm = NULL) fixed the issue

Problem with User Provider query 'retrieveByCredentials'

Best,

I have a specific problem. The 'retrieveByCredentials' is not getting executed correctly. The function is part of the Eloquent User provider found in https://github.com/laravel/framework/blob/5.8/src/Illuminate/Auth/EloquentUserProvider.php. Other functions work with no problem, for example retrieveById wil retrieve the user with no problem from the db2 database.

This function is executed when using auth()->attempt($x). When I look at the resulting query in telescope something seems to be wrong when comparing it to a correct working mysql query.

DB2 sql
select * from WPBGID where PBGTXTE = FETCH FIRST 1 ROWS ONLY

Mysql
select * from WPBGID where PBGTXTE = '[email protected]' limit 1

I know the columns have strange names, WPBGID is the user table and PBGTXTE is email column. What I see, is that somehow the email adres is not showing up in the db2 query.

To me it seems like somehow the query gets not build correctly. Raw query's and the Ibm ToolKit are working flawless with db2.

Are you able to point me into a direction how this can posibly happen. If it is a bug I would be happen to resolve the isseu with a pull request, but at the moment I'am not shure what the cause could be, and how to fix this isseu.

System & Config
driver: db2_ibmi_odbc
driverName: IBM i Access ODBC Driver
Client OS : Ubuntu 18.04 with UnixODBC
Database OS : AS400, release 7.3, Power 9

how to add isolation to each query

Hi.
Due to technical requirements of the DB master, I am obliged to use an isolation that is probably very common in db2, I need to add "with ur;" at the end of each query, this avoids blocking the database.
Is there a way to configure this from the connection data? for now I'm solving it by doing the queries instead of using eloquent
thank you.

Laravel 5.1 auth

Have you guys tried with the built-in auth system? I can't seem to register a new user. Code works on MySQL but not DB2 using ODBC

Table name using lower case

I have a trouble while using query builder because my table name in db2 using lower case. How to fix this?
I try double quote but not fixed it.

Error message without quote ($users = DB::table('users')->get();):
Illuminate\Database\QueryException
SQLSTATE[42S02]: Base table or view not found: -204 [IBM][CLI Driver][DB2/LINUXX8664] SQL0204N "APP_PUTUSAN.USERS" is an undefined name. SQLSTATE=42704 (SQLNumResultCols[-204] at /tmp/PDO_IBM-1.3.6/ibm_driver.c:153) (SQL: select * from users)

Error message when using double quote ($users = DB::table('"users"')->get();):
Illuminate\Database\QueryException
SQLSTATE[42601]: Syntax error: -104 [IBM][CLI Driver][DB2/LINUXX8664] SQL0104N An unexpected token """" was found following "select * from ". Expected tokens may include: "". SQLSTATE=42601 (SQLNumResultCols[-104] at /tmp/PDO_IBM-1.3.6/ibm_driver.c:153) (SQL: select * from ""users"")

Note:
I can get that data using raw query like this:
$users = DB::select('select * from "users"'); // I'm using double quote on table name

Function sequence error: -99999 [IBM] [CLI Driver]

Hello, when running a query Model::find($id). I get this error:

SQLSTATE[HY010]: Function sequence error: -99999 [IBM][CLI Driver] CLI0125E Error in function sequence. SQLSTATE=HY010 (SQLExecute[-99999] at ext\pdo_odbc\odbc_stmt.c:260) (SQL: select * from name_table where name_table.id = 1 FETCH FIRST 1 ROWS ONLY).

However when I run a query Model::all()

I get all the data normally.

Has anyone had a similar problem?

error when update from 2.3 to 2.5.1

hello. when upgrade to 2.5.1 ... error in line return sprintf(implode(';', $dsnParts), ...$dsnConfig); FatalErrorException' with message 'syntax error, unexpected '.''
in /var/www/web/vendor/cooperl/laravel-db2/src/Connectors/ODBCConnector.php:84

OS400

hello does this connects to os400 also or only to a linux machine with db2 ?

Error when laravel running on linux

Hi,

I'm trying to use this package to connect to a i series db2 server from a laravel application hosted on a linux server (centos).

I'm using db2_ibmi_ibm driver :

I had a first problem with these PDO constants in the options :

PDO::I5_ATTR_DBC_SYS_NAMING => false,
PDO::I5_ATTR_COMMIT => PDO::I5_TXN_NO_COMMIT,
PDO::I5_ATTR_JOB_SORT => false,
PDO::I5_ATTR_DBC_LIBL => '',
PDO::I5_ATTR_DBC_CURLIB => '',

Undefined class constant 'I5_ATTR_DBC_SYS_NAMING'

When I commented theses lines, I got this error :

SQLSTATE[42601]: Syntax error: -104 [IBM][CLI Driver][AS] SQL0104N An unexpected token "" was found following "". Expected tokens may include: "( + - ? : DAY INF NAN RID ROW RRN". SQLSTATE=42601 (SQLNumResultCols[-104] at /root/PDO_IBM-1.3.6/ibm_driver.c:153) (SQL: select * from )

Is there something special to do to make it work on that configuration ? Meaning laravel running on a linux server.

Problem (and temporary solution) connection to System i (AS400) DB2 using db2_ibmi_odbc

I have setted all the parameters in the connection file, leaving the standard values.
Once i try to query the database, the connection fail with the following error.

PDOException
SQLSTATE[42000]: Syntax error or access violation: 0 [IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0104 - Token <END-OF-STATEMENT> was not valid. Valid tokens: ? : USER DEFAULT SYSTEM_USER. (SQLPrepare[0] at /builddir/build/BUILD/php-7.3.14/ext/pdo_odbc/odbc_driver.c:204)

I have debugged a bit the code following the stack trace.
I have found that the problem is not the DSN (i have tested the string composed by laravel-db2 with a plain php PDO call) but the code used to set the default schema.
The file is: vendor/cooperl/laravel-db2/src/Connectors/DB2Connector.php:29
The piece of code that throws the error is:

if (isset($config['schema'])) {

            $schema = $config['schema'];

            $connection->prepare('set schema ' . $schema)

                       ->execute();

        }

May solution consists of commenting the 'schema' => '', element in the connection configuration.
In my opinion this is a temporary solution to the problem. Probability the problem is caused by the query builder engine that puts ";" ath the end of the query string.

Using package on windows with ODBC manager

Hiya,

Great package you have here!

I am using the odbc manager from within windows to setup the connection the ODBC driver. However this packages needs the configuration for:

'System=%s',
'Database=%s',
'UserID=%s',
'Password=%s',

in ODBCConnector.php

I only need the "odbc" option within this connector.

The idea is to allow 2 extra settings to be specified in the config file:

'windows_odbc_defined' => true,
'windows_odbc_name' => 'name'

When these settings are specified the odbc connector will make the odbc connection based on what is specified.

Changes in ODBCConnector.php:

if ($config['windows_odbc_defined'] == true) {
            $dsnParts = [
                'odbc:%s'
            ];

            $dsnConfig = [
                $config['windows_odbc_name']
            ];

        } else {
            $dsnParts = [
                'odbc:DRIVER=%s',
                'System=%s',
                'Database=%s',
                'UserID=%s',
                'Password=%s',
            ];

            $dsnConfig = [
                $config['driverName'],
                $config['host'],
                $config['database'],
                $config['username'],
                $config['password'],
            ];
        }

php artisan migrate duplicate migrations table

Laravel 5.6
PHP 7.1
IBM iSeries v7r2
Zend Server 9.1
Connection via db2_ibmi_ibm

When running php artisan migrate for the first time, the command completes successfully.
Running the command a second time results in;

public → php artisan migrate                                                                                         

   Illuminate\Database\QueryException  : SQLSTATE[42710]: Duplicate object: -601 MIGRATIONS in AJTEST type *FILE already exists. (SQLExecute[-601] at /patched-php-src-7.1.16/php-7.1.16/ext/pdo_ibm/ibm_statement.c:1270) (SQL: create table MIGRATIONS (id int not null generated by default as identity constraint MIGRATIONS_id_primary primary key, migration varchar(255) not null, batch int not null))

  at /www/zendphp7/htdocs/test/public/vendor/laravel/framework/src/Illuminate/Database/Connection.php:664
    660|         // If an exception occurs when attempting to run a query, we'll format the error
    661|         // message to include the bindings with SQL, which will make this exception a
    662|         // lot more helpful to the developer instead of just the database's errors.
    663|         catch (Exception $e) {
  > 664|             throw new QueryException(
    665|                 $query, $this->prepareBindings($bindings), $e
    666|             );
    667|         }
    668| 

  Exception trace:

  1   PDOException::("SQLSTATE[42710]: Duplicate object: -601 MIGRATIONS in AJTEST type *FILE already exists. (SQLExecute[-601] at /patched-php-src-7.1.16/php-7.1.16/ext/pdo_ibm/ibm_statement.c:1270)")
      /www/zendphp7/htdocs/test/public/vendor/laravel/framework/src/Illuminate/Database/Connection.php:458

  2   PDOStatement::execute()
      /www/zendphp7/htdocs/test/public/vendor/laravel/framework/src/Illuminate/Database/Connection.php:458

  Please use the argument -v to see more details.

I have found a similar issue with Postgress located here. I have my schema set properly in the initial config and I have tried prefixing migrations in config/database.php. None of these recommended fixes seem to work. It seems to me that Laravel isn't finding the existing migrations table and is trying to create it again.

Any suggestions would be greatly appreciated! Thanks!

SQLSTATE[IM002] SQLDriverConnect: 0 [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

"odbc:DRIVER={IBM i Access ODBC Driver 64-bit};System=192.168.12.116;Database=AIBSIT;UserID=db2inst4;Password=db2inst4;SIGNON=3;SSL=0;CommitMode=2;ConnectionType ▶"
"db2inst4"
"db2inst4"

i already have setup ODBC to db2 v10.5 and it is working using this code

$conn = new PDO("odbc:sit",'db2inst4','db2inst4'); but it is not working in laravel nor your package

Error connecting using 'driverName' => '{IBM DB2 ODBC DRIVER}'

Hello,

I'm using windows 8.1 64 bits with php_pdo_odbc.dll for php. Already installed IBM ODBC CLI driver 64bits.
When I try to use standard parameters connection like:

'ibmi' => [
            'driver'               => 'db2_ibmi_odbc',           
            'driverName'           => '{IBM DB2 ODBC DRIVER}',            
             // General settings
            'host'                 => env('DB_HOST', 'localhost'),
            'protocol'             => env('DB_PROTOCOL', ''),
            'username'             => env('DB_USERNAME', ''),
            'password'             => env('DB_PASSWORD', ''),
            //Server settings
            'database'             => env('DB_DATABASE', ''),
            'prefix'               => '',
            'schema'               => env('DB_SCHEME', ''),
            'port'                 => env('DB_PORT', '60000'),

I get this error: SQL30081N A communication error has been detected. Communication protocol
being used: "TCP/IP". Communication API being used: "SOCKETS". Location
where the error was detected: "". Communication function detecting the error: "connect".

Analising the connection string I saw that DB2Connector class put all parameters, but when it pass to Connector class(laravel) the port and protocol parameters are not been passed.

Then I do this trick:

'ibmi' => [
            'driver'               => 'db2_ibmi_odbc',           
            'driverName'           => '{IBM DB2 ODBC DRIVER};PROTOCOL=TCPIP;HOSTNAME=MyHost;PORT=60000',            
             // General settings
            'host'                 => env('DB_HOST', 'localhost'),
            'protocol'             => env('DB_PROTOCOL', ''),
            'username'             => env('DB_USERNAME', ''),
            'password'             => env('DB_PASSWORD', ''),
            //Server settings
            'database'             => env('DB_DATABASE', ''),
            'prefix'               => '',
            'schema'               => env('DB_SCHEME', ''),
            'port'                 => env('DB_PORT', '60000'),

Doing this I'v got a connection, but when I tried to call Model::all() method I'v got this error: FatalErrorException
syntax error, unexpected '?'

Unfortunatly, there is no php_pdo_ibm.dll for windows 64bits. Plus, i Access IBM driver was discontinued after windows 7.1.

Any clue will be welcome ^^.

By the way, congratulations and thank you for the effort to develop this plugin. I know linux is better for this purpose, so I'm builduing a virtual machine to test my code.

Best Regards,
Alexander

Symfony \ Component \ Debug \ Exception

Undefined class constant 'I5_ATTR_DBC_SYS_NAMING'

All the PDO:I5 attributes are listed as errors; Reference constant is not found in class.

Is there a typo or am I missing something? New to Laravel.

Thanks

Paginated Order By Raw Bindings Wrong

I have a query like:

$query->where(function ($qu) use ($name) {
    $qu->where('upper(name)', 'like', strtoupper(substr($name, 0, 20)).'%')
       ->orWhere('upper(alpha)', 'like', strtoupper(substr($name, 0, 12)).'%')
       ->orderByRaw('case 
                        when phone like ? then 15
                        when phone like ? then 10
                        else 0
                     end desc', [
                        substr($phone, 0, 6).'%', 
                        substr($phone, 0, 3).'%'
                    ]);
});

This happens to anger DB2 because the phone field is numeric and the $name variable is a string, but the ultimate problem is that the OVER parameters being added to the select statement by DB2Grammar.php's compileOver steal the bindings that should have gone to the where parameters. When binding time comes, the getBindings() call returns something like:

0 = "NAME%"
1 = "NAME%"
2 = "123456%"
3 = "123%"

For a statement that reads like:

select * from (select TABLE_NAME.*,  row_number() over (order by case
    when phone like ? then 15
    when phone like ? then 10
    else 0
end desc, priority asc, name asc, id asc) as row_num 
from TABLE_NAME
where (upper(name) like ? 
   or upper(alpha) like ?) 
) as temp_table where row_num between 6 and 11

So the final query would be something like:

select * from (select TABLE_NAME.*,  row_number() over (order by case
    when phone like 'NAME%' then 15
    when phone like 'NAME%' then 10
    else 0
end desc, priority asc, name asc, id asc) as row_num 
from TABLE_NAME
where (upper(name) like '123456%' 
    or upper(alpha) like '123%') 
) as temp_table where row_num between 6 and 11

When it should be:

select * from (select TABLE_NAME.*,  row_number() over (order by case
    when phone like '123456%' then 15
    when phone like '123%' then 10
    else 0
end desc, priority asc, name asc, id asc) as row_num 
from TABLE_NAME
where (upper(name) like 'NAME%' 
    or upper(alpha) like 'NAME%') 
) as temp_table where row_num between 6 and 11

In short, moving the orderings to the select to get the pagination working while not moving the bindings to match, results in the bindings attachings to the wrong markers. I worked around the issue by manually binding the order parameters a second time to the select component and moving the ordering portion of the query before the wheres in my method. I'm trying to think of a way to do this in DB2Grammer though, so it would work as expected. Any suggestions?

Thanks for reading.

Problem (and solution) on IBMI with driver db2_ibmi_ibm

Hi,

I just installed Laravel 6.12.0 and Cooperl/laravel-ibmi on Zendserver 2019.0.3 on IBMI (PHP version 7.2.24).
I copied the config from this github page and replaced database, username, password, ... to my needs.
driver => db2_ibmi_ibm

When I make a query (through Model or DB facade, I get:

PDOException {#256 ▼
#message: b"SQLSTATE=42705, SQLConnect: -950 DRIVER={IBM i Acce absente du répertoire des bases de données relationnelles."
#code: 0
#file: "/www/zendphp7/htdocs/twins_api_l6/vendor/laravel/framework/src/Illuminate/Database/Connectors/Connector.php"
#line: 70
+errorInfo: null
trace: {▶}
}

To fix this, I had to go Cooperl\Database\DB2\Connectors\IBMConnector and replace this line :

$dsn = "ibm:DRIVER={$config['driverName']};DATABASE={$config['database']};HOSTNAME={$config['host']};PORT={$config['port']};PROTOCOL=TCPIP;";

with this one:
$dsn = "ibm:{$config['database']}";

Then it works.

After doing this, I can even comment out the host, driverName and port from config/database.php, it still works.

I tried to find more info on the DSN and how to "write" it but did not find any clues on how to specify the host, port, etc...

Is it normal ? What did I miss ?

Sorry for my bad English :)

PDO::ATTR_CASE => PDO::CASE_NATURAL

Hi,
I get an error when using pagination with the case option set to 'CASE_NATURAL' or 'CASE_UPPER'.

Undefined property: stdClass::$aggregate
in line 2163 return (int) $results[0]->aggregate;
file /vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php

This is because PHP object properties (in this case, "aggregate") are case sensitive. Changing the above line of code to:
return (int) $results[0]->AGGREGATE;
fixes the problem, however I would rather not change Laravel backend code.

Does this package not support the other two case types? If not, is there a way around this?
Loving this package so far. Using it every day.
Thanks!!

Schema Grammar

Hi there

How thoroughly have you looked at the DB2Grammar file? For example you have the following function:

public function compileTableExists()
{
    return 'select * from information_schema.tables where table_schema = upper(?) and table_name = upper(?)';
}

But from what I can tell Db2 does not have information_schema available. Is this a known issue?

Many thanks!

could not find driver {"exception":"[object] (PDOException(code: 0):

I've setup a scheduled task to run every 5 minutes to update query results in redis cache.

app\Console\Kernel.php

namespace App\Console;

use App\Jobs\QueryLoadsJob;
use Illuminate\Console\Scheduling\Schedule;
use Illuminate\Foundation\Console\Kernel as ConsoleKernel;

protected function schedule(Schedule $schedule)
    {
    	$schedule->job(new QueryLoadsJob())->everyFiveMinutes();
    }

QueryLoadsJob.php

namespace App\Jobs;

use App\Repositories\LoadRepositoryEloquent;
use Illuminate\Bus\Queueable;
use Illuminate\Queue\SerializesModels;
use Illuminate\Queue\InteractsWithQueue;
use Illuminate\Contracts\Queue\ShouldQueue;
use Illuminate\Foundation\Bus\Dispatchable;

class QueryLoadsJob implements ShouldQueue
{
    use Dispatchable, InteractsWithQueue, Queueable, SerializesModels;

    /**
     * Create a new job instance.
     *
     * @return void
     */
    public function __construct()
    {
        //
    }

    /**
     * Execute the job.
     *
     * @param LoadRepositoryEloquent $load_repository_eloquent
     *
     * @return void
     */
    public function handle(LoadRepositoryEloquent $load_repository_eloquent)
    {
        $load_repository_eloquent->getLoads();
    }
}

My LoadsRepositoryEloquent.php

namespace App\Repositories;

use Illuminate\Support\Facades\Cache;
use Redis;
use Prettus\Repository\Eloquent\BaseRepository;
use Prettus\Repository\Criteria\RequestCriteria;
use App\Repositories\LoadRepository;
use App\Models\Load;
use App\Validators\LoadValidator;

class LoadRepositoryEloquent extends BaseRepository implements LoadRepository
{
    /**
     * Specify Model class name
     *
     * @return string
     */
    public function model()
    {
        return Load::class;
    }

    /**
    * Specify Validator class name
    *
    * @return mixed
    */
    public function validator()
    {

        return LoadValidator::class;
    }

    public function getLoads() {

    	$loads = Cache::remember('loads_waiting_cache', 5, function ()
	    {
		    return $this->model::doors()
		                         ->scheduled()
		                         ->admitted()
		                         ->notRejected()
		                         ->notReturned()
		                         ->stillHere()->get();
		});
		    return $loads;

    }
}

my model Loads.php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Prettus\Repository\Contracts\Transformable;
use Prettus\Repository\Traits\TransformableTrait;
use Carbon\Carbon;
use App\Scopes\gblGateScope;
use App\Scopes\gblLimitScope;
use App\Scopes\gblMillScope;

class Load extends Model implements Transformable
{
    use TransformableTrait;
    
    /**
     * The attributes that are mass assignable.
     *
     * @var array
     */
    protected $fillable = [];

	public $connection = 'QISDB';

	protected $table = 'USERID.SHIPCAR'; 
	protected $primarykey = null;
	public $incrementing = false;
	public $timestamps = false;

	protected static function boot() {
		parent::boot();

		// sets the SHPCAR_MILL to M for all queries
		static::addGlobalScope(new gblMillScope());

		// sets the SHPCAR_GATE_LOC to W for all queries
		static::addGlobalScope(new gblGateScope());

		// sets the limit to 1000 just in case
		static::addGlobalScope(new gblLimitScope());
	}


	/**
	 *  Door scope for door numbers to be included in query.
	 *
	 * @param \Illuminate\Database\Eloquent\Builder $query
	 *
	 * @return \Illuminate\Database\Eloquent\Builder
	 */
	public function scopeDoors($query){
		return $query->wherein('USERID.SHIPCAR.SHPCAR_DOOR1',['77', '78', '166', '167', '185A', '191']);
	}

	public function scopeAdmitted($query){
		return $query->where('USERID.SHIPCAR.SHPCAR_LOAD_ADMT_D','<>','9999-01-01');
	}

	public function scopeStillHere($query){
		return $query->where('USERID.SHIPCAR.SHPCAR_COMP_LD_DT','=','9999-01-01');
	}

	public function scopeScheduled($query){
		$startTime = Carbon::now()->subHour(36)->format('Y-m-d');
		$endTime = Carbon::now()->addHour(12)->format('Y-m-d');
		return $query->whereBetween('USERID.SHIPCAR.SHPCAR_SCHD_PU_D',[$startTime, $endTime])
		             ->where('USERID.SHIPCAR.SHPCAR_SCHD_PU_D','<>','9999-01-01');
	}

	public function scopeNotRejected($query){
		return $query->where('USERID.SHIPCAR.SHPCAR_REJECT_DT','=','9999-01-01');
	}

	public function scopeNotRescheduled($query){
		return $query->where('USERID.SHIPCAR.SHPCAR_RESCHD_DT','=','9999-01-01');
	}

	public function scopeNotReturned($query){
		return $query->where('USERID.SHIPCAR.SHPCAR_RETURN_DT','=','9999-01-01');
	}
}

DB2 connection setup

'QISDB' => [
	        'driver' => 'db2_ibmi_ibm', // pdo driver
	        'username' => 'xxxxx',
	        'password' => 'password',
	        'database' => 'QISDB',
	        'prefix' => '',
	        'schema' => 'USERID',
            'options' => [
		        PDO::ATTR_CASE => PDO::CASE_LOWER,
		        PDO::ATTR_PERSISTENT => false, 
                   ]
		],

If I call the repository's getLoads() from a controller, the results come back fine. Running the QueryLoadsJob results in a 'could not find driver {"exception":"[object] (PDOException(code: 0): could not find driver at /var/www/ep2/vendor/laravel/framework/src/Illuminate/Database/Connectors/Connector.php:68)'

Do I need to inject the model somewhere so the Job can find the database connection? Why would the connection work in the controller and not from the job? Any ideas (or a workaround)? Thanks.

First 10 items in stack trace:

[stacktrace]
#0 /var/www/ep2/vendor/laravel/framework/src/Illuminate/Database/Connectors/Connector.php(68): PDO->__construct('ibm:QISDB', 'xxxxx', 'xxxxx', Array)
#1 /var/www/ep2/vendor/laravel/framework/src/Illuminate/Database/Connectors/Connector.php(44): Illuminate\\Database\\Connectors\\Connector->createPdoConnection('ibm:QISDB', 'xxxx', 'xxxxxx', Array)
#2 /var/www/ep2/vendor/cooperl/laravel-db2/src/Connectors/DB2Connector.php(24): Illuminate\\Database\\Connectors\\Connector->createConnection('ibm:QISDB', Array, Array)
#3 /var/www/ep2/vendor/cooperl/laravel-db2/src/DB2ServiceProvider.php(81): Cooperl\\Database\\DB2\\Connectors\\DB2Connector->connect(Array)
#4 [internal function]: Cooperl\\Database\\DB2\\DB2ServiceProvider->Cooperl\\Database\\DB2\\{closure}(Array, 'QISDB')
#5 /var/www/ep2/vendor/laravel/framework/src/Illuminate/Database/DatabaseManager.php(109): call_user_func(Object(Closure), Array, 'QISDB')
#6 /var/www/ep2/vendor/laravel/framework/src/Illuminate/Database/DatabaseManager.php(74): Illuminate\\Database\\DatabaseManager->makeConnection('QISDB')
#7 /var/www/ep2/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Model.php(1135): Illuminate\\Database\\DatabaseManager->connection('QISDB')
#8 /var/www/ep2/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Model.php(1101): Illuminate\\Database\\Eloquent\\Model::resolveConnection('QISDB')
#9 /var/www/ep2/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Model.php(931): Illuminate\\Database\\Eloquent\\Model->getConnection()
#10 /var/www/ep2/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Model.php(877): Illuminate\\Database\\Eloquent\\Model->newBaseQueryBuilder()

Bad return value with CCSID 1208

Hi,

I try this vendor to retrieve data from my iSeries database. The iSeries QCCSID is 65535.

My CCSID conf for this vendor is : 1208

I have a model named EXB and code are execute on Laravel on Ubuntu 18.04.4 with IBM i Access ODBC Driver.

I have a model name EXB, and when i retrieve data, some value are null while when i test with iSQL i get the correct value and not null.

Example :

{
"data": {
"exb01": "CL00000001",
"exb02": "MME",
"exb03": null, <-- this value is not null with iSQL test

After some searches, i can see that all column values which have an accent return a null value on laravel.

Anybody knows what happens ?

Thanks for your help,

Laravel 7.x

Have you had the time look at #61. Please let me know if somethings need to be changed for Laravel 7 compatibility.

Formatting timestamp precision (Y-m-d H:i:s.000000 --> Y-m-d H:i:s)

Hi,
I get "datetime field overflow" error on storing because value is "2019-01-29 12:01:12.000000" instead of "2019-01-29 12:01:12" in the "created_at" column.
How can I set/remove the timestamp precision in the whole application?
Thanks

Error typeTinyInteger does not exist

Hi !

When i try to create the jobs table, i have this error

php artisan queue:table
php artisan migrate

Migrating: 2020_10_19_091025_create_jobs_table

   BadMethodCallException 

  Method Cooperl\DB2\Database\Schema\Grammars\DB2Grammar::typeTinyInteger does not exist.

  at vendor/laravel/framework/src/Illuminate/Support/Traits/Macroable.php:103
     99|      */
    100|     public function __call($method, $parameters)
    101|     {
    102|         if (! static::hasMacro($method)) {
  > 103|             throw new BadMethodCallException(sprintf(
    104|                 'Method %s::%s does not exist.', static::class, $method
    105|             ));
    106|         }
    107| 

      +9 vendor frames 
  10  database/migrations/2020_10_19_091025_create_jobs_table.php:24
      Illuminate\Support\Facades\Facade::__callStatic("create")

      +22 vendor frames 
  33  artisan:37
      Illuminate\Foundation\Console\Kernel::handle(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))

They are a solution ?

I would like to express my gratitude for all your help in this matter

Laravel 6.0 compatibility

Hi! Would love if this was updated to Laravel 6.0. Thank you for all the hard work!

Publish changes to be compatible with LV 5.7

Hi, can you accept the pull request to gain compatibility with Laravel 5.7?

Thanks!

Issue in nested transactions

Hi @cooperl22

When running unit tests using DatabaseTransactions trait to test a method which uses database transactions for its business logic, PDO exception gets thrown.

I found that this is because laravel uses save points to emulate transactions in databases which does not support nested transactions (MySql). However since DB2 support proper nested transactions, this feature seems redundant.

explicitly specifying

/**
 * Determine if the grammar supports savepoints.
 *
 * @return bool
 */
public function supportsSavepoints()
{
    return false;
}

in Cooperl\Database\DB2\Query\Grammars\DB2Grammar.php seems to fix the issue on the surface.

Persistent Connections

I noticed in your example configuration you have set PDO::ATTR_PERSISTENT => false. Does this package support persistent connections to DB2 on iSeries? I gave it a test run and seem to be running into a dropped connection but I can't tell yet if it is PHP, firewall, or DB2 causing it.

ODBCZOSConnector - Undefined variable: driverName

When using the db2_zos_odbc driver, I get the following error:

ErrorException (E_NOTICE)
Undefined variable: driverName

Dependencies

Hi!

I was wondering if you can help me out with a dependencies list. I am asking this as I'm having some connecting issues, thrown by both PDO and ODBC

You use some constants that are not mentioned in the documentation (http://php.net/manual/en/pdo.constants.php), like:

PDO::I5_ATTR_DBC_SYS_NAMING

Therefor, I keep getting errors like:

[Symfony\Component\Debug\Exception\FatalErrorException]  
Undefined class constant 'I5_ATTR_DBC_SYS_NAMING'

Whilst with OBDC, after installing php5-obdc, I can't seem to get past this issue:

[PDOException]                                                                                                             
SQLSTATE[IM002] SQLDriverConnect: 0 [unixODBC][Driver Manager]Data source name not found, and no default driver specified

Posibility for custom Processor

I would like to have the ability to configure a custom Processor. This because I need to trim the response I get. I get often results with allot of added whitespaces. I have solved this by changing the processSelect function in the DB2Processor class. I would prefer to make this configurable.

I'am wondering if you are willing to add or accept a pull request from me to add this feature.

Driver requirement

Hi, does this extension require the PHP driver and modification to the php.ini file? If so, which one, and where can I get them?

Undefined index: aggregate

Hi there. I'm trying to get this to work with DB2 currently, and I keep getting this error from artisan:

  [ErrorException]            
  Undefined index: aggregate  

Exception trace:
 () at /www/october/htdocs/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php:1649
 Illuminate\Foundation\Bootstrap\HandleExceptions->handleError() at /www/october/htdocs/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php:1649
 Illuminate\Database\Query\Builder->aggregate() at /www/october/htdocs/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php:1596
 Illuminate\Database\Query\Builder->max() at /www/october/htdocs/vendor/laravel/framework/src/Illuminate/Database/Migrations/DatabaseMigrationRepository.php:105
 Illuminate\Database\Migrations\DatabaseMigrationRepository->getLastBatchNumber() at /www/october/htdocs/vendor/laravel/framework/src/Illuminate/Database/Migrations/DatabaseMigrationRepository.php:95
 Illuminate\Database\Migrations\DatabaseMigrationRepository->getNextBatchNumber() at /www/october/htdocs/vendor/laravel/framework/src/Illuminate/Database/Migrations/Migrator.php:104
 Illuminate\Database\Migrations\Migrator->runMigrationList() at /www/october/htdocs/vendor/laravel/framework/src/Illuminate/Database/Migrations/Migrator.php:82
 Illuminate\Database\Migrations\Migrator->run() at /www/october/htdocs/modules/system/Classes/UpdateManager.php:351
 System\Classes\UpdateManager->migrateModule() at /www/october/htdocs/modules/system/Classes/UpdateManager.php:132
 System\Classes\UpdateManager->update() at /www/october/htdocs/modules/system/Console/OctoberUp.php:34
 System\Console\OctoberUp->fire() at n/a:n/a
 call_user_func_array() at /www/october/htdocs/vendor/laravel/framework/src/Illuminate/Container/Container.php:523
 Illuminate\Container\Container->call() at /www/october/htdocs/vendor/laravel/framework/src/Illuminate/Console/Command.php:115
 Illuminate\Console\Command->execute() at /www/october/htdocs/vendor/symfony/console/Symfony/Component/Console/Command/Command.php:257
 Symfony\Component\Console\Command\Command->run() at /www/october/htdocs/vendor/laravel/framework/src/Illuminate/Console/Command.php:101
 Illuminate\Console\Command->run() at /www/october/htdocs/vendor/symfony/console/Symfony/Component/Console/Application.php:874
 Symfony\Component\Console\Application->doRunCommand() at /www/october/htdocs/vendor/symfony/console/Symfony/Component/Console/Application.php:195
 Symfony\Component\Console\Application->doRun() at /www/october/htdocs/vendor/symfony/console/Symfony/Component/Console/Application.php:126
 Symfony\Component\Console\Application->run() at /www/october/htdocs/vendor/laravel/framework/src/Illuminate/Foundation/Console/Kernel.php:94
 Illuminate\Foundation\Console\Kernel->handle() at /www/october/htdocs/artisan:36

Any ideas? This is for an AS400 running DB2 v7.1.

I realize that that is outdated, but I checked the grammars, and I don't believe that is part of the issue.

[OFF] Ubuntu/CentOS ODBC Driver

I was able to work with this library on Windows (XAMPP) with the ODBC Driver iSeries.

Now I need to deploy the project in production, and I would like it to be on Linux. The company support team only has one Ubuntu 11 VM with the ODBC Driver installed successfully. According to them it was not possible to install the driver in newer versions or other distributions.

My question: What is the ODBC Driver, and which Linux distribution/version are you using?

My server is an AS/400 late 2009.

ErrorException in DB2Grammer.php: Missing Argument

I updated today to ~5.4 and I get this error now whenever I open a page that uses my ibmi connection:

ErrorException in DB2Grammar.php line 45:
Missing argument 1 for Cooperl\Database\DB2\Schema\Grammars\DB2Grammar::__construct(), called in /var/www/lara/vendor/cooperl/laravel-db2/src/DB2Connection.php on line 114

I see the latest commit changed this. Is it working as intended?

artisan migrate:rollback exception

Artisan command 'migrate:rollback' trigger this error :

[Illuminate\Database\QueryException]                                                                                                                                                                                                       
  SQLSTATE[42000]: Syntax error or access violation: 0 [IBM][Pilote ODBC System i Access][DB2 for i5/OS]SQL0199 - Le mot clé EXISTS est mal placé. Eléments possibles : <FIN-INSTRUCTIO (SQLPrepare[0] at /usr/src/builddir/ext/pdo_odbc/od  
  bc_driver.c:206) (SQL: drop table if exists password_resets)

usage in laravel 4

I have a laravel 4.2 app that I would like to use this for. I'm able to get it installed okay but I'm getting the error "ReflectionException: Class db does not exist".

The full error from artisan is:
{"error":{"type":"ReflectionException","message":"Class db does not exist","file":"/var/www/laravel/vendor/laravel/framework/src/Illuminate/Container/Container.php","line":504}}

in my composer.json I have:
"cooperl/laravel-db2": "1.0.*"

I added 'Cooperl\Database\DB2\DB2ServiceProvider', to my app.php file

and my controller is making a very simple query:
DB::connection('iseries')->table('my_users')->first();.

I get the same error if I attempt to run php artisan

My main question is does this work at all for laravel 4?

Suggession: Create a laravel 5.3 Beranch

Suggestion to create a laravel 5.3 branch to apply laravel 5.3 patches :)

select id from new table

I've the issue when inserting on any table, the query generated is something with select id from new table. I've switched the "new" keyword to "final" and now it seems to work.

EDIT:
Sorry for the poor information I've written on this.
The error that's happening is this: SQL0199N The use of the reserved word "INTO" following "" is not valid. Expected tokens may include: ", )". SQLSTATE=42601
So we are using the PDO_IBM connector but the DB is on zOS. So I've had to modify the DB2Processor.php on vendor/cooperl/laravel-db2/src/Query/Processors to switch the "new" keyword for "final" like on the DB2 zOS ODBC Processor.

DB2 Union Support Lost in Framework Refactor

This refactor: laravel/framework#29496 seems to have changed the base Grammar class sufficiently to break union support in this repo. 'union' has been removed from $selectComponents so it no longer gets compiled automatically, instead getting manually compiled in the same method. DB2Grammar overrides this method but does not do the extra steps so unions seem to be ignored completely.

I saw my existing unions fail when I upgraded from Laravel 5.8 to 6.0 with laravel-db2 moving to 6.1.0.

Operation result set not found

Im getting this error when try to do some SQL sentence :
[QueryException in Connection.php line 662:
SQLSTATE[HY000]: General error: 0 [IBM][Controlador ODBC de Client Access Express (32 bits)]
[DB2/400 SQL]PWS0007 Operation result set not found
SQLPrepare[0] at ext\pdo_odbc\odbc_driver.c:206]

any idea what is happen?

Laravel 5.8 Compatibility?

Hi, will this be made compatible with Laravel 5.8? I'm getting the following when doing a composer update

- cooperl/laravel-db2 5.7.0 requires laravel/framework 5.7.* -> satisfiable by laravel/framework[5.7.x-dev].
- cooperl/laravel-db2 5.7.1 requires illuminate/database 5.7.* -> satisfiable by laravel/framework[5.7.x-dev], illuminate/database[5.7.17, 5.7.18, 5.7.19, 5.7.x-dev, v5.7.0, v5.7.1, v5.7.10, v5.7.11, v5.7.15, v5.7.2, v5.7.20, v5.7.21, v5.7.22, v5.7.23, v5.7.26, v5.7.27, v5.7.28, v5.7.3, v5.7.4, v5.7.5, v5.7.6, v5.7.7, v5.7.8, v5.7.9].

Paginate error with schema config

Hi,

I try to paginate my users list with:

User::paginate(5),

When i change page, i have this error:

SQLSTATE[42000]: Syntax error or access violation: 0 [IBM][Pilote ODBC System i Access][DB2 for i5/OS]SQL0104 - Texte de message d'erreur non disponible. La conversion du message ne peut pas aboutir. (SQLPrepare[0] at ext\pdo_odbc\odbc_driver.c:206) (SQL: select * from (select user.*, row_number() over (order by 1) as row_num from user) as temp_table where row_num between 6 and 10)

However, when i set my model table attribute to 'toto.user' where toto is the name of my schema/library and set config schema to the main database schema, i don't get error and all pagination is done. But i don't want to change all my $table attributes models just for this issue.

I have tried it with laravel 5.5 and laravel 5.8.

Thanks for your help,

DATETIME field is not defined

I get this error when trying to create a column using $table->datetime('date').

  SQLSTATE[42S02]: Base table or view not found: 0 [IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0204 - DATETIME in *LIBL type *SQLUDT not found.

According to this, DB2 does not have a field called DATETIME. Insted its called TIMESTAMP.
http://www.ibm.com/support/knowledgecenter/SSEPEK_10.0.0/intro/src/tpc/db2z_datetimetimestamp.html

Drivers issue

This is not a code issue, i have problems installing drivers and php extensions.
I have a Centos 7 machine with php 7 and apache running.
I have installed ibm_db2 driver and extension through ibm_data_server_driver_package_linuxx64_v11.5.tar.gz and I series client access through iSeriesAccess-7.1.0-1.0.x86_64.rpm.
using the following commands:

sudo yum install libiodbc
sudo yum install unixODBC.x86_64
sudo yum install unixODBC-devel.x86_64
cd /usr/lib64
sudo ln libodbcinst.so.2 libodbcinst.so.1
sudo ln libodbc.so.2 libodbc.so.1
sudo rpm -ivh iSeriesAccess-7.1.0-1.0.x86_64.rpm --nodeps

I have added ibm_db2 extension to the php ini

and setted my laravel connection as follow:

'stealth' => [
            'driver'        => env('STEALTH_DB_DRIVER', 'db2_ibmi_ibm'), // or 'db2_ibmi_ibm' / 'db2_zos_odbc' / 'db2_expressc_odbc
            'driverName'    => env('STEALTH_DB_DRIVERNAME', '{iSeries Access ODBC Driver}'), // or '{iSeries Access ODBC Driver}' / '{IBM i Access ODBC Driver 64-bit}'
            'host'          => env('STEALTH_DB_HOST', ''),
            'username'      => env('STEALTH_DB_USERNAME', ''),
            'password'      => env('STEALTH_DB_PASSWORD', ''),
            'database'      => env('STEALTH_DB_DATABASE', ''),
            'prefix'        => env('STEALTH_DB_PREFIX', ''),
            'schema'        => env('STEALTH_DB_SCHEMA', ''),
            'port'          => env('STEALTH_DB_PORT', 50000),
            'date_format'   => env('STEALTH_DB_DATE_FORMAT', 'Y-m-d H:i:s'), // or 'Y-m-d H:i:s.u' / 'Y-m-d-H.i.s.u'...
            'odbc_keywords' => [
                'SIGNON'                => env('STEALTH_DB_SIGNON', 3),
                'SSL'                   => env('STEALTH_DB_SSL', 0),
                'CommitMode'            => env('STEALTH_DB_COMMITMODE', 2),
                'ConnectionType'        => env('STEALTH_DB_CONNECTIONTYPE', 0),
                'DefaultLibraries'      => env('STEALTH_DB_DEFAULTLIBRARIES', ''),
                'Naming'                => env('STEALTH_DB_NAMING', 0),
                'UNICODESQL'            => env('STEALTH_DB_UNICODESQL', 0),
                'DateFormat'            => env('STEALTH_DB_DATEFORMAT', 5),
                'DateSeperator'         => env('STEALTH_DB_DATESEPERATOR', 0),
                'Decimal'               => env('STEALTH_DB_DECIMAL', 0),
                'TimeFormat'            => env('STEALTH_DB_TIMEFORMAT', 0),
                'TimeSeparator'         => env('STEALTH_DB_TIMESEPARATOR', 0),
                'TimestampFormat'       => env('STEALTH_DB_TIMESTAMPFORMAT', 0),
                'ConvertDateTimeToChar' => env('STEALTH_DB_CONVERTDATETIMETOCHAR', 0),
                'BLOCKFETCH'            => env('STEALTH_DB_BLOCKFETCH', 1),
                'BlockSizeKB'           => env('STEALTH_DB_BLOCKSIZEKB', 32),
                'AllowDataCompression'  => env('STEALTH_DB_ALLOWDATACOMPRESSION', 1),
                'CONCURRENCY'           => env('STEALTH_DB_CONCURRENCY', 0),
                'LAZYCLOSE'             => env('STEALTH_DB_LAZYCLOSE', 0),
                'MaxFieldLength'        => env('STEALTH_DB_MAXFIELDLENGTH', 15360),
                'PREFETCH'              => env('STEALTH_DB_PREFETCH', 0),
                'QUERYTIMEOUT'          => env('STEALTH_DB_QUERYTIMEOUT', 1),1,
                'DefaultPkgLibrary'     => env('STEALTH_DB_DEFAULTPKGLIBRARY', 'QGPL'),
                'DefaultPackage'        => env('STEALTH_DB_DEFAULTPACKAGE', 'A /DEFAULT(IBM),2,1,0'),
                'ExtendedDynamic'       => env('STEALTH_DB_EXTENDEDDYNAMIC', 0),
                'QAQQINILibrary'        => env('STEALTH_DB_QAQQINILIBRARY', ''),
                'SQDIAGCODE'            => env('STEALTH_DB_SQDIAGCODE', ''),
                'LANGUAGEID'            => env('STEALTH_DB_LANGUAGEID', 'ENU'),
                'SORTTABLE'             => env('STEALTH_DB_SORTTABLE', ''),
                'SortSequence'          => env('STEALTH_DB_SORTSEQUENCE', 0),
                'SORTWEIGHT'            => env('STEALTH_DB_SORTWEIGHT', 0),
                'AllowUnsupportedChar'  => env('STEALTH_DB_ALLOWUNSUPPORTEDCHAR', 0),
                'CCSID'                 => env('STEALTH_DB_CCSID', 819),819,
                'GRAPHIC'               => env('STEALTH_DB_GRAPHIC', 0),
                'ForceTranslation'      => env('STEALTH_DB_FORCETRANSLATION', 0),
                'ALLOWPROCCALLS'        => env('STEALTH_DB_ALLOWPROCCALLS', 0),
                'DB2SQLSTATES'          => env('STEALTH_DB_DB2SQLSTATES', 0),
                'DEBUG'                 => env('STEALTH_DB_DEBUG', 0),
                'TRUEAUTOCOMMIT'        => env('STEALTH_DB_TRUEAUTOCOMMIT', 0),
                'CATALOGOPTIONS'        => env('STEALTH_DB_CATALOGOPTIONS', 3),
                'LibraryView'           => env('STEALTH_DB_LIBRARYVIEW', 0),
                'ODBCRemarks'           => env('STEALTH_DB_ODBCREMARKS', 0),
                'SEARCHPATTERN'         => env('STEALTH_DB_SEARCHPATTERN', 1),
                'TranslationDLL'        => env('STEALTH_DB_TRANSLATIONDLL', ''),
                'TranslationOption'     => env('STEALTH_DB_TRANSLATIONOPTION', 0),
                'MAXTRACESIZE'          => env('STEALTH_DB_MAXTRACESIZE', 0),
                'MultipleTraceFiles'    => env('STEALTH_DB_MULTIPLETRACEFILES', 1),
                'TRACE'                 => env('STEALTH_DB_TRACE', 0),
                'TRACEFILENAME'         => env('STEALTH_DB_TRACEFILENAME', ''),
                'ExtendedColInfo'       => env('STEALTH_DB_EXTENDEDCOLINFO', 0),
            ],
            'options' => [
                PDO::ATTR_CASE => PDO::CASE_LOWER,
                PDO::ATTR_PERSISTENT => false,
                //PDO::I5_ATTR_DBC_SYS_NAMING => false,
                //PDO::I5_ATTR_COMMIT => PDO::I5_TXN_NO_COMMIT,
                //PDO::I5_ATTR_JOB_SORT => false,
                //PDO::I5_ATTR_DBC_LIBL => '',
                //PDO::I5_ATTR_DBC_CURLIB => '',
            ]
        ],

But when i try to connect to the database, laravel returns

PDOException
could not find driver

Can someone help me figuring out what i'm doing wrong and let the connection working?
Thanks in advance.

Updated table structure ignored by Laravel

I have been using this plugin to access data from an iSeries via ODBC. Yesterday, a column was added to the table my Eloquent model is associated with. Today, my data is coming in jumbled and the new column isn't showing in the model's attributes.

There are two new columns 'IVTYPE' and 'IVDOMI', which are not showing up in my dump of the model:

InventoryItem {#337 ▼
  #attributes: array:55 [▼
  ...
    "IVFIN" => "A"
    "IVUOM" => "D "
  ...
 ]

When I view the contents of the table directly, the IVFIN is 'B' and the IVUOM is 'CP', but the newly added columns (which are not showing in my attribute list) have 'AT' and 'D', respectively. To me, it looks like the data scooted over to fit the new columns, and the Eloquent model is somehow working positionally and thinks the columns haven't moved.

I ran artisan cache:clear but the problem persists. Is there something I need to do to trigger Laravel to notice the updated table structure?

Thanks for reading.

Selecting individual column names does find the correct information, only the default * style select is giving me a bad list of attributes. If I prefix the star with the table name, it returns correctly. Also, if I change the connection CCSID to 65535, it returns all 57 columns, though the character fields are wrongly encoded. Changing the naming parameter to 1 (SYSTEM) also fetches the right columns.

Problem sending query that contains § char

Hi,
I have a problem running querys that contains char §.
The DB2 instance contins table with column names that contains § char for example CLM$§1.
when i try to execute the query the following query, laravel returns sql error

 DB::connection('ibmi')
    ->table('ST3SFA.TSTCLM$F')
    ->selectRaw('
        TRIM(CAST(CLM$§1 AS VARCHAR(255))) AS id
        ,TRIM(CAST(CLM$02 AS VARCHAR(255))) AS description
        ,TRIM(CAST(ECLM05 AS VARCHAR(255))) AS super_calss_id
        ,CASE WHEN TRIM(CAST(CLM$96 AS VARCHAR(255))) = \'\' THEN CAST(NULL as VARCHAR(255)) ELSE CURRENT_TIMESTAMP END AS deleted_at
    ')
    ->orderByRaw('TRIM(CAST(CLM$§1 AS VARCHAR(255)))')
    ->chunk(10, function ($lines) {
        foreach ($lines as $line) {
            $result = [
                'id'=>ucfirst(strtolower($line->id)),
                'description'=>ucfirst(strtolower($line->description)),
                'super_calss_id'=>ucfirst(strtolower($line->super_calss_id)),
                'deleted_at'=>$line->deleted_at,
            ];
            dump($result);
       }
    });

Error:

Illuminate\Database\QueryException
SQLSTATE[42000]: Syntax error or access violation: 0 (SQLPrepare[0] at /builddir/build/BUILD/php-7.3.14/ext/pdo_odbc/odbc_driver.c:204) (SQL: select TRIM(CAST(CLM$§1 AS VARCHAR(255))) AS id ,TRIM(CAST(CLM$02 AS VARCHAR(255))) AS description ,TRIM(CAST(ECLM05 AS VARCHAR(255))) AS super_calss_id ,CASE WHEN TRIM(CAST(CLM$96 AS VARCHAR(255))) = '' THEN CAST(NULL as VARCHAR(255)) ELSE CURRENT_TIMESTAMP END AS deleted_at from ST3SFA.TSTCLM$F order by TRIM(CAST(CLM$§1 AS VARCHAR(255))) FETCH FIRST 10 ROWS ONLY)

I have tried to enable 'UNICODESQL' => 1, and 'DEBUG' => 65536, in the cfg but without success.
If i try to execute the same query wituout specifying column names and using the *

DB::connection('ibmi')
    ->table('ST3SFA.TSTCLM$F')
    ->selectRaw('*')
    ->get();

the result is returned but the colun is not accessible sing it contains $ in the name.
Anyone has faced this kind of problem before?
Thanks in advance
Riccardo

Getting started with DB2 on i

Hi there

Really sorry to ask here, as it's not directly related to this package! I've used this package before on a standard Ubuntu 14.04 install to access a remote DB2 server on LUW, and that was fine. I used unixODBC with the IBM DB2 cli libs and it (eventually!) worked.

My new requirement is to connect to DB2 running on iSeries, connecting from the same intel-based Ubuntu box I was using before. I'm not a customer of IBM currently, though we're looking into that, and I'm really struggling with sorting out what sort of ODBC driver I need, and where to get it from.

Am I right in thinking that I can't simply use the CLI libs that came with the LUW DB2 server in the same way I did before? I found http://www.ibm.com/developerworks/ibmi/library/i-ibmi-access-client-solutions-linux/ which seems to offer a debian package which would contain the ODBC driver. Is that right?

Again, I appreciate this has nothing to do with this package, but I had a couple of responses before to my LUW questions from people using iSeries, so I was hoping someone might be able to offer a tiny bit of insight.

Many thanks!

Error typeUuid does not exist