Query database using ODBC from a linux machine: Difference between revisions

Latest revision as of 16:17, 19 May 2020

This article isn't finished yet or needs to be revised. Please keep in mind that thus it may be incomplete.

Reason: Rephrase, reformat, set links and wade out unneccessary stuff. Maybe move to user's subpage?

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 99 Bottles of Beer (using REXX) and Hello World (using database, display file and RPG) before you try this!^[1]

Create a database to play with

This is the third project Heiko did, so his personal Library was getting a bit cluttered. Therefore, let's create a library just for this niew project:
- CRTLIB LIB(ODBC) TEXT(Library for ODBC project source)
CRTSRCPF FILE(ODBC/ODBCDB) RCDLEN(112) TEXT('Project ODBC and Database') to create new physical file to hold the DDS.
WRKMBRPDM FILE(ODBC/ODBCDB) Work with this file.
F6 to create new member, insert member's name ODBCDB^[2] and the type PF:
Enter this DDS: (Learn what the below means Especially time- and dateformats Even more about wibbly-wobbly-timey-wimey-stuff and more)

        ***************** 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…

…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:
- 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:

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

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:

Use odbc_ command set
- HowTo use PHP and ODBC^[7]
Use PDO
- Short Overview @PoC: Is this compatible???
Use Db2 driver @PoC: Is this compatible???
- Db2 client & direct link
- Installation instructions
- Usage and after client installation: TBD -h might help!

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].

UPDATE: Installing an newer driver fixed this problem. Unfortunately it has another flaw: If a query result contains an german umlaut, odbc_fetch_array() causes an segmentation fault. After some digging I used setlocale (LC_ALL, 'de_DE'); to get it working.

PHP Code using DB2 client

That wasn't very smooth, either.

I first installed some software:

Get IBM's CLI driver: https://public.dhe.ibm.com/ibmdl/export/pub/software/data/db2/drivers/odbc_cli/ and move it to /home/clidriver
Insert this into .bashrc:

# 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

apt-get install php-dev
pecl install ibm_db2
add extension=ibm_db2.so to /etc/php/7.0/cli/php.ini
If used with apache (untested):
- add LD_LIBRARY_PATH as shown above to httpd.conf
- add extension=ibm_db2.so to /etc/php/7.0/apache2/php.ini
- Beyond: https://www.php.net/manual/en/ibm-db2.installation.php || https://github.com/php/pecl-database-ibm_db2 || https://public.dhe.ibm.com/ibmdl/export/pub/software/data/db2/drivers/odbc_cli/

$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:

http://www.unixodbc.org
http://www.unixodbc.org/doc/db2.html
https://www.easysoft.com/developer/interfaces/odbc/linux.html
https://www.easysoft.com/developer/languages/c/odbc_tutorial.html
https://stackoverflow.com/questions/33020646/gcc-odbc-on-linux-is-not-linking
You might need to install unixodbc-dev

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/

Footnotes and references:

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

[1] Heiko made this pretty brief, f.e. not every F3 or ⏎ is noted.

[2] This wil change later. Read on...

[3] r 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] -mmap-issue: https://stackoverflow.com/questions/42132558/odbc-prepare-gives-fatal-error-allowed-memory-size-exhausted

[9] -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

[1]

[2]

[3]

[4]

[5]

[6]

[7]

[8]

[9]

[10]

[11]

@@ Line 1: / Line 1: @@
+{{FIXME|Rephrase, reformat, set links and wade out unneccessary stuff. Maybe move to user's subpage?}}
 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.
@@ Line 4: / Line 5: @@
 == 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!<ref>[[User:Heiko|Heiko]] made this pretty brief, f.e. not every <code>F3</code> or <code>&#x23CE;</code> is noted.</ref><br/>
+I suggest you try [[99 Bottles of Beer (using REXX)]] and [[Hello World (using database, display file and RPG)]] before you try this!<ref>[[User:Heiko|Heiko]] made this pretty brief, f.e. not every <code>F3</code> or <code>&#x23CE;</code> is noted.</ref><br/>
 === Create a database to play with ===
@@ Line 135: / Line 136: @@
 After some research<ref>php-mmap-issue: https://stackoverflow.com/questions/42132558/odbc-prepare-gives-fatal-error-allowed-memory-size-exhausted</ref><ref>php-mmap-issue: https://stackoverflow.com/questions/21286589/linux-odbc-fatal-error-allowed-memory-size/21412667#21412667</ref><ref>unixodbc-32/64bit drivers: http://www.unixodbc.org/doc/db2.html</ref>I'm pretty sure, its old 32bit ODBC-drivers conflicting with 64bit PHP<ref>Oddly, the same drivers work with perl</ref>.
-STAY TUNED WE ARE WORKING ON A SOLUTION.
+UPDATE: Installing an newer driver fixed this problem. Unfortunately it has another flaw: If a query result contains an [https://en.wikipedia.org/wiki/Umlaut german umlaut], <code>odbc_fetch_array()</code> causes an <tt>segmentation fault</tt>. After some digging I used <code>setlocale (LC_ALL, 'de_DE');</code> to get it working.
 ==== PHP Code using DB2 client ====

Query database using ODBC from a linux machine: Difference between revisions

Latest revision as of 16:17, 19 May 2020

Contents

Create a database

Create a database to play with

Insert content into the database

ODBC Queries

Perl

Script Source Code

Output

PHP

PHP Code using ODBC

PHP Code using DB2 client

PHP Code using PDO

C

C Source Code

Compiling

Output

Navigation menu

Query database using ODBC from a linux machine: Difference between revisions

Latest revision as of 16:17, 19 May 2020

Create a database

Create a database to play with

Insert content into the database

ODBC Queries

Perl

Script Source Code

Output

PHP

PHP Code using ODBC

PHP Code using DB2 client

PHP Code using PDO

C

C Source Code

Compiling

Output

Navigation menu

Search