Query database using ODBC from a linux machine

From Try-AS/400
Jump to navigation Jump to search

Accessing a AS/400 via ODBC is something that might come in handy some day, so I decided to explore how it can be done.

Be warned: This is more like a log, showing (and hopefully solving) all issues I ran into doing this. I think this is more helpful than a ideal world example.

Create a database

I suggest you try Beginners Project: 99 Bottles Of Beer (using REXX) and Beginners Project: Hello World (using database, display file and RPG) before you try this![1]

Create a database to play with

        ***************** Datenanfang *******************************************************************************************
0001.00      A          R CALLS                                                                                  191002          
0002.00      A            CALLER        42A                                                                      191002          
0003.00      A            CALLED        42A                                                                      191002          
0004.00      A            DATE            L                TIMFMT(*ISO)                                          191002          
0005.00      A            TIME            T                                                                      191002          
0006.00      A            TIMESTAMP       Z                                                                      191002          
        ******************Datenende *********************************************************************************************
  • 14 to compile…
    ODBCDB02.png
    …I failed again. Stupid me. I can't create a new physical database file in the same place, where source physical file (with the same name!) exists.
    • Just rename the DDS member from ODBCDB to ODBCDBPF:
      ODBCDB03.png
      ODBCDB04.png
    • And try again.
    • Check with DSPMSG[3] whether the compile was successful or not. If it did, you find the new PF in the library:
      ODBCDB10.png

Insert content into the database

  • STRSQL + F4
    • Enter the library name, in this example ODBC[4]
  • In the interactive SQL session, enter:
    INSERT INTO CALLS (CALLER, CALLED, DATE, TIME, TIMESTAMP) VALUES ('Ernie', 'Bert', '2019-10-05', '23.23', '2019-10-05 23:23:42')[5][6]
  • There are many ways to display the result:
    • If you're still in STRSQL, enter: SELECT * FROM CALLS
    • On command line, try this: DSPF FILE(ODBC/ODBCDBPF)
    • Or use STRDFU, select option 5 and fill out the displayed form

Displayed using DSPF

ODBC Queries

Make sure you've set up your ODBC connection (/etc/odbc.ini) correctly.

Perl

Here is a nice HowTo from IBM. Worth reading.

Script Source Code

#!/usr/bin/perl
use strict;
use DBI;

my $db_cfg="YOURCONFIG"; # See your /etc/odbc.ini
my $db_pwd="YOURUSER";
my $db_user="YOURPASSWORD";

printf("Connecting to source database on $db_cfg...\n");
my $dbh = DBI->connect('DBI:ODBC:' . $db_cfg, $db_user, $db_pwd, {PrintError => 1, AutoCommit => 1}); # Connecting to Db2 database
if ( $dbh )
{
	my $sth = $dbh->prepare ("SELECT * FROM ODBC/ODBCDBPF");
	if (!$sth->execute)
	{
		printf("Error: Preparing select on source failed.\n");
		die;
	}
	
	my $fCaller; 
	my $fCalled;
	my $fDate;
	my $fTime;
	my $fTS;	
	while ( ($fCaller, $fCalled, $fDate, $fTime, $fTS) = $sth->fetchrow ) # Read one row from source table
	{
		printf("%s, %s, %s, %s, %s\n", $fCaller, $fCalled, $fDate, $fTime, $fTS);
	} 

	# Clean up last statements
	$sth->finish;

	# Disconnect from database
	if ( $dbh )
	{
		print("Disconnecting from $db_cfg...\n");
		$dbh->disconnect;
	}
}
else
{
	printf("Error: Connection to database (%s) failed:\n%s\n%s\n%s\n", $db_cfg,	$DBI::err, $DBI::errstr, $DBI::state);
	die;
}

Output

$ ./as400-odbc.pl 
Connecting to source database on YOURCONFIG...
Ernie                                      Bert                                       2019-10-05 23:24:00 2019-10-05 23:23:42.000000
Bob                                        Heppo                                      2019-11-06 05:05:05 2019-10-05 23:23:42.000000
Disconnecting from YOURCONFIG...

PHP

I found several possible approaches:

PHP Code using ODBC

Let me say right away, that didn't work out as easy as I hoped.

<?php

/* Sample Code: How to query a Db2 database on a AS/400 (iSeries) 
 * Note:        apt-get install php-odbc might be necessary
 * About:       https://try-as400.pocnet.net
 * */

$db_cfg="YOURCONFIG"; # See your /etc/odbc.ini
$db_user="YOURUSER";
$db_pwd="YOURPASSWORD";

$dbh=odbc_connect($db_cfg, $db_user, $db_pwd);
if ($dbh)
{
	$sql="SELECT * FROM ODBC/ODBCDBPF";
	printf("Preparing \"$sql\"...\n");
	$sth=odbc_prepare($dbh, $sql);

As you can see, the source isn't complete. That's because I wasn't able to execute it any further. Output so far:

Preparing "SELECT * FROM ODBC/ODBCDBPF"...

mmap() failed: [12] Cannot allocate memory

PHP Fatal error:  Out of memory (allocated 2097152) (tried to allocate 206158430251 bytes)

The driver seems to need 206158430251 bytes, that's 192GB, a bit greedy for my taste.

After some research[8][9][10]I'm pretty sure, its old 32bit ODBC-drivers conflicting with 64bit PHP[11].

STAY TUNED WE ARE WORKING ON A SOLUTION.

PHP Code using DB2 client

That wasn't very smooth, either.

I first installed some software:

# CLI Driver (PHP -> Db2 (AS/400),
export IBM_DB_HOME=/home/clidriver
export LD_LIBRARY_PATH=${IBM_DB_HOME}/lib
export PATH=${IBM_DB_HOME}/bin:$PATH
$db_user="YOURUSER";
$db_pwd="YOURPASSWORT";
$db_db = 'ODBC';
$db_hostname = 'silverlake.intern.mathpeter.com';
$db_port = 8471; //50000; /* https://www.ibm.com/support/pages/tcpip-ports-required-ibm-i-access-and-related-functions */
$db_options = array('autocommit' => DB2_AUTOCOMMIT_ON);

ini_set("display_errors", 1);

//$connectionString = "DRIVER={IBM DB2 ODBC DRIVER};DATABASE=$db_db;HOSTNAME=$db_hostname;PORT=$db_port;PROTOCOL=TCPIP;UID=$db_user;PWD=$db_pwd;"; // takes for ever to connect
$connectionString = "DATABASE=$db_db;HOSTNAME=$db_hostname;PORT=$db_port;PROTOCOL=TCPIP;UID=$db_user;PWD=$db_pwd;"; // takes for ever to connect
printf("connectionString: $connectionString\n");
$dbh=db2_connect($connectionString, NULL, NULL, $db_options); // https://www.php.net/manual/en/function.db2-connect.php 

This code stalls at db2_connect(). netstat shows that the TCP-connection is ESTABLISHED. It doesn't create heavy load or traffic. It doesn't run into a timeout.

STAY TUNED - MAYBE I CAN GET THIS WORKING.

PHP Code using PDO

TBD

C

Nice to know:

C Source Code

/* File: as400-odbc.c
* About: Learn more here: https://try-as400.pocnet.net
*/

#include <stdio.h>
#include <stdlib.h>
#include <sql.h>
#include <sqlext.h>
#include <string.h>

#define RESULT_LEN  256

char *odbcConnectionString = "DSN=YOURCONFIG;UID=YOURLOGIN;PWD=YOURPASSWORD;"; // See /etc/odbc.ini for DSN, use your AS/400 account as credentials.

#define CHECK_ERROR(e, s, h, t)\
	(\
		{\
			if (e!=SQL_SUCCESS && e != SQL_SUCCESS_WITH_INFO)\
			{ extract_error(s, h, t); goto exit;}\
		}\
	)

void extract_error(char *fn, SQLHANDLE handle, SQLSMALLINT type)
{
	SQLINTEGER i = 0;
	SQLINTEGER NativeError;
	SQLCHAR SQLState[7];
	SQLCHAR MessageText[256];
	SQLSMALLINT TextLength;
	SQLRETURN ret;

	fprintf(stderr, "\nThe driver reported the following error %s\n", fn);
	do
 	{
		ret = SQLGetDiagRec(type, handle, ++i, SQLState, &NativeError,
			MessageText, sizeof(MessageText), &TextLength);
		if (SQL_SUCCEEDED(ret)) {
			printf("%s:%ld:%ld:%s\n",
				SQLState, (long)i, (long)NativeError, MessageText);
		}
	} while (ret == SQL_SUCCESS); 
}

int main()
{

	SQLHENV   henv = SQL_NULL_HENV;    // Environment
	SQLHDBC   hdbc = SQL_NULL_HDBC;    // Connection handle
	SQLHSTMT  hstmt = SQL_NULL_HSTMT;  // Statement handle
	SQLRETURN retcode;
	SQLCHAR   fCaller[RESULT_LEN];
	SQLCHAR   fCalled[RESULT_LEN];
	SQLCHAR   fDate[RESULT_LEN];
	SQLCHAR   fTime[RESULT_LEN];
	SQLCHAR   fTS[RESULT_LEN];
	SQLCHAR   outstr[1024];
	SQLSMALLINT outstrlen;
	int i = 0;

	// Allocate environment handle
	retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
	CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_ENV)", henv, SQL_HANDLE_ENV);

	// Set the ODBC version environment attribute
	retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER*)SQL_OV_ODBC3, 0);
	CHECK_ERROR(retcode, "SQLSetEnvAttr(SQL_ATTR_ODBC_VERSION)", henv, SQL_HANDLE_ENV);

	retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER*)SQL_OV_ODBC3, 0);
	CHECK_ERROR(retcode, "SQLSetEnvAttr(SQL_ATTR_ODBC_VERSION)", henv, SQL_HANDLE_ENV);

	// Allocate connection handle
	retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
	CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_DBC)", hdbc, SQL_HANDLE_DBC);

	// Set login timeout to 5 seconds
	SQLSetConnectAttr(hdbc, SQL_LOGIN_TIMEOUT, (SQLPOINTER)5, 0);
	CHECK_ERROR(retcode, "SQLSetConnectAttr(SQL_LOGIN_TIMEOUT)", hdbc, SQL_HANDLE_DBC);

	// Connect to data source, replace with your connection string
	retcode = SQLDriverConnect(hdbc, NULL, odbcConnectionString, SQL_NTS, outstr, sizeof(outstr), &outstrlen, SQL_DRIVER_NOPROMPT);

	// Allocate statement handle
	retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
	CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_STMT)",	hstmt, SQL_HANDLE_STMT);

	// Execute SQL Query
	retcode = SQLExecDirect(hstmt, (SQLCHAR*) "SELECT * FROM ODBC/ODBCDBPF", SQL_NTS);
	CHECK_ERROR(retcode, "SQLExecDirect()", hstmt, SQL_HANDLE_STMT);

	// Bind columns
	retcode = SQLBindCol(hstmt, 1, SQL_C_CHAR, &fCaller, RESULT_LEN, 0);
	retcode = SQLBindCol(hstmt, 2, SQL_C_CHAR, &fCalled, RESULT_LEN, 0);
	retcode = SQLBindCol(hstmt, 3, SQL_C_CHAR, &fDate, RESULT_LEN, 0);
	retcode = SQLBindCol(hstmt, 4, SQL_C_CHAR, &fTime, RESULT_LEN, 0);
	retcode = SQLBindCol(hstmt, 5, SQL_C_CHAR, &fTS, RESULT_LEN, 0);

	// Fetch and print each row of data until SQL_NO_DATA returned.
	for (i = 0; ; i++)
	{
		retcode = SQLFetch(hstmt);
		if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)
		{
			printf("Row %d: %s\t%s\t%s\t%s\t%s\n", i, fCaller, fCalled, fDate, fTime, fTS);
		}
		else
		{
			if (retcode != SQL_NO_DATA)
			{
				CHECK_ERROR(retcode, "SQLFetch()", hstmt, SQL_HANDLE_STMT);
			}
			else
			{
				break;
			}
		}
	}

exit:

	//
	// Free handles
	//
	// Statement
	if (hstmt != SQL_NULL_HSTMT)
	{
		SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
	}

	// Connection
	if (hdbc != SQL_NULL_HDBC)
	{
		SQLDisconnect(hdbc);
		SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
	}

	// Environment
	if (henv != SQL_NULL_HENV)
	{
		SQLFreeHandle(SQL_HANDLE_ENV, henv);
	}

	return 0;
}

Compiling

#!/bin/bash

clear
gcc as400-odbc.c -o as400-odbc_c -lodbc

Note the -lodbc for linking against the ODBC-libraries.

Output

$./as400-odbc_c 
Row 0: Ernie                                     	Bert                                      	2019-10-05	23:24:00	2019-10-05 23:23:42.000000
Row 1: Bob                                       	Heppo                                     	2019-11-06	05:05:05	2019-10-05 23:23:42.000000
$ 

\o/

  1. Heiko made this pretty brief, f.e. not every F3 or is noted.
  2. This wil change later. Read on...
  3. or use Compile Sources Without Queuing
  4. Shortcut: STRSQL LIBOPT(ODBC)
  5. Timestamp format: https://www.ibm.com/support/knowledgecenter/SSFMBX/com.ibm.swg.im.dashdb.sql.ref.doc/doc/r0007107.html
  6. You can use either . or : as delimiter in time fields. The proper way, according to the documentation I found, is: .
  7. Sadly this is for mySQL. Db2 would be much more appreciated.
  8. php-mmap-issue: https://stackoverflow.com/questions/42132558/odbc-prepare-gives-fatal-error-allowed-memory-size-exhausted
  9. php-mmap-issue: https://stackoverflow.com/questions/21286589/linux-odbc-fatal-error-allowed-memory-size/21412667#21412667
  10. unixodbc-32/64bit drivers: http://www.unixodbc.org/doc/db2.html
  11. Oddly, the same drivers work with perl