Accessing Database - Mysql and Postgresql

Accessing Database - Mysql and Postgresql

Acessando Banco de Dados - Mysql e Postgresql

Luiz Sérgio Araújo Morais - netmoraisap@gmail.com

Abstract

This article aims to show, in a practical way, which is possible through simple interfaces to access and manipulate data in the most popular databases, using a basic resources and common languages, without the need for special drives, which are generally paid, enabling a cost of development and maintenance reduced and standardized.

Methodology

To materialize the proposed will be built a "server interface" in C, using the resources of the library "mysqlclient", composed of functions that allow access to the database Mysql. See: Usando MySQL na linguagem C. It is important to emphasize that there are solutions for other databases, such as the library "libpq" of Postgresql for detail see: Acessando PostgreSQL com C. On the application side will be built two "client consoles," one in Cobol and another with Bash to demonstrate the independence of the methodology with respect to the language used. The "console" represents the application side, and can be seen as the "Control Customers, Suppliers, etc". Already the communication between the client and server modules will be done through a messaging system based on files, but may be replaced by more efficient methods, such as: call direct interface module by the client, passing and receiving the information in memory, such as in the case of Cobol, "call interface using sending receive" or using a protocol to send and receive data, such as Tcp. Following, the installation procedures found in "dem_en.tar.gz".

Requirements

Linux distribution, with the Bash, Mysql with mysqlclient and Micro Focus Cobol installed.

Procedures

Copying to a folder the files described below:

1. demosh - script to run the application in bash

#!/bin/bash

# demosh - script using bash

# identifie the process id

idproc=$$;

# create the files

mkfifo -m 777 democ-$idproc ; > demoi-$idproc ; > demoo-$idproc

> demof-$idproc ; chmod 777 demoi-$idproc demoo-$idproc demof-$idproc

# run the interface server

demosrv.exe $idproc &

# run the client application

sh ./democon.sh $idproc ;

# remove the files

if test democ-$idproc ; then rm -f democ-$idproc ; fi

if test -f demoi-$idproc ; then rm -f demoi-$idproc ; fi

if test -f demoo-$idproc ; then rm -f demoo-$idproc ; fi

if test -f demof-$idproc ; then rm -f demof-$idproc ; fi

2. democob - bash script to run the application in cobol

#!/bin/bash

# democob - script using cobol

# identifies the process id

idproc=$$;

# create the files

mkfifo -m 777 democ-$idproc ; > demoi-$idproc ; > demoo-$idproc

> demof-$idproc ; chmod 777 demoi-$idproc demoo-$idproc demof-$idproc

# run the interface server

demosrv.exe $idproc &

# run the client application

cobrun democon.int $idproc,,, ;

# remove the files

if test democ-$idproc ; then rm -f democ-$idproc ; fi

if test -f demoi-$idproc ; then rm -f demoi-$idproc ; fi

if test -f demoo-$idproc ; then rm -f demoo-$idproc ; fi

if test -f demof-$idproc ; then rm -f demof-$idproc ; fi

3. democon.sh - script of the "client console" in bash

#!/bin/bash

# democon

# receives the process id

idproc=$1;

mpro="bash-mysql: enter command or f to close"

# prepares names of files

democ=`echo "democ-$idproc"`

demoi=`echo "demoi-$idproc"`

demoo=`echo "demoo-$idproc"`

demof=`echo "demof-$idproc"`

clear

while true ; do

# receives the sql command

echo "$mpro"; read ope

# write in the request file

echo "$ope" > $demoi

# releases the server

echo > $democ

# if end request

if [ "$ope" == "f" ] ; then break ; fi

# waiting the server

read < $democ

# read the status file and show

echo "status" ; cat $demof

# read the output file and show

echo "result" ; cat $demoo

done

4. democon.cbl - source of the "client console" in cobol

identification division.

program-id. democon.

environment division.

configuration section.

special-names.

console is crt

decimal-point is comma.

input-output section.

file-control.

select opee assign to opee-dat

organization line sequential status est.

select opes assign to opes-dat

organization line sequential status est.

select opef assign to opef-dat

organization line sequential status est.

select opec assign to opec-dat

organization line sequential status est.

data division.

file section.

fd opee

record is varying 1 to 8192 depending on treg.

01 ropee pic x(8192).

fd opes

record is varying 1 to 8192 depending on treg.

01 ropes pic x(8192).

fd opef.

01 ropef pic x(35).

fd opec.

01 ropec pic x.

working-storage section.

01 est pic xx.

01 opee-dat pic x(35) value space.

01 opes-dat pic x(35) value space.

01 opef-dat pic x(35) value space.

01 opec-dat pic x(35) value space.

01 ope pic x(8192).

01 treg pic 9(4).

01 preg pic 9(4).

01 nproc pic x(35) value space.

01 cmd pic x(80).

01 mpro pic x(78).

01 par pic x(256) value space.

procedure division.

inicio.

accept par from command-line.

*******receives the process id

unstring par delimited by "," into nproc.

display space upon crt.

move "cobol-mysql: enter command or f to close"

to mpro.

*******prepares names of files

move space to opee-dat string "demoi-" nproc

delimited by " " into opee-dat.

move space to opes-dat string "demoo-" nproc

delimited by " " into opes-dat.

move space to opef-dat string "demof-" nproc

delimited by " " into opef-dat.

move space to opec-dat string "democ-" nproc

delimited by " " into opec-dat.

*******receives the sql command

proc.

display mpro upon console.

move space to ope accept ope from console.

*******write in the request file

abr-opee.

open output opee.

if est = "9a" close opee go to abr-opee.

if est not = "00"

display "error opening the entry"

upon console stop run.

move ope to ropee.

perform ver-topee thru fim-topee.

write ropee.

if est not = "00"

display "error recording the entry"

upon console stop run.

close opee.

*******releases the server

lib-srv.

open output opec.

if est = "9a" close opec go to vrf-fim.

if est not = "00"

display "error opening the control"

upon console stop run.

close opec.

*******if end request

vrf-fim.

if ope = "f" stop run.

*******waiting the server

esp-srv.

open input opec.

if est = "9a" close opec go to esp-srv.

if est not = "00"

display "error opening the control"

upon console stop run.

ler-opec.

read opec end close opec go to abr-opef.

if est = "9d" go to ler-opec.

if est not = "00"

display "error reading the control"

upon console stop run.

close opec.

*******read the status file

abr-opef.

open input opef.

if est = "9a" close opef go to abr-opef.

if est not = "00"

display "error opening the status"

upon console stop run.

ler-opef.

read opef end close opef go to ler-opes.

if est = "9d" go to ler-opef.

if est not = "00"

display "error reading the status"

upon console stop run.

display "status" upon console.

display ropef upon console.

close opef.

*******read the output file

abr-opes.

open input opes.

if est = "9a" close opes go to abr-opes.

if est not = "00"

display "error opening the exit"

upon console stop run.

display "result" upon console.

ler-opes.

read opes end go to clo-opes.

if est = "9d" go to ler-opes.

if est not = "00"

display "error reading the output"

upon console stop run.

move ropes to ropee.

perform ver-topee thru fim-topee.

*******show the output

display ropes (1:treg) upon console.

go to ler-opes.

*******close and return

clo-opes.

close opes go to proc.

*******check the size for recording

ver-topee.

if ropee = space move zero to treg go to fim-topee.

move 0128 to treg.

if ropee (0129:8064) = space go to ver-topee1.

move 0256 to treg.

if ropee (0257:7936) = space go to ver-topee1.

move 0512 to treg.

if ropee (0513:7680) = space go to ver-topee1.

move 1024 to treg.

if ropee (1025:7168) = space go to ver-topee1.

move 2048 to treg.

if ropee (2049:6144) = space go to ver-topee1.

move 4096 to treg.

if ropee (4097:4096) = space go to ver-topee1.

move 8192 to treg.

ver-topee1.

if treg = zero go to fim-topee.

if ropee (treg:1) not = space go to fim-topee.

subtract 1 from treg go to ver-topee1.

fim-topee.

exit.

5. demosrv.c - source of the "server interface" in c

/* demosrv */

#include <my_global.h>

#include <stdio.h>

#include <stdlib.h>

#include <unistd.h>

#include <fcntl.h>

#include <mysql.h>

MYSQL conn; MYSQL_RES *resp; MYSQL_FIELD *cpo; MYSQL_ROW lin;

char papl [35] = "", ent [8192] = "", cmd [8192] = "";

char opeedat [35] = "", opesdat [35] = "";

char opefdat [35] = "", opecdat [35] = "";

char terr [5] = "", cerr [5] = "", merr [70] = "", serr [70] = "";

char scmd [8192] = "";

int nlin, ncol, nerr, ind, res;

char m00501 [70] = "MESS: null return";

char eZZ001 [70] = "ERROR: entry not open";

char eZZ002 [70] = "ERROR: entry not read";

char eZZ003 [70] = "ERROR: process not informed";

char eZZ004 [70] = "ERROR: connection is not established";

char eZZ005 [70] = "ERROR: state not open";

char eZZ006 [70] = "ERROR: state not write";

char eZZ007 [70] = "ERROR: output not open";

char eZZ008 [70] = "ERROR: output not write";

char eZZ011 [70] = "ERROR: control not open";

char eZZ012 [70] = "ERROR: control not read";

FILE *opee, *opes, *opef, *opec;

main (argc, argv)

int argc; char *argv[];

{

inicio:

/* receives the process id */

if (argc > 0 && argv [1] != NULL) {strcpy (papl, argv [1]);}

else {strcpy (cerr, "ZZ003"); strcpy (merr, eZZ003); goto erro;}

/* connects database */

mysql_init (&conn); mysql_real_connect (&conn, "localhost",

"demo", "demo", "demo", 0, NULL, 0); nerr = mysql_errno (&conn);

if (nerr != 0 )

{strcpy (cerr, "ZZ004"); strcpy (merr, eZZ004); goto erro;}

/* prepares names of files */

strcpy (opeedat, "demoi-"); strcat (opeedat, papl);

strcpy (opesdat, "demoo-"); strcat (opesdat, papl);

strcpy (opefdat, "demof-"); strcat (opefdat, papl);

strcpy (opecdat, "democ-"); strcat (opecdat, papl);

proc0:

/* wait the client */

strcpy (ent, ""); opec = fopen (opecdat, "r");

if (opec == NULL || ferror (opec))

{strcpy (cerr, "ZZ011"); strcpy (merr, eZZ011); goto erro;}

fgets (ent, 1, opec);

if (ferror (opec))

{strcpy (cerr, "ZZ012"); strcpy (merr, eZZ012); goto erro;}

fclose (opec);

/* open end read entry */

strcpy (ent, ""); opee = fopen (opeedat, "r+");

if (opee == NULL || ferror (opee))

{strcpy (cerr, "ZZ001"); strcpy (merr, eZZ001); goto erro;}

proc1:

fgets (ent, 8192, opee);

if (ferror (opee))

{strcpy (cerr, "ZZ002"); strcpy (merr, eZZ002); goto erro;}

fclose (opee);

if (strncmp ("f", ent, 1) == 0 && strlen (ent) == 2) {goto fim;}

/* run command */

strcpy (cmd, ent); mysql_query (&conn, cmd);

/*verifies the execution */

nerr = mysql_errno (&conn);

resp = mysql_store_result (&conn);

if (resp == NULL) {nlin = 0;} else

{nlin = mysql_num_rows (resp);}

if (nerr == 0 && nlin != 0) {strcpy (cerr, "00000");}

if (nerr == 0 && nlin == 0) {strcpy (cerr, "00501");}

if (nerr != 0) {sprintf (terr, "%i", nerr);

strcpy (cerr, "0"); strcat (cerr, terr);}

/* open and write the state */

opef = fopen (opefdat, "w+");

if (opef == NULL || ferror (opef))

{strcpy (cerr, "ZZ005"); strcpy (merr, eZZ005); goto erro;}

strcpy (serr, cerr); strcat (serr, "\n") ; fputs (serr, opef);

if (ferror (opef))

{strcpy (cerr, "ZZ006"); strcpy (merr, eZZ006); goto erro;}

fclose (opef);

/* open and write the output */

opes = fopen (opesdat, "w+");

if (opes == NULL || ferror (opes))

{strcpy (cerr, "ZZ007"); strcpy (merr, eZZ007); goto erro;}

/* execution with null output */

if (strncmp ("00501", cerr, 5) == 0)

{strcpy (serr, m00501); strcat (serr, "\n"); fputs (serr, opes);

if (ferror (opes))

{strcpy (cerr, "ZZ008"); strcpy (merr, eZZ008); goto erro;}

fclose (opes) ; goto proc2;}

/* execution with error */

if (strncmp ("00000", cerr, 5) != 0)

{strcpy (serr, "ERROR: "); strcat (serr, mysql_error (&conn));

strcat (serr, "\n"); fputs (serr, opes);

if (ferror (opes))

{strcpy (cerr, "ZZ008"); strcpy (merr, eZZ008); goto erro;}

fclose (opes) ; goto proc2;}

/* execution with output */

if (resp == NULL) {fclose (opes); goto proc2;}

while ((lin = mysql_fetch_row (resp)) != NULL)

{cpo = mysql_fetch_fields (resp); strcpy (scmd, "");

ncol = mysql_num_fields (resp);

for (ind = 0; ind < ncol; ind++)

{strcat (scmd, lin [ind]);

if (cpo [ind].length < 256 &&

strlen (lin [ind]) < cpo [ind].length)

{res = cpo [ind].length - strlen (lin [ind]);

while (res != 0) {strcat (scmd, " "); res--;}}}

strcat (scmd, "\n"); fputs (scmd, opes);

if (ferror (opes))

{strcpy (cerr, "ZZ008"); strcpy (merr, eZZ008); goto erro;}}

fclose (opes);

proc2:

/* release the object and client */

mysql_free_result (resp);

opec = fopen (opecdat, "w");

if (opec == NULL || ferror (opec))

{strcpy (cerr, "ZZ011"); strcpy (merr, eZZ011); goto erro;}

fclose (opec); goto proc0;

fim:

/* release database */

mysql_close (&conn);

exit (0);

erro:

/* open and write state */

opef = fopen (opefdat, "w+");

if (opef == NULL || ferror (opef)) {goto dspe;}

strcpy (serr, cerr); strcat (serr, "\n") ; fputs (serr, opef);

if (ferror (opef)) {goto dspe;}

fclose (opef);

/* open and write output */

opes = fopen (opesdat, "w+");

if (opes == NULL || ferror (opes)) {goto dspe;}

strcpy (serr, merr); strcat (serr, "\n"); fputs (serr, opes);

if (ferror (opes)) {goto dspe;}

fclose (opes) ; goto fim;

dspe:

printf ("%s\n%s\n", cerr, merr); goto fim;

}

6. demo.txt - demo table to be loaded in the database

000001|MUOIL DTMANGOIS DU SALVU

000002|UPTLTNAT BUCASCU DT NUSEAMINCT

000003|U E PIRIARU

000004|U G V RIPRISINCUETIS I VINDUS

000005|U GUREAU LUJU LCDU

000006|UNDRI GTNSULVIS DI TLAVIARU

000007|U M ERASCTVUT & ERASCTVUT LCDU

000008|PRIFIACORU MONAEAPUL DI EOBUCU

000009|U P DI EUSCRT

000010|MURAT LTCCT

000011|U PIQOINANU EINCRT ISCICAET S/

000012|U CRUNSPTRCUDTRU I URROMUDTRU

000013|U CRABONU DI SUNCTS JTRNUL I I

000014|E S S

000015|JTSI GTNEULVIS

000016|U DU INEURNUEUT BIRNURDT & EAU

000017|U. FIRRIARU NICT

000018|JTSI RTGIRAT DI UMTRAM

000019|U.P. EURVULHT CRUNSPTRCIS LCDU

000020|U S KUMURUOSKUS

7. Open a session on Linux

7.1. Create a User "demo" in Mysql

mysql -u root -p

grant all privileges on *.* to demo@localhost identified by 'demo';

flush privileges;

quit

7.2. Join mysql as "demo" and make the database "demo"

mysql -u demo -p

create database demo;

quit

7.3. Create the demo table and load demo.txt

mysql -u demo -p demo

create table demo (code char (0006), description char (0030));

load data infile 'pwd/demo.txt' into table demo fields terminated by '|' (code, description);

replace "pwd" by the "routing of your folder/directory, ie /home/abc/..."

7.4. Make sure the data were loaded and quit

select * from demo limit 5;

code description

000001 MUOIL DTMANGOIS DU SALVU

000002 UPTLTNAT BUCASCU DT NUSEAMINCT

000003 U E PIRIARU

000004 U G V RIPRISINCUETIS I VINDUS

000005 U GUREAU & LUJU LCDU

quit

7.5. Compile the "server interface" in c

gcc demosrv.c -l mysqlclient -o demosrv.exe

7.6. Compile the "client console" in cobol

cob -iv democon.cbl

7.7. Run the application in cobol or bash

sh. /democob or sh. /demosh

Enter a sql right:

bash ou cobol-mysql: enter command or f to close

select * from demo limit 10

status

00000

result

000001MUOIL DTMANGOIS DU SALVU

000002UPTLTNAT BUCASCU DT NUSEAMINCT

000003U E PIRIARU

000004U G V RIPRISINCUETIS I VINDUS

000005U GUREAU & LUJU LCDU

000006UNDRI GTNSULVIS DI TLAVIARU

000007U M ERASCTVUT & ERASCTVUT LCDU

000008PRIFIACORU MONAEAPUL DI EOBUCU

000009U P DI EUSCRT

000010MURAT LTCCT

Enter a sql with null return:

bash ou cobol-mysql: enter command or f to close

select * from demo where code > '90000'

status

00502

result

MESS: null return

Enter a wrong sql:

bash ou cobol-mysql: enter command or f to close

command error

status

01064

resultado

ERRO: You have an error in your SQL syntax; check the manual that corresponds to

your MySQL server version for the right syntax to use near 'command error' at

line 1

Enter f to close:

bash ou cobol-mysql: enter command or f to close

f

Uses

The following are some proposals to use and improve the methodology in its entirety, in part or with adjustments and optimizations:

Accepts improvements, as previously described, on the means of communication between modules, through the use of shared memory, use the tcp protocol, alone or combined, in other words, the same interface can work simultaneously with more than one form of communication , and this form can be decided at runtime.

It allows applications built with different languages to access the same database. This can be seen, running two simultaneous sessions, one with the Bash, "demosh" and another with Cobol, "democob" accessing the same database and table.

Allows access to various databases for the same application. Just consider that for example that are built two separate server interfaces, one for Mysql, already established, and other for Postgresql, using the same methodology, and that the two interfaces, according to some pre-established criteria, for example, name of database and table, use both the Mysql and Postgresql simultaneously.

Therefore it is also possible the combination of the two previous features, or multiple applications with multiple databases, facilitating for example, the construction of applications such as "e-commerce", where want to provide users with information that almost always come from several different applications and different databases condensed into a single vision.

Use of middleware schemes for the data access layer,in other words, the solution can provide a common place, a bus of integration, where all systems involved, for example by means of a "meta language to access", send and receive their requests for access and manipulation of data, totally independently of any database will provide the information, see a similar solution with respect to the development of Web Interface for Users in: Migration of Applications for Web Environment.

Even with the "meta language to access," its application to safely use the famous "sql extensions", which are feared by developers because they are specific, but have the advantage of showing greater efficiency in its use in a database.

In relation specifically to Microfocus Cobol, the "reads" and "writes" can be seen from the point of view of Sql as a "meta language". Then it is possible to build a middleware to access the data with a module that intercepts calls to the "extfh", a mechanism to access the native system, and map these commands to the "equivalent" in sql. The practical result of this methodology is in "Tests" below.

Tests

For implementation of practical tests of this method was selected a real application that consists of 27 tables, with approximately 2 million records/lines installed a Linux distribution of the profile of the demonstration proposal already resulting in the following results:

1. Time of conversion of the application of Cobol programs to the database and Mysql Postgressql - zero.

In fact, there is no time for conversion. A program written in Cobol intercepts the calls of "extfh" and with information that is in a "Mysql environment" for example, maps on a table, see below, the "reads" and "whites" to "selects" and "inserts" in accordance with the columns described below:

Op: code of the operation; Description; Opm: method of opening; Lcm: lock mode; Stage: stages of mapping, for example. cob - commands cobol, lk? - Sqls control of release, and lock release, Sop - sql-opening, sql - sql itself (read, write ,...), etc. Mf or Sql: summary of the command Cobol or Sql; Conditions: conditions expected to the result of command; Sql c/r and s/r: return code of the database resulting from transactions with and without data returned, and St Mf: file-status standard Micro Focus to be returned.

Table mapping commands from Micro Focus Cobol to Sql Mysql/Postgresql

2. Time of conversion of data from Cobol files to the database and Mysql Postgressql - average of 9 minutes.

This phase occurs only in "load" the system is required or when a change in the structure of the tables. Is the automatic creation of descriptions of the tables, based on descriptions of Cobol pre-existing files and copy the data later.

3. In normal use of the system, comparing the times of access to the Cobol with Mysq and Postgresq, in the options of daily use, such as maintenance of the database, reports and consultations which usually work with a small number of records/lines, the difference is imperceptible. In cases in which involved large quantities of records/lines such as, general processes of calculation, there is slowness in the access to the tables of the databases. For that have an idea, a process of simple copy of a table of 40,000 records/lines in Cobol which takes about 6.40 seconds, with Mysqt 245.34 seconds and with Postgresql in 283.56 seconds, which is a difference quite significant.

Conclusions

The methodology, though attractive in issues of cost and time of deployment and maintenance, ease of conversion and integration, it is not behaving properly for large volumes, in the case of automatic conversion using Cobol. When to see the cycle of copying, as already shown, it is noted that within each cycle, the phase that consumes more time is the communication between modules "client mapper" and "server interface", using the messaging system in files. It is believed that the adoption of other means of communication, replacing or combining with the already existing, such as those already mentioned, will minimize the problem fairly. Moreover, this is a problem in a very specific situation, in other words, for use with the Cobol in conditions of automatic conversion of the system. It should be remembered that applications written in Cobol have generally by the characteristic nature of accessing the records/lines one by one, even for large volumes, moreover, the databases, accessed with Sql naturally work faster when the commands for large volumes have been written with the idea of a set of lines at once. Therefore nothing prevents the use of the methodology, in any language, even Cobol, provided that the procedures for large volumes have Sqls specific commands, which would entail the re-writing code therefore not utilizing the full benefits mentioned in automatic conversion of the applications.

References

1. Using MySQL in the C language - Ricardo R Lucca. Usando MySQL na linguagem C

2. Accessing PostgreSQL with C - Poleto. Acessando PostgreSQL com C

3. Migration of Environment for Web Applications - Luiz Sérgio A. Morais. Migration of Applications for Web Environment - A Practical Approach