Acucobol interface

Roberto Fichera <robyf@tekno-soft.it> writes:

My first think was to bypass the SQL translation and use the Postgresql low
level routines.
I need to see the tables as record oriented archive, so I can scan
sequentially (forward and
backward) each record, lock/unlock it, insert and delete it and start to
read the records with
a match of a specific key.

I don't think you want an SQL database at all. Possibly something like
Sleepycat's Berkeley DB package is closer to what you are looking for...

regards, tom lane

At 18.13 05/06/01 -0400, Tom Lane wrote:

Roberto Fichera <robyf@tekno-soft.it> writes:

My first think was to bypass the SQL translation and use the Postgresql

low

level routines.
I need to see the tables as record oriented archive, so I can scan
sequentially (forward and
backward) each record, lock/unlock it, insert and delete it and start to
read the records with
a match of a specific key.

I don't think you want an SQL database at all. Possibly something like
Sleepycat's Berkeley DB package is closer to what you are looking for...

regards, tom lane

I know the Sleepycat's Berkelay DB packages but isn't what I need.
I need a relational database that can be used outside our Acucobol program,
like Excel, Access, Apache and in general a SQL view of our data for external
analysis and presentation. This is why I'm thinking to use SQL and in
particular
the PostgreSQL. Currently there is only one direct interface from Acucobol and
a SQL server and was developed by DBMaker for their SQL server, but have
some limitation that I want bypass.

regards,

Roberto Fichera.

Roberto Fichera wrote:

Hi All,

I'm developing (currently in pre-alfa stage) a Acucobol interface for the
Postgresql.
The Acucobol runtime have a generic FS API interface that handle the work
with the
record oriented files, defining the open, close, read, write and so on low
level function I can
extend the runtime to talk with any file and database.

My current work translate each Acucobol FS command in a relative Postgresql
query and
the returned tuple will be translated in a record oriented view.
After some performance tests I've notice that this path have much overhead
and because
this I was thinking to redesign the interface.

My first think was to bypass the SQL translation and use the Postgresql low
level routines.
I need to see the tables as record oriented archive, so I can scan
sequentially (forward and
backward) each record, lock/unlock it, insert and delete it and start to
read the records with
a match of a specific key.

Does anyone know where can I start to search/read/learn/study some
document/code of the
Postgresql low level routines ?

If need some detail, please ask ;-)!

Thanks in advance.

Roberto Fichera.

What you are looking for is a very powerful database back-end, as Tom Lane
suggests, something like Berkeley DB might do, but then what you want is a SQL
interface over that.

I am reticent to admit that I have done a little COBOL and the interface for
data paradigms is very good for a dBase like package. If you can live without a
client/server interface ala Postgres, and can live with a file based access
methodology, then what you want is doable.

I'm not aware what platform you wish to run your program, I am assuming
Windows. The old dBase format is currently being used under the name "xbase."
There are many libraries that conform to this file format and offer the type of
access which you wish to have. On top of that, there are ODBC drivers (in UNIX
and Windows, btw) for these xBase files.

You write acucobol extensions using some generic xbase access layer, and use
the ODBC xbase driver for applications like Access and Excel.

You'll have to sort out all the issues like concurrent access, and stuff like
that, but it should come pretty close to what you want to do.

At 08.03 07/06/01 -0400, mlw wrote:

Roberto Fichera wrote:

Hi All,

I'm developing (currently in pre-alfa stage) a Acucobol interface for the
Postgresql.
The Acucobol runtime have a generic FS API interface that handle the work
with the
record oriented files, defining the open, close, read, write and so on low
level function I can
extend the runtime to talk with any file and database.

My current work translate each Acucobol FS command in a relative Postgresql
query and
the returned tuple will be translated in a record oriented view.
After some performance tests I've notice that this path have much overhead
and because
this I was thinking to redesign the interface.

My first think was to bypass the SQL translation and use the Postgresql low
level routines.
I need to see the tables as record oriented archive, so I can scan
sequentially (forward and
backward) each record, lock/unlock it, insert and delete it and start to
read the records with
a match of a specific key.

Does anyone know where can I start to search/read/learn/study some
document/code of the
Postgresql low level routines ?

If need some detail, please ask ;-)!

Thanks in advance.

Roberto Fichera.

What you are looking for is a very powerful database back-end, as Tom Lane
suggests, something like Berkeley DB might do, but then what you want is a SQL
interface over that.

I've already evaluated the Berkeley DB interface, and surely it's a good
interface, it maybe superior of the current Acucobol proprietary format
when the archive is very large (several Gb).

I am reticent to admit that I have done a little COBOL and the interface for
data paradigms is very good for a dBase like package. If you can live
without a
client/server interface ala Postgres, and can live with a file based access
methodology, then what you want is doable.

The main problem is that we want see our data as relational database
and we want continue to use the current programs. Currently we have
some customer that have their company archive large around 50Gb.

I'm not aware what platform you wish to run your program, I am assuming
Windows. The old dBase format is currently being used under the name "xbase."
There are many libraries that conform to this file format and offer the
type of
access which you wish to have. On top of that, there are ODBC drivers (in UNIX
and Windows, btw) for these xBase files.

Acucobol runtime, currently is present in around 650 different platform
(HW/SW)
so we can run the same programs in different environment. We use Linux
and WNT/W2K as server and W9x/WME as client. Use the xBase format
isn't a good choice when we have a several Gb of data, this is why I'm
thinking
to the PostgreSQL. The current Acucobol's "flat file" isn't adequate to manage
such large files, we need a way to see that files as relational DB.

You write acucobol extensions using some generic xbase access layer, and use
the ODBC xbase driver for applications like Access and Excel.

You'll have to sort out all the issues like concurrent access, and stuff like
that, but it should come pretty close to what you want to do.

I have already done some work. I've implemented an extension of the generic
Acucobol FS layer that talk with a PostgreSQL. This lowlevel layer
translate each
FS primitive in a query. The acucobol's record is translated in attribute
(and vice versa)
using a XFD file (eXtended Fields Description) which describe each record's
field and
that is cached in memory. This file is generated by the acucobol compiler
for each used file.
With this informations I'm able to perform a complete query to the
PostgreSQL, the
returned tuples will be translated in the expected "flat record" and
finally returned to the
runtime for its work.

I know, the cobol use a different philosophy than a relational DB but this
my work
show that this two different world could talk. Also, I know the difficulty
of the major lowlevel
integration. I need some doc/indication/files-to-read of PostgreSQL
lowlevel routines
to see if this different world could have a major integration bypassing the
"overhead" of
the query accessing directly to the DB.

Roberto Fichera.

Roberto Fichera wrote:

I am reticent to admit that I have done a little COBOL and the interface for
data paradigms is very good for a dBase like package. If you can live
without a
client/server interface ala Postgres, and can live with a file based access
methodology, then what you want is doable.

The main problem is that we want see our data as relational database
and we want continue to use the current programs. Currently we have
some customer that have their company archive large around 50Gb.

In what format is this data?

I'm not aware what platform you wish to run your program, I am assuming
Windows. The old dBase format is currently being used under the name "xbase."
There are many libraries that conform to this file format and offer the
type of
access which you wish to have. On top of that, there are ODBC drivers (in UNIX
and Windows, btw) for these xBase files.

Acucobol runtime, currently is present in around 650 different platform
(HW/SW)
so we can run the same programs in different environment. We use Linux
and WNT/W2K as server and W9x/WME as client. Use the xBase format
isn't a good choice when we have a several Gb of data, this is why I'm
thinking
to the PostgreSQL. The current Acucobol's "flat file" isn't adequate to manage
such large files, we need a way to see that files as relational DB.

Just out of curiosity, why is the xbase format not a good choice?

You write acucobol extensions using some generic xbase access layer, and use
the ODBC xbase driver for applications like Access and Excel.

You'll have to sort out all the issues like concurrent access, and stuff like
that, but it should come pretty close to what you want to do.

I think you are missing the point. A good xbase library will allow you to perform
"joins" on data across tables. It doesn't have a SQL syntax, but that does not
mean you can't code that way.

Also, ODBC drivers for xbase use SQL format queries.

I have already done some work. I've implemented an extension of the generic
Acucobol FS layer that talk with a PostgreSQL. This lowlevel layer
translate each
FS primitive in a query. The acucobol's record is translated in attribute
(and vice versa)
using a XFD file (eXtended Fields Description) which describe each record's
field and
that is cached in memory. This file is generated by the acucobol compiler
for each used file.
With this informations I'm able to perform a complete query to the
PostgreSQL, the
returned tuples will be translated in the expected "flat record" and
finally returned to the
runtime for its work.

I know, the cobol use a different philosophy than a relational DB but this
my work
show that this two different world could talk. Also, I know the difficulty
of the major lowlevel
integration. I need some doc/indication/files-to-read of PostgreSQL
lowlevel routines
to see if this different world could have a major integration bypassing the
"overhead" of
the query accessing directly to the DB.

I think, strongly, you are going down the wrong track. Take a look at:

http://sourceforge.net/projects/xdb/
http://www.unixodbc.org/

At 15.38 07/06/01 -0400, mlw wrote:

Roberto Fichera wrote:

I am reticent to admit that I have done a little COBOL and the

interface for

data paradigms is very good for a dBase like package. If you can live
without a
client/server interface ala Postgres, and can live with a file based

access

methodology, then what you want is doable.

The main problem is that we want see our data as relational database
and we want continue to use the current programs. Currently we have
some customer that have their company archive large around 50Gb.

In what format is this data?

It's a VISION format, a proprietary variant of a B*Tree format.

I'm not aware what platform you wish to run your program, I am assuming
Windows. The old dBase format is currently being used under the name

"xbase."

There are many libraries that conform to this file format and offer the
type of
access which you wish to have. On top of that, there are ODBC drivers

(in UNIX

and Windows, btw) for these xBase files.

Acucobol runtime, currently is present in around 650 different platform
(HW/SW)
so we can run the same programs in different environment. We use Linux
and WNT/W2K as server and W9x/WME as client. Use the xBase format
isn't a good choice when we have a several Gb of data, this is why I'm
thinking
to the PostgreSQL. The current Acucobol's "flat file" isn't adequate to

manage

such large files, we need a way to see that files as relational DB.

Just out of curiosity, why is the xbase format not a good choice?

Because the DBF format didn't perform well on a large archive of serveral
million
records. It's don't reuse the deleted records, have a limitation of 255
fields and
the char() field can be max 255 char in length and the max record size is 4k.

You write acucobol extensions using some generic xbase access layer,

and use

the ODBC xbase driver for applications like Access and Excel.

You'll have to sort out all the issues like concurrent access, and

stuff like

that, but it should come pretty close to what you want to do.

I think you are missing the point. A good xbase library will allow you to
perform
"joins" on data across tables. It doesn't have a SQL syntax, but that does not
mean you can't code that way.

Also, ODBC drivers for xbase use SQL format queries.

I know.

I have already done some work. I've implemented an extension of the generic
Acucobol FS layer that talk with a PostgreSQL. This lowlevel layer
translate each
FS primitive in a query. The acucobol's record is translated in attribute
(and vice versa)
using a XFD file (eXtended Fields Description) which describe each record's
field and
that is cached in memory. This file is generated by the acucobol compiler
for each used file.
With this informations I'm able to perform a complete query to the
PostgreSQL, the
returned tuples will be translated in the expected "flat record" and
finally returned to the
runtime for its work.

I know, the cobol use a different philosophy than a relational DB but this
my work
show that this two different world could talk. Also, I know the difficulty
of the major lowlevel
integration. I need some doc/indication/files-to-read of PostgreSQL
lowlevel routines
to see if this different world could have a major integration bypassing the
"overhead" of
the query accessing directly to the DB.

I think, strongly, you are going down the wrong track.

Could be ;-)! But I want try to do some work before abandon this
solution.

Take a look at:

http://sourceforge.net/projects/xdb/

I've take a look on it, and I'd the confirm of the aspected limitations.
Another
problem of this library is that we can't perform concurrent locks with other
applications different than xdb library. So it's unusable for me.

http://www.unixodbc.org/

I know it, it's good link!

Roberto Fichera.