Re: ESQL/C documentation nightmare

On Sat, May 3, 2008 at 10:22 PM, Gerardo Santana
<gerardo.santana@xxxxxxxxx> wrote:
I just had a hard time trying to make sense from the ESQL/C online
documentation about explicit and implicit connections.

Oh dear.

I wanted to create a database using ESQL/C while having multiple
connections.

I started reading this:
http://publib.boulder.ibm.com/infocenter/idshelp/v111/topic/com.ibm.esqlc.doc/esqlc233.htm

"Important:
It is recommended that you use the CONNECT, DISCONNECT, and SET
CONNECTION connection statements for new applications of Version 6.0
and later. For pre-6.0 versions, the SQL database statements (such as
DATABASE, START DATABASE, and CLOSE DATABASE) remain valid for
compatibility with earlier versions."

Ok, I should stick with CONNECT, DISCONNECT and SET CONNECTION. If I
want to create a database will use EXECUTE IMMEDIATE to avoid CREATE
DATABASE.

Connecting to an existing database is different from creating a new
one, but the basic advice here is OK, as far as it goes.

Then I run the following:

EXEC SQL connect to 'test1';
EXEC SQL execute immediate 'create database test2';

and get:

"-759 Cannot use database commands in an explicit database
connection.

If you use the CONNECT TO database@server syntax to connect to a database
and server, you cannot select another database until you disconnect your
current connection."

The '@server' portion is optional and when omitted, the value of
$INFORMIXSERVER is used instead.

Hmm, I'm not exactly using that syntax (I'm not specifying a dbserver)
and was not expecting to select another database. By experimentation,
I found that CREATE DATABASE and EXECUTE IMMEDIATE 'create
database...' fail identically.

Yes. When you have connected (via CONNECT) to a database -- as
distinct from a server -- then you cannot do any of the database
operations (or, at least none of the database operations that select a
different database; that means DATABASE, CREATE DATABASE, START
DATABASE (for SE), ROLLFORWARD DATABASE (for SE again), or CLOSE
DATABASE. You also cannot use DROP DATABASE. Oddly, you can use
RENAME DATABASE (but not the current database, and when you do rename
a database, you acquire some sort of - presumably shared - lock on the
database).

With SE, there isn't a server to connect to via '@server', so my
comments should be interpreted as applying to IDS rather than SE.

Black JL: sqlcmd -e 'create database aleph in dbspace'
Black JL: sqlcmd -d stores
SQL[2637]: drop database aleph;
SQL -759: Cannot use database commands in an explicit database connection.
SQLSTATE: IX000 at /dev/stdin:1
SQL[2638]: rename database aleph to beth;
SQL[2639]: rename database stores to contrapunctus;
SQL -359: Cannot drop or rename current database.
SQLSTATE: IX000 at /dev/stdin:3
SQL[2641]: connect to '@black_17';
SQL[2642]: drop database beth;
SQL -425: Database is currently opened by another user.
ISAM -107: ISAM error: record is locked.
SQLSTATE: IX000 at /dev/stdin:6
SQL[2643]: info connections;
stores|stores||OnLine|logged|non-ANSI|with concurrent transactions|idle|
@black_17|@black_17||OnLine|unlogged|non-ANSI|no concurrent
transactions|current|
SQL[2644]: set connection 'stores';
SQL[2645]: info tables where tabid = (select max(tabid) from systables
where tabtype = 'T');
jleffler|audit_days|T|557
SQL[2647]: disconnect current;
SQL[2648]: set connection '@black_17';
SQL[2649]: drop database beth;
SQL[2650]: q;
Black JL:

[I think the lock held by the rename database statement is probably a buglet.]


Then I thought that the following paragraph should apply to both.

http://publib.boulder.ibm.com/infocenter/idshelp/v111/topic/com.ibm.esqlc.doc/esqlc233.htm

"Important:
Use of the DATABASE, CREATE DATABASE, START DATABASE, CLOSE DATABASE,
and DROP DATABASE statements is still valid with an explicit
connection. However, in this context, refer only to databases that are
local to the current connection in these statements; do not use the
@server or //server syntax."

I think that information is plain misleading. It would be better
written along the lines of:

You can only use the DATABASE, CREATE DATABASE, (START DATABASE,
ROLLFORWARD DATABASE), CLOSE DATABASE, and DROP DATABASE statements if
either (a) no connection has yet been made to the server, or (b) the
current connection was made directly to the server and not to a
database using CONNECT TO '@server'. In case (a), an implicit
connection to the server is established as if via the CONNECT TO
'@server' notation.


I'm not specifying a server! Anyways, I keep experimenting and found
that the following works:

EXEC SQL CONNECT TO 'test1';
EXEC SQL CONNECT TO DEFAULT; /* or to '@dbserver' */
EXEC SQL EXECUTE IMMEDIATE 'create database test2';
EXEC SQL SET CONNECTION 'test1';

Ok, the trick is to connect to a server without opening a database
before EXECUTE IMMEDIATE.

Yes - or do not have any connection established.

I also found the following.

http://publib.boulder.ibm.com/infocenter/idshelp/v111/topic/com.ibm.sqls.doc/sqls167.htm

"After you create an explicit connection, you cannot use any database
statement to create implicit connections until after you close the
explicit connection."

How come? I just did use a database statement!, after creating an
explicit connection!, without disconnecting!

I suppose that explanation could add "or unless you create a new
DEFAULT connection or a new connection to '@server'", or words to that
effect. I was not able to connect to IDS using the '//black_17'
notation -- something I'd not tried recently (say, during this
millennium).

The point is, when a given connection is created explicitly by
connecting to a specific database (as distinct from a server), you
cannot do almost any operation that involves the keyword DATABASE --
RENAME DATABASE seems to be the exception. So, the statement above
might be better amended to:

"After you create an explicit connection to a database, you cannot use
any database
statement (other than RENAME DATABASE) with that as the current
connection." (or 'using that connection' instead of 'with that ...').


I didn't expect to create an implicit connection though. I didn't want
to make one.

After more experimentation I got the following error.

"-758 Cannot implicitly reconnect to the new server server_name.

If you use the CONNECT TO statement to connect to a server, you cannot
implicitly reconnect to another server through one of the DATABASE
statements (DATABASE, START DATABASE, and so on). You must switch to
it with the SET CONNECTION statement."

Ah, now I understand. The online help was referring to implicitly
connecting to a different dbserver by specifying one as part of the
name of the database. Otherwise, I can use database statements and
switch connections with SET CONNECTION. I don't have to disconnect.

I'm not sure this summary is wholly accurate. To do DATABASE operations:
* You don't have to disconnect any connections
(but you cannot use a connection that connected directly to a database).
* If there is no current connection, the DATABASE operations create


I've not checked the definition of 'explicit connection' vs 'implicit
connection'. However, from my perspective, an explicit connection is
one that is initiated by a CONNECT statement that specifies a database
- not DEFAULT nor '@server'. An implicit connection is one that is
created by a DATABASE statement. I'd like to group DEFAULT and
'@server' with implicit connections, but that needs some
refinement...hmmm, what about: (1) 'direct connection' - a connection
via CONNECT directly to a database, (2) 'indirect connection' - a
connection via CONNECT to DEFAULT or to '@server', and (3) 'implicit
connection' - a connection created via database statement. We could
even use 'direct explicit connection' and 'indirect explicit
connection'.

At the risk of being answered with a "yes, you are the only one,
<insert your preferred insult here>" I'm asking: am I the only one
that finds this documentation confusing?

Looked at afresh - something that is hard to do after many years - I
think you have raised some valid points that might perhaps be best
addressed by removing material before adding new, clearer material.

P.S. I still don't understand why do I need the trick of connecting
with a server without opening a database to be able to create a
database without losing my previous connections.

You can look at it various ways. "Because that's the way it is
implemented". "Because you cannot change the database to which a
direct explicit connection is bound". "Because it is not clear
whether the connection name should change if you changed the database
to which it is connected".

--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@xxxxxxxxxxxxx, jleffler@xxxxxxxxxx
Guardian of DBD::Informix v2008.0229 -- http://dbi.perl.org/
"Blessed are we who can laugh at ourselves, for we shall never cease
to be amused."
NB: Please do not use this email for correspondence.
I don't necessarily read it every week, even.
.

Relevant Pages

  • Re: ADO Connection Timeout
    ... so what happens when a connection failure forces one station to revert ... to a local database? ... Further, you *will* have contention issues, Jet does not support record ... to the central server, but you are willing to live with periods where it ...
    (microsoft.public.data.ado)
  • Re: ADO Connection Timeout
    ... When the first test is run, the results are stored in the central database. ... to the central server, but you are willing to live with periods where it ... i.e. a local database or even a text file. ... to function until the connection can be restored to the server. ...
    (microsoft.public.data.ado)
  • Re: ADO Connection Timeout
    ... to the central server, but you are willing to live with periods where it ... i.e. a local database or even a text file. ... to function until the connection can be restored to the server. ...
    (microsoft.public.data.ado)
  • Re: ADO Connection Timeout
    ... much rather write stored procedures and use server side cursors. ... local database, it is vital that I get the information to a central server. ... Once the connection is restored, ...
    (microsoft.public.data.ado)
  • Re: Communicating over the internet
    ... > That way you provide one manner in which to open the database, ... > can keep the connection open and then when someone wants to ... > server as the database and the soap dll connecting with a TCP socket. ... The name of the running program might help. ...
    (microsoft.public.vb.general.discussion)