About

This is Heikos personal page, in which he maybe shares some private information or collects bits for new articles.

Personal information

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

Project: Access database with ODBC

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

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 and 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^[2] 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^[3]
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')^[4]^[5]
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 /etc/odbc.ini correctly. In my case, Silverlake is the name of the defined connection I'll use.

Perl

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

my $db_cfg="YOUR MACHINE"; # See your /etc/odbc.ini
my $db_pwd="YOUR USER";
my $db_user="YOUR PASSWORD";

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;

	# ###
	# Clean up after ourselves - Disconnect from all source and target databases
	#
	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;
}

PHP

TBD

C

tbd

Articles to maintain

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

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

Using (not-working) console-tag

                                  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:

↑ Heiko made this pretty brief, f.e. not every F3 or ⏎ is noted.
↑ 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: .

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

[2] r use Compile Sources Without Queuing

[3] Shortcut: STRSQL LIBOPT(ODBC)

[4] Timestamp format: https://www.ibm.com/support/knowledgecenter/SSFMBX/com.ibm.swg.im.dashdb.sql.ref.doc/doc/r0007107.html

[5] You can use either . or : as delimiter in time fields. The proper way, according to the documentation I found, is: .

[1]

[2]

[3]

[4]

[5]

User:Heiko

Contents

About

Personal information

Ideas for new articles

Project: Access database with ODBC

Create a database

Create a database to play with

Insert content into the database

ODBC Queries

Perl

PHP

C

Articles to maintain

Sandbox

Screen mit MW-Bordmitteln

Und nun Screen mit Bordmitteln und etwas HTML-Foo

Using (not-working) console-tag

Navigation menu

User:Heiko

About

Personal information

Ideas for new articles

Project: Access database with ODBC

Create a database

Create a database to play with

Insert content into the database

ODBC Queries

Perl

PHP

C

Articles to maintain

Sandbox

Screen mit MW-Bordmitteln

Und nun Screen mit Bordmitteln und etwas HTML-Foo

Using (not-working) console-tag

Navigation menu

Search