How to solve Variadic Arguments issue (using C)

2019-11-28T09:59:46Z

Heiko: /* What happened */

How to solve Variadic Arguments issue (using C)

2019-11-28T09:58:04Z

Heiko: /* What happened */

How to solve Variadic Arguments issue (using C)

2019-11-28T09:57:53Z

Heiko: /* What happened */

How to solve Variadic Arguments issue (using C)

2019-11-28T09:55:51Z

User:Heiko

2019-10-16T20:16:39Z

Heiko: /* Articles to maintain */

= About =
This is [[User:Heiko|Heiko]]s personal page, in which he maybe shares some private information or collects bits for new articles.

== Personal information ==
[[User:Heiko|Heiko]] has been in IT since the 90s, but never worked with an AS/400…until 2019. He'll document his first steps in the hope they will be useful to others.

= Ideas for new articles =
tbd

= Articles to maintain =
* [[Basic Software Development]]
** [[Beginners Project: 99 Bottles Of Beer (using REXX)]]
** [[Beginners Project: Hello World (using C)]]
** [[Beginners Project: Hello World (using database, display file and RPG)]]
** [[Beginners Project: Create a database and query it using ODBC from a linux machine]] <-- NEEDS A LOT OF WORK IN THE PHP DEPARTMENT!
* [[Editing Source Files using Eclipse and FTP]]
* [[Compile Sources Without Queuing]]

== Sandbox ==
=== Screen mit MW-Bordmitteln ===

Anmelden
System . . . . . : SLVRLAKE
Subsystem . . . . : QINTER
Bildschirm . . . : QPADEV0004

Benutzer . . . . . . . . . . . .
Kennwort . . . . . . . . . . . .
Programm/Prozedur . . . . . . . .
Men} . . . . . . . . . . . . . .
Aktuelle Bibliothek . . . . . . .

(C) COPYRIGHT IBM CORP. 1980, 2013.

=== Und nun Screen mit Bordmitteln und etwas HTML-Foo ===

<tt>
Anmelden 
System . . . . . : SLVRLAKE 
Subsystem . . . . : QINTER 
Bildschirm . . . : QPADEV0004 
 
Benutzer . . . . . . . . . . . . 
Kennwort . . . . . . . . . . . . 
Programm/Prozedur . . . . . . . . 
Men} . . . . . . . . . . . . . . 
Aktuelle Bibliothek . . . . . . . 
 
 
 
 
 
 
 
 
 
 
 
 
 
(C) COPYRIGHT IBM CORP. 1980, 2013. 
</tt>


=== Using (not-working) console-tag ===
<console>
Test
</console>

<console>

Anmelden
System . . . . . : SLVRLAKE
Subsystem . . . . : QINTER
Bildschirm . . . : QPADEV0004

Benutzer . . . . . . . . . . . .
Kennwort . . . . . . . . . . . .
Programm/Prozedur . . . . . . . .
Men} . . . . . . . . . . . . . .
Aktuelle Bibliothek . . . . . . .

(C) COPYRIGHT IBM CORP. 1980, 2013.
</console>

----
* Links
** [[Basic Commands]]
* References:
<references/>

[[Category:Basic Knowledge]]

Query database using ODBC from a linux machine

2019-10-16T20:04:17Z

Heiko: /* Output */

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!<ref>[[User:Heiko|Heiko]] made this pretty brief, f.e. not every <code>F3</code> or <code>⏎</code> is noted.</ref> 

=== Create a database to play with ===
* This is the third project [[User:Heiko|Heiko]] did, so his personal Library was getting a bit cluttered. Therefore, let's create a library just for this niew project:
** <code>CRTLIB LIB(ODBC) TEXT(Library for ODBC project source)</code>
* <code>CRTSRCPF FILE(ODBC/ODBCDB) RCDLEN(112) TEXT('Project ODBC and Database')</code> to create new physical file to hold the DDS.
* <code>WRKMBRPDM FILE(ODBC/ODBCDB)</code> Work with this file.
* <code>F6</code> to create new member, insert member's name <code>ODBCDB</code><ref>This wil change later. Read on...</ref> and the type <code>PF</code>: [[Image:ODBCDB01.png|666px]]
* Enter this DDS: ([https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_72/rzakc/rzakcdatel.htm Learn what the below means] [https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_74/rzasb/cpdtex.htm Especially time- and dateformats] [https://www.db2tutorial.com/db2-basics/db2-time/ Even more about wibbly-wobbly-timey-wimey-stuff] [https://www.ibm.com/support/knowledgecenter/en/SSEPEK_11.0.0/intro/src/tpc/db2z_datetimetimestamp.html 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 *********************************************************************************************
* <code>14</code> to compile… [[Image:ODBCDB02.png|666px]] …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 <code>ODBCDB</code> to <code>ODBCDBPF</code>: [[Image:ODBCDB03.png|666px]] [[Image:ODBCDB04.png|666px]]
** And try again.
** Check with <code>DSPMSG</code><ref>or use [[Compile Sources Without Queuing]]</ref> whether the compile was successful or not. If it did, you find the new PF in the library: [[Image:ODBCDB10.png|666px]]

=== Insert content into the database ===
* <code>STRSQL</code> + <code>F4</code>
** Enter the library name, in this example <code>ODBC</code><ref>Shortcut: <code>STRSQL LIBOPT(ODBC)</code></ref>
* In the interactive SQL session, enter: <code>INSERT INTO CALLS (CALLER, CALLED, DATE, TIME, TIMESTAMP) VALUES ('Ernie', 'Bert', '2019-10-05', '23.23', '2019-10-05 23:23:42')</code><ref>Timestamp format: https://www.ibm.com/support/knowledgecenter/SSFMBX/com.ibm.swg.im.dashdb.sql.ref.doc/doc/r0007107.html</ref><ref>You can use either <code>.</code> or <code>:</code> as delimiter in time fields. The proper way, according to the documentation I found, is: <code>.</code></ref>
* There are many ways to display the result:
** If you're still in STRSQL, enter: <code>SELECT * FROM CALLS</code>
** On command line, try this: <code>DSPF FILE(ODBC/ODBCDBPF)</code>
** Or use <code>STRDFU</code>, select option 5 and fill out the displayed form
[[Image:ODBC_DSPF01.png|666px|Displayed using <code>DSPF</code>]]

== ODBC Queries ==
Make sure you've set up your ODBC connection (<tt>/etc/odbc.ini</tt>) correctly.

=== Perl ===
Here is a nice [https://www.ibm.com/developerworks/data/library/techarticle/dm-0512greenstein/index.html 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 <code>odbc_</code> command set
** [https://www.ibm.com/developerworks/library/os-php-odbc/index.html HowTo use PHP and ODBC]<ref>Sadly this is for mySQL. Db2 would be much more appreciated.</ref>
* Use <tt>PDO</tt>
** [https://www.php.net/manual/en/ref.pdo-ibm.php Short Overview] '''''@PoC: Is this compatible???'''''
* Use Db2 driver '''''@PoC: Is this compatible???'''''
** [https://www.ibm.com/support/knowledgecenter/en/SS6NHC/com.ibm.swg.im.dashdb.doc/connecting/connect_driver_package.html Db2 client] & [https://public.dhe.ibm.com/ibmdl/export/pub/software/data/db2/drivers/odbc_cli/ direct link]
** [https://github.com/php/pecl-database-ibm_db2 Installation instructions]
** [https://www.php.net/manual/en/function.db2-connect.php Usage] and after client installation: <code>TBD -h</code> 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<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.

==== 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 <code>db2_connect()</code>. <tt>netstat</tt> shows that the TCP-connection is <tt>ESTABLISHED</tt>. 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 <tt>unixodbc-dev</tt>
==== 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 <code>-lodbc</code> 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:
<references/>

Query database using ODBC from a linux machine

2019-10-16T20:04:05Z

Heiko: /* Output */