Ways to access RDB database

[Esko Ilola]

Good morning !
I need Your professional opinion on interfacing RDB (V7,1).
So far I have been using SqlServer and the API it offers. However, that API seems to be very sluggish and AFAIK it is also very complex if compared to direct database access like the one that the SQL uses.
My application (several binary CGI programs) is running in the same physical machine as the database so using SqlServer feels like a lot of wasted resources.
I would like to have an API which can do dynamic or pseudo-dynamic database interface directly to the database engine without going through the SqlServer.

The SqlServer was my first try on OpenVMS/RDB environment so I don't know much about the other options around yet. You could save a lot of my time just by pointing to right direction.

I have done a lot of embedded SQL in my daily job and from experience I know that it may be a quick but really dirty way.

One option might be creating an embedded SQL program and then digging into the intermediate source code (precompiled code) looking on how it works. I did this once with Ignres esqlc and the resulting API was outstanding. The bad news here is that nobody quarantees that the API is still functional after next patch...

The attached program (source code) can be used @ http://furpile.com/cgi-bin/RandomQuote

I attach this so You can get some idea ...

Any Ideas for bypassing SqlServer ?

[Dan Herron]

Hi Esko,
I strongly suggest you submit your question to the JCC Listserver which is an open forum for Rdb DBA's and programmers world wide. Go to WWW.JCC.COM and look for instructions on joining the Oracle Rdb Listserver.

[Richard J Maher]

Hi Esko,

Rdb does have a very useful dynamic SQL interface. Which probably adheres to some standard using SQLDA and SQLDA2 structures and lovely meta-verbs like Prepare and Describe. I'd like to be of more use and provide a pointer to the relevant manual but I'm upset that I can't see the replies to my TCP/IP suggestion :-(

Anyway look at the SQL Reference manual for starters at "Prepare" and "Descripe" and "Execute (immediate)" and in the Appendices for SQLDA(2) (B.2??, D?)

Good luck

Cheers Richard

[Craig A Berry]

You might have a look at Andreas Stiller's DBD::RDB module, an RDB-specific driver for the Perl DBI:

http://search.cpan.org/~astiller/dbd_rdb-1_16/

[Richard J Maher]

Hi Esko,

Here's a pointer that may be of some use: -

http://www.oracle.com/technology/products/rdb/rdb_doc_index.html#rdbrel71

There's a downloadable PDF version of The Guide to SQL Programming. Chpt 11 is (used to be?) the part about dynamic SQL.

A couple of quick questions though: -

1) When you say "SqlServer" do you mean the SQL/Services API for Rdb or something else?

2) Do you really need "Dynamic" SQL?

Cheers Richard

[Chris Barratt]

While I have no direct experience with the SQL Services API, I have heard many good things about it from people who have used it. On the PC platform, many people have used it when they find ODBC too cumbersome or lacking performance. We mostly use ODBC from PCs to access SQL Services (the ODBC driver itself calls the SQL Services API), and rarely find any performance problems related to SQL Services itself.

So one possibility for you might be to find out where the actual performance problem is...perhaps you need to set up your database servers differently (eg. ensure you have some pre-started).

If you wish to continue with the CGI-style app, then other options are to use SQL module language compiled into your program, use SQL module language to do dynamic SQL (as has already ben suggested) or use one of the SQLPRE compilers to have embeedded SQL. In each case, you will need to ensure that the username that your web server runs under has access to the database (whereas with SQL Services API, I don't think you do).

If you wanted to change tack with the web app completely, there are native JDBC drivers available from Oracle, and there is a PHP API available from http://www.ktl.fi/distribution/. There are also commercial options available from Attunity.

I would second the previous reply and suggest the JCC Rdb List server as a great place to get support for these kind of questions. If you have a support contract for Rdb, you really should log a call about the perofrmance problems.

Cheers,
Chris

[Jean-François Piéronne · Accepted solution]

Python include also a Rdb native interface module.
Some examples are online.

http://vmspython.dyndns.org/

A Web demonstration accessing the famous mf_personnel database is also available.

The demonstration is not complete (degree is currently
missing) but fairly functional, it just a couple of days of work.
This need a recent browser with javascript enabled because this is also
a demonstrator for the ajax technology.


http://vmspython.dyndns.org/personnel/

use demo/demo to login

Jean-François

[Esko Ilola]

Holy Python !

First of all: Thanks for many constructive and informative replies.

As it seems there actually is no API library available to access the RDB. However - the Python sources revealed another way to go around this problem.

Using SQLMOD looks promising but it is still somewhat cumbersome as compared to a simple - no-mess API. However - I'm going to give it a try. I already did some test compilations and it seems like there would be a 10 to 20 fold speed improvement.

The reason why SQL/Services is so sluggish is simply the machine: An AS2000 can do just that much. Also - the SQL/Services tend to fire up new processes which takes some time with authentication and all.

And Yes, I need dynamic SQL as I use a C++ class library having this database engine in the first floor - it means that it receives each and every database request from the overlaying interfaces. This type of solution makes application programming a trip-in-the-park. However - it also makes the database interface routines somewhat hard to design and code.

I still do have a question.
¤#%&"# - this seems to be a problem in each and every database except MySql.

I think most web developers need a way to limit the result set in a proper way. This means that You can tell how many rows is to be fetched and most importantly also where to start with. I looked into the "HELP SQL SELECT" and found out that there really is a "LIMIT TO xxx ROW" available. For my surprise there was also a "SKIP" part but I was not able to make it work. Seems like the SQL and the documentation are slightly out-of-sync here. Without the start-point option the LIMIT TO is next to useless.

FYI: In Oracle (9i) this is done with three SQL statements inside each other ... and it still doesn't work in certain situations. This isn't something I have invented - this was a "solution" from Oracle support...


Thanks in advance !

P.S. You can test the software @ http://furpile.com/stock/ListSockt <---that is the old code at the moment.

[Chris Barratt]

Hi Esko,

In regard to SQL Services - you can get around it creating new processes by setting up a number of processes that you wish to remain running at all times. For example, you may allow 20 servers and require that there are at least 10 pre-started at all times. I guess it depends on the volatility of your workload.

I would note, also that use of "database executors" instead of "generic" executors can remove the step of attaching to the db.

I'm assuming you are at a fairly recent version too.

In regards to your cursor question, I think what you are alluding to is the ability to stop reading a cursor after some predefined number of records are returned, commit the transaction and then start a new trans later and continue on from where you were ?

Aside from the data consistency issues in this, Rdb does have a feature called "HOLD cursors", which allows you to do something like this. I seem to remember that there is some limitation when using these via SQL Services, but I may be wrong. I looked in to using them here, to replace our current method, but decided they didn't quite achieve what we currently did.

The way we achieve this is to create our own contexts, and store within this context the original query parameters, along with all the values in the last record read that uniquely point to the record within the query. The SQL used then not only includes bits to select the required records, but also to ensure we start selecting from where we left off. IT can get a bit hairy at times, but mostly works pretty well.

Cheers,
Chris