Slim Framework and connection to AS400-CodePudding

I tried to connect to AS400 using Php Slim Framework. It returned to me an error about odbc connection php function. I edited this framework files this way.

SETTINGS.PHP

<?php
declare(strict_types=1);
use App\Application\Settings\Settings;
use App\Application\Settings\SettingsInterface;
use DI\ContainerBuilder;
use Monolog\Logger;

return function (ContainerBuilder $containerBuilder) {
    // Global Settings Object
    $containerBuilder->addDefinitions([
        SettingsInterface::class => function () {
            return new Settings([
                'displayErrorDetails' => true, // Should be set to false in production
                'logError'            => false,
                'logErrorDetails'     => false,
                'logger' => [
                    'name' => 'slim-app',
                    'path' => isset($_ENV['docker']) ? 'php://stdout' : __DIR__ . '/../logs/app.log',
                    'level' => Logger::DEBUG,
                ],
                "db" => [
                    'name' => 'EDDXXXXXXX',
                    'username' => 'XXXXXXX',
                    'password' => 'XXXXXXXX',
                    'connection' => 'xxxxxx.xxx.xxxxx'
                ]
            ]);
        }
    ]);
};

DEPENDENCIES.PHP

<?php
declare(strict_types=1);

use App\Application\Settings\SettingsInterface;
use DI\ContainerBuilder;
use Monolog\Handler\StreamHandler;
use Monolog\Logger;
use Monolog\Processor\UidProcessor;
use Psr\Container\ContainerInterface;
use Psr\Log\LoggerInterface;

return function (ContainerBuilder $containerBuilder) {
    $containerBuilder->addDefinitions([
        LoggerInterface::class => function (ContainerInterface $c) {
            $settings = $c->get(SettingsInterface::class);

            $loggerSettings = ...........CODE HERE ..........

            return $logger;
        },

        PDO::class => function (ContainerInterface $c) {

            $settings = $c->get(SettingsInterface::class);

            $dbSettings = $settings->get('db');
            $name = $dbSettings['name'];
            $username = $dbSettings['username'];
            $password = $dbSettings['password'];
            $dsn = "Driver={Client Access ODBC Driver (32-bit)};System=" . 
            $connection . ";libraries=" . $name . 
            ";naming=system;transaction isolation=read committed;Uid=" . 
            $username  .";Pwd=" . $password . ";";
            //return new PDO($dsn, $username, $password);
            return odbc_connect($dsn, $username, $password);
        },

    ]);
};

ROUTES.PHP

$app->get('/db-test', function (Request $request, Response $response) {
        $db = $this->get(PDO::class);
        $sth = $db->prepare("SELECT * FROM XXXX");
        $sth->execute();
        $data = $sth->fetchAll(PDO::FETCH_ASSOC);
        $payload = json_encode($data);
        $response->getBody()->write($payload);
        return $response->withHeader('Content-Type', 'application/json');
    });

When i call /db-test I obtain this server error

  "statusCode": 500,
  "error": {
    "type": "SERVER_ERROR",
    "description": "ERROR: odbc_connect(): SQL error: [Microsoft][Driver Manager ODBC] Nome origine dati non trovato e driver predefinito non specificato., SQL state IM002 in SQLConnect on line 46 in file C:\\slim\\as400\\app\\dependencies.php."
  }

I don't understand why it gives to me that error.

CodePudding user response：

The odbc_connect function in your DI container is not correct, because it returns a resource, but not a PDO object. It should be return new PDO($dsn, $username, $password);
Data source name not found and default driver not specified., SQL state IM002. Make sure that the ODBC driver is installed and that the data-source name exists. I recommend to try this connection string for AS400.

Provider=IBMDA400;Data Source=MY_SYSTEM_NAME;User Id=myUsername; Password=myPassword;Default Collection=MY_LIBRARY;

Driver={IBM i Access ODBC Driver};System=mySystem;Uid=myUser;Pwd=myPassword;

CodePudding user response：

My experience with PHP and IBM i is that you need to use the ODBC drivers, not PDO_ODBC. This experience is admittedly old, and may no longer be true, however, I looked at the current PHP 8.1 documentation for PDO_ODBC and found this:

ibm-db2 Supports access to IBM DB2 Universal Database, Cloudscape, and Apache Derby servers through the free DB2 express-C client.

unixODBC Supports access to database servers through the unixODBC driver manager and the database's own ODBC drivers.

generic Offers a compile option for ODBC driver managers that are not explicitly supported by PDO_ODBC.

On Windows, php_pdo_odbc.dll has to be enabled as extension in php.ini. It is linked against the Windows ODBC Driver Manager so that PHP can connect to any database cataloged as a System DSN.

So PDO_ODBC supports DB2 UDB. This is only one of the three flavors of DB2, and unfortunately not the one used by IBM i. So maybe you can get the generic ODBC PDO to work, but I would stick to just ODBC, not PDO unless you execute the spend money command and purchase the DB2 Connect product which will let you access the enterprise DB2 products (DB2 for i and DB2 for zOS) from a Windows/Unix/Linux box.