Creating a table in different database from within 4GL
Joerg Spilker needed to create a table in another database from a 4GL client without losing his session context; CREATE TABLE db:table failed (error 956), DATABASE switching lost context and SPL could not PREPARE. Art Kagel advised a second named connection WITH CONCURRENT TRANSACTIONS, then SET CONNECTION TO DEFAULT. This worked in ESQL/C; Jonathan Leffler said I4GL 7.30 supports CONNECT directly.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
Hello, i´m in a database session opened by a 4GL Client. The client has set some options on the open database (LOCK MODE, ISOLATION LEVEL) and prepared some statements. Now the client is doing his work and i must create a table in a different database. CREATE TABLE database:table () doesn´t work (error 956 i think, Can´t create object on a remote database). If i switch the database with a DATABASE statement, i can create the table, but i´m loosing my current context in my default database when switching back. Creating the table by a stored procedure on the remote database also doesn´t work because SPL doesn´t let me use PREPARE and EXECUTE (i had to use PREPARE and EXECUTE because the table name is variable). Is there any other possibility i forgot ?? Greetings, Joerg Spilker
Joerg Spilker wrote:
>
> Hello,
>
> i´m in a database session opened by a 4GL Client. The client has set
> some options on the open database (LOCK MODE, ISOLATION LEVEL) and
> prepared some statements.
>
> Now the client is doing his work and i must create a table in a
> different database. CREATE TABLE database:table () doesn´t work (error
> 956 i think, Can´t create object on a remote database). If i switch
> the database with a DATABASE statement, i can create the table, but
> i´m loosing my current context in my default database when switching
> back. Creating the table by a stored procedure on the remote database
> also doesn´t work because SPL doesn´t let me use PREPARE and EXECUTE
> (i had to use PREPARE and EXECUTE because the table name is variable).
>
> Is there any other possibility i forgot ??
Open a second, named, connection, with the WITH CONCURRENT
TRANSACTIONS clause included (so your existing cursors are not
destroyed and open transactions rolled back) and create the table
then switch back to the other, possibly the DEFAULT, connection and
go on with what you were doing.
CONNECT TO "otherdatabase" AS remote WITH CONCURRENT TRANSACTIONS;
create table other_db_table (.....);SET CONNECTION TO DEFAULT;
Or something very close to that. You MIGHT want to RTFM on this
feature to make sure you get it right.
Art S. Kagel
"Art S. Kagel" wrote: Hello Art, > Or something very close to that. You MIGHT want to RTFM on this > feature to make sure you get it right. thank you for your help. Using CONNECT from within an ESQL/C included source is working. Is there any why CONNECT isn´t allowed directly from 4GL code? Greetings, Joerg
Joerg Spilker wrote: > "Art S. Kagel" wrote: > > Or something very close to that. You MIGHT want to RTFM on this > > feature to make sure you get it right. > > thank you for your help. Using CONNECT from within an ESQL/C included > source is working. Is there any why CONNECT isn´t allowed directly > from 4GL code? If you're using I4GL 7.30, CONNECT is supported directly (also D4GL v3.00). If you're using an older version, it might be time to upgrade. -- Yours, Jonathan Leffler (Jonathan.Leffler@Informix.com) #include <disclaimer.h> Guardian of DBD::Informix v1.00.PC1 -- http://www.perl.com/CPAN "I don't suffer from insanity; I enjoy every minute of it!"