I just had a hard time trying to make sense from the ESQL/C online
documentation about explicit and implicit connections.
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.
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@[EMAIL PROTECTED]
syntax to connect to a
database
and server, you cannot select another database until you disconnect
your
current connection."
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. 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
@[EMAIL PROTECTED]
or //server syntax."
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 '@[EMAIL PROTECTED]
' */
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.
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 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.
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?
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.
|
