Thread: RE: [INTERFACES] ecpg and getting just assigned serial number
It's would be pretty difficult to return the unique key, because that is
defined by the user, and what if I had a table into which were placed values
from two separate sequences (not great design, but possible), or a compound
unique key, or multiple unique keys? The things that's necessary here is
for the sqlca struct to contain the oid of the newly inserted row after the
insertion. Of course, then there is a problem if more than one row is
inserted. What does Informix do then?
But why can't you just write a database function that performs the insert,
and returns the value, and then call the function using embedded sql. The
function can be written in plsql, and accept as parameters all the values
that need to go into the new record. This is far better db design.
The basic function looks like this (pseudocode):
BEGINget next sequence value into i;insert new record using parameters and i;if insert fails then return (0);else
return(i);
END
This has the added benefit of being able to add further business rules into
the function, which better encapsulates your process. Of course, this is
design dependent, but it's a method I've used quite successfully in the past
(although, admittedly, not in PG ;-)
MikeA
>> -----Original Message-----
>> From: Andreas Theofilu [mailto:theofilu@eunet.at]
>> Sent: Monday, September 13, 1999 2:11 PM
>> To: Michael Meskes
>> Cc: theofilu@eunet.at; pgsql-interfaces@postgreSQL.org
>> Subject: Re: [INTERFACES] ecpg and getting just assigned
>> serial number
>>
>>
>> Am Mo, 13 Sep 1999 schrieben Sie:
>> > On Sun, Sep 12, 1999 at 04:25:17PM +0200, Theofilu Andreas wrote:
>> > > I'm using 'ecpg' to develop my application, because it's
>> mostly compatible
>> > > to Informix's 'esqlc'. However. I have a table with a
>> field of type
>> > > 'serial'. Now I've no problem to insert any sentences
>> into this table, but
>> > > immediately after inserting a new sentence I need the
>> newly assigned number
>> > > to the serial field in the table. How can I access this number?
>> > > With Informix I got this number in a field of structure
>> 'sqlca'. With
>> > > PostgreSQL the same field in this structure exists but
>> contains always 0,
>> > > as documented.
>> >
>> > Where exactly does Informix return it? sqlca[?].
>>
>> Informix returns this number in 'sqlca.sqlerrd[1]'.
>> Currently this field
>> is not used by ecpg.
>>
>> > Also does anyone know whether the backend returns that
>> number somewhere?
>>
>> Now I know that the backend does not return this number
>> anywhere. Instead
>> you need two SQL commands to insert a sentence and get the
>> number. You can
>> do it with following commands:
>>
>> EXEC SQL nextval ('<sequence name>') into :variable;
>> EXEC SQL insert into <table> values (:variable, ...);
>>
>> Maybe ecpg can implement the first call behind the scenes
>> and put the new
>> serial number into 'sqlca.sqlerrd[1]'.
>> In Informix I have to write:
>>
>> EXEC SQL insert into <table> values (0, ...);
>>
>> The '0' increments the serial counter, who is a simple int4
>> field in a
>> system table, by one, inserts the sentence with new serial
>> number into the
>> table and returns the new number in 'sqlca.sqlerrd[1]'.
>> It would be nice to have this feature exactly the same way
>> with 'ecpg'.
>> This would make it more compatible, at least against
>> Informix. Don't know
>> about Oracle.
>>
>> BTW: When I insert a sentence in Informix and the serial
>> field is not 0,
>> but contains a unique number and this number is higher than
>> the current
>> value of the serial counter, the serial counter is set to
>> the manualy set
>> number. This is important if one inserts sentences from a file. For
>> example when I move data between two machines/databases.
>> With PostgreSQL
>> I've to set the counter into the sequence by hand, or it will not be
>> incremented.
>> --
>> Theofilu Andreas
>> http://members.eunet.at/theofilu
>>
>> -------------------------------------------------
>> Enjoy the science of Linux!
>> Genieße die Wissenschaft von Linux!
>> -------------------------------------------------
>>
>> ************
>>
Am Mo, 13 Sep 1999 schrieben Sie: > It's would be pretty difficult to return the unique key, because that is > defined by the user, and what if I had a table into which were placed values > from two separate sequences (not great design, but possible), or a compound > unique key, or multiple unique keys? The things that's necessary here is > for the sqlca struct to contain the oid of the newly inserted row after the > insertion. Of course, then there is a problem if more than one row is > inserted. What does Informix do then? Agree. It's difficult to do that with PostgreSQL. It works with Informix only because the type 'serial' is realy a seperate type and handled completely different. Beside this it's allowed to have only one field of type serial in a table. With SQL command 'insert' you can only insert one sentence at a time. To enter more than one sentence you must program a loop. So I can see no problem with the serial number. Beside this: The serial field of Informix is independant of any index. Therefore it doesn't matter how complicate an index may be. > But why can't you just write a database function that performs the insert, > and returns the value, and then call the function using embedded sql. The > function can be written in plsql, and accept as parameters all the values > that need to go into the new record. This is far better db design. I can write such a function, of course. But I will not break compatibility to Informix, because both databases, PostgreSQL _and_ Informix are supported in only a single source file. > The basic function looks like this (pseudocode): > > BEGIN > get next sequence value into i; > insert new record using parameters and i; > if insert fails then > return (0); > else > return (i); > END > > This has the added benefit of being able to add further business rules into > the function, which better encapsulates your process. Of course, this is > design dependent, but it's a method I've used quite successfully in the past > (although, admittedly, not in PG ;-) I've done exactly the same your example shows, but not in a seperate function. I simply added one SQL command to get the next number out of the sequence. This works well now and the extra line is ignored when compiling for Informix :-). -- Theofilu Andreas http://members.eunet.at/theofilu ------------------------------------------------- Enjoy the science of Linux! Genie�e die Wissenschaft von Linux! -------------------------------------------------
On 13-Sep-99 Andreas Theofilu wrote:
> I can write such a function, of course. But I will not break compatibility
> to Informix, because both databases, PostgreSQL _and_ Informix are
> supported in only a single source file.
I've tried to do this myself, and in the long run I think
you'll be better off planning on having two different versions of
embedded C, or at least, two different sets of functions that do
certain operations. Informix ESQL/C and ecpg have significantly
different syntax, especially regarding CURSORs and FETCHs. If you
keep things simple, and have some way of converting database names
between the two systems, you can undoubtedly do what you're trying
to do. Anything else and it gets really difficult. While Informix
supports EXEC SQL IFDEF, PostgreSQL does not appear to.
An alternative might be to have a single source file that
can be modified by a sed or awk script to conform to whatever
embedded SQL you're using at the moment. I considered this approach,
but didn't pursue it because I didn't have the time.
We had a discussion here about this subject in June. The
subject was "esql/c documentation". If you can't find it in the
archive, I can e-mail some of the more interesting posts in that
thread.
----------------------------------
Date: 13-Sep-99 Time: 10:55:47
Craig Orsinger (email: <orsingerc@epg-gw1.lewis.army.mil>)
Logicon RDA
Bldg. 8B28 "Just another megalomaniac with ideas above his
6th & F Streets station. The Universe is full of them."
Ft. Lewis, WA 98433 - The Doctor
----------------------------------
RE[2]: [INTERFACES] ecpg and getting just assigned serial number
From
theofilu@eunet.at (Theofilu Andreas)
Date:
Craig Orsinger <orsingerc@epg-gw1.lewis.army.mil> wrote: > > On 13-Sep-99 Andreas Theofilu wrote: > > I can write such a function, of course. But I will not break > compatibility > > to Informix, because both databases, PostgreSQL _and_ Informix are > > supported in only a single source file. > > I've tried to do this myself, and in the long run I think > you'll be better off planning on having two different versions of > embedded C, or at least, two different sets of functions that do > certain operations. Informix ESQL/C and ecpg have significantly > different syntax, especially regarding CURSORs and FETCHs. If you > keep things simple, and have some way of converting database names > between the two systems, you can undoubtedly do what you're trying > to do. Anything else and it gets really difficult. While Informix > supports EXEC SQL IFDEF, PostgreSQL does not appear to. > It is not always easy to do that and the two are different, but not completely. However. I use a special preprocessor (lwpp [look at freshmeat for it]) to create an embedded C file for PostgreSQL 'ecpg' or Informix 'esqlc'. I think this is the better way. Having two source trees would mean to do everything twice and if I forget something I have two incompatible programs. > An alternative might be to have a single source file that > can be modified by a sed or awk script to conform to whatever > embedded SQL you're using at the moment. I considered this approach, > but didn't pursue it because I didn't have the time. > look above. > We had a discussion here about this subject in June. The > subject was "esql/c documentation". If you can't find it in the > archive, I can e-mail some of the more interesting posts in that > thread. > Thanks for that tip, will search for it. -- Theofilu Andreas http://members.eunet.at/theofilu ------------------------------------------------- Enjoy the science of Linux! Genieße die Wissenschaft von Linux! -------------------------------------------------
Hi, I'm thrilled to see that there is some kind of discussion running in the C area on the exact equivalent of my very recent JDBC problem. I insert a row into a table where one field is a SERIAL. This SERIAL, of course, is automatically assigned by PostgreSQL. How do I get this number immeadiately after inserting the row? When I issue the command with psql I get back some kind of number which presumably is the OID. Getting just this one would be fine too because I need it for referencing. Any hints are greatly appreciated. TIA and cheers, Chris -- Dr. Christoph Steinbeck (http://www.ice.mpg.de/~stein) MPI of Chemical Ecology, Tatzendpromenade 1a, 07745 Jena, Germany Tel: +49(0)3641 643644 - Mobile: +49(0)177 8236510 - Fax: +49(0)3641 643665 What is man but that lofty spirit - that sense of enterprise. ... Kirk, "I, Mudd," stardate 4513.3..
On Mon, 13 Sep 1999, Christoph Steinbeck wrote: > Hi, > > I'm thrilled to see that there is some kind of discussion running in the > C area on the exact equivalent of my very recent JDBC problem. > I insert a row into a table where one field is a SERIAL. This SERIAL, of > course, is automatically assigned by PostgreSQL. How do I get this > number immeadiately after inserting the row? When I issue the command > with psql I get back some kind of number which presumably is the OID. > Getting just this one would be fine too because I need it for > referencing. There's not a standard way, but we do have an extension method that does the job: getResultStatusString() This method was added as someone else asked for it in the distant past. ie: Statement st = conn.createStatement(); .. your code that either runs st.executeQuery() or st.executeUpdate() .. then for JDK1.1.x: String returncode = ((postgresql.jdbc1.Statement)st).getResultStatusString(); or for Java2:String returncode = ((postgresql.jdbc2.Statement)st).getResultStatusString(); PS: Sorry, you have to state the driver version (jdbc1 or jdbc2), although this will be fixed in 6.6, and will become postgresql.Statement. Peter -- Peter T Mount peter@retep.org.uk Main Homepage: http://www.retep.org.uk PostgreSQL JDBC Faq: http://www.retep.org.uk/postgresJava PDF Generator: http://www.retep.org.uk/pdf
On Mon, Sep 13, 1999 at 11:49:03AM -0700, Craig Orsinger wrote: > to do. Anything else and it gets really difficult. While Informix > supports EXEC SQL IFDEF, PostgreSQL does not appear to. Not yet, but it may make a nice addition. Michael -- Michael Meskes | Go SF 49ers! Th.-Heuss-Str. 61, D-41812 Erkelenz | Go Rhein Fire! Tel.: (+49) 2431/72651 | Use Debian GNU/Linux! Email: Michael@Fam-Meskes.De | Use PostgreSQL!
On 13-Sep-99 Theofilu Andreas wrote:
>> to do. Anything else and it gets really difficult. While Informix
>> supports EXEC SQL IFDEF, PostgreSQL does not appear to.
>>
> It is not always easy to do that and the two are different, but not
> completely. However. I use a special preprocessor (lwpp [look at freshmeat
> for it]) to create an embedded C file for PostgreSQL 'ecpg' or Informix
> 'esqlc'. I think this is the better way. Having two source trees would mean
> to do everything twice and if I forget something I have two incompatible
> programs.
I downloaded this program and tried it out. It definitely
has possibilities. I especially like its flexible prefix feature,
which should make it possible to use for source files in just
about any language. You could use this, for instance, to pre-process
SQL scripts for different DBMSs. I've been doing stuff like this
using sed scripts in makefiles, and I can tell you I'd much rather
do it this way.
Thanks for the tip.
----------------------------------
Date: 15-Sep-99 Time: 11:10:52
Craig Orsinger (email: <orsingerc@epg-gw1.lewis.army.mil>)
Logicon RDA
Bldg. 8B28 "Just another megalomaniac with ideas above his
6th & F Streets station. The Universe is full of them."
Ft. Lewis, WA 98433 - The Doctor
----------------------------------
At 21:19 +0200 on 13/09/1999, Christoph Steinbeck wrote:
> I'm thrilled to see that there is some kind of discussion running in the
> C area on the exact equivalent of my very recent JDBC problem.
> I insert a row into a table where one field is a SERIAL. This SERIAL, of
> course, is automatically assigned by PostgreSQL. How do I get this
> number immeadiately after inserting the row? When I issue the command
> with psql I get back some kind of number which presumably is the OID.
> Getting just this one would be fine too because I need it for
> referencing.
I have answered this question several times already on the SQL list. It has
nothing to do with Java or any other interface you are using (except,
maybe, the question about how to get the OID).
Let's make it short.
1) Don't use OIDs for reference. It will only make life harder.
2) If you want to use the OID just to retrieve that last row, make sure that the OID column is indexed, otherwise you
arein for sequential searching. See, I told you not to use it...
3) As for the serial field. "serial" is a shorthand notation for creating an PostgreSQL sequence, and taking the next
numberfrom that sequence. You can see the name of the sequence that was created with a quick \ds in psql.
Now, after inserting a row, use SELECT currval('sequence_name'); The number returned is the number assigned to the
serialnumber in the last INSERT statement of the current session.
Herouth
--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma