Thread: Re: @@IDENTITY (Was: Access - ODBC - serial problem)
> For reasons unfathomable to modern man, Microsoft chose @@IDENTITY to mean, > not the current user, as I or you or any sane person would expect, but to hold > the incremented value of an autoincrementing column fed by an insert. Another > triumph in orthogonally named things from Microsoft. > > Anyway, what the original conversation was about was getting back the auto > increment value from pgsql via ODBC with access on top. The problem being > that postgresql doesn't have a simple "what was the autoincrement I just put > in" but requires the user to either use the system catalogs to look up the > sequence name and then use currval on it, or something like a plpgsql function > to insert your data. ----------- Without a way of getting the autoincrement from the ODBC driver, there will always be cases, when adding data to a table through a linked table, where the data that appears onscreen is not the one that has been added to the table. And this until you requery the whole table. - After an insert, Microsft ODBC driver issues a "SELECT @@IDENTITY". - After an insert, Postgresql ODBC driver reads the data based on the values that have been inserted. There are cases were these values already existed in the table before the insert, and so the autoincrement fetched may not be correct. Is there anything that can be done at the driver level?
I agree with the strangeness of Microsoft's name, but the capability is
useful. What I do in a case like this is use the transaction capability. If
you do
Begin transaction;
<Do the insert>;
Select max(oid_column_name) from table;
Commit work;
You are guaranteed to get the correct ID back in any system that supports
transactions. Because all "real" databases support caching, it's
computationally inexpensive, unless you have a trigger that will insert
another record behind your back.
Ed
----- Original Message -----
From: "Philippe Lang" <philippe.lang@attiksystem.ch>
To: <pgsql-odbc@postgresql.org>
Cc: "scott.marlowe" <scott.marlowe@ihs.com>
Sent: Saturday, April 10, 2004 1:59 AM
Subject: Re: [ODBC] @@IDENTITY (Was: Access - ODBC - serial problem)
> For reasons unfathomable to modern man, Microsoft chose @@IDENTITY to
mean,
> not the current user, as I or you or any sane person would expect, but
to hold
> the incremented value of an autoincrementing column fed by an insert.
Another
> triumph in orthogonally named things from Microsoft.
>
> Anyway, what the original conversation was about was getting back the
auto
> increment value from pgsql via ODBC with access on top. The problem
being
> that postgresql doesn't have a simple "what was the autoincrement I
just put
> in" but requires the user to either use the system catalogs to look up
the
> sequence name and then use currval on it, or something like a plpgsql
function
> to insert your data.
-----------
Without a way of getting the autoincrement from the ODBC driver, there
will always be cases, when adding data to a table through a linked
table, where the data that appears onscreen is not the one that has been
added to the table. And this until you requery the whole table.
- After an insert, Microsft ODBC driver issues a "SELECT @@IDENTITY".
- After an insert, Postgresql ODBC driver reads the data based on the
values that have been inserted. There are cases were these values
already existed in the table before the insert, and so the autoincrement
fetched may not be correct.
Is there anything that can be done at the driver level?
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
On Sat, 10 Apr 2004, Ed Brown wrote: > I agree with the strangeness of Microsoft's name, but the capability is > useful. What I do in a case like this is use the transaction capability. If > you do > > Begin transaction; > <Do the insert>; > Select max(oid_column_name) from table; > Commit work; > > You are guaranteed to get the correct ID back in any system that supports > transactions. Because all "real" databases support caching, it's > computationally inexpensive, unless you have a trigger that will insert > another record behind your back. In Postgresql, this is NOT I/O inexpensive, but results in a seq scan of the whole table to find the max value. select oid_column_name from table order by oid_column_name desc limit 1 is computationally and I/O inexpensive if the table has an index on that field. However, I think you have to be in serializable transaction mode for that code to be gauranteed not to get the wrong data. But I'm not certain on that one.
"scott.marlowe" <scott.marlowe@ihs.com> writes:
> [ use ORDER BY ... LIMIT instead of MAX ]
> However, I think you have to be in serializable transaction mode for that
> code to be gauranteed not to get the wrong data. But I'm not certain on
> that one.
Yes, you need serializable mode with *either* method to ensure you see
your own row, and not one inserted later by another transaction that
managed to commit before you. The ORDER BY trick is just to get around
Postgres' inability to connect MAX() to indexes; it doesn't affect the
transactional semantics at all.
regards, tom lane
I'm still getting this message at every ODBC connection. I think that there
might be a problem. I am having issues with ODBC connections being terminated
without a disconnect issued. This isn't due to script error or to the script
terminating. PostgreSQL's log reads:
LOG: could not receive data from client: Connection reset by peer
LOG: unexpected EOF on client connection
I would love to track down the reason for the disconnects.
Any assistance in this matter would be greatly appreciated. Thank you!
conn=48183040, PGAPI_DriverConnect(
in)='DRIVER={PostgreSQL};UID=adduser;PWD=xxxxx;DATABASE=xxxxx;SERVER=xxx.xxx.xxx.xxx;PORT=5432;READONLY=0;PROTOCOL=6.4;FAKEOIDINDEX=1;SHOWOIDCOLUMN=1;ROWVERSIONING=0;SHOWSYSTEMTABLES=0;CONNSETTINGS=;',
fDriverCompletion=0
Global Options: Version='07.03.0200', fetch=100, socket=4096, unknown_sizes=0,
max_varchar_size=254, max_longvarchar_size=8190
disable_optimizer=1, ksqo=1, unique_index=1, use_declarefetch=0
text_as_longvarchar=1, unknowns_as_longvarchar=0,
bools_as_char=1 NAMEDATALEN=64
extra_systable_prefixes='dd_;', conn_settings=''
conn_encoding='OTHER'
conn=48183040, query=' '
conn=48183040, query='select version()'
[ fetched 1 rows ]
[ PostgreSQL version string = 'PostgreSQL 7.4.2 on i686-pc-linux-gnu,
compiled by GCC gcc (GCC) 3.3.2 20031218 (Gentoo Linux 3.3.2-r5,
propolice-3.3-7)' ]
[ PostgreSQL version number = '7.4' ]
conn=48183040, query='set DateStyle to 'ISO''
conn=48183040, query='set geqo to 'OFF''
conn=48183040, query='set extra_float_digits to 2'
conn=48183040, query='select oid from pg_type where typname='lo''
[ fetched 0 rows ]
conn=48183040, query='select pg_client_encoding()'
[ fetched 1 rows ]
[ Client encoding = 'SQL_ASCII' (code = 0) ]
conn=48183040,
PGAPI_DriverConnect(out)='DRIVER={PostgreSQL};DATABASE=xxxxx;SERVER=xxx.xxx.xxx.xxx;PORT=5432;UID=adduser;PWD=xxxxx;ReadOnly=0;Protocol=6.4;FakeOidIndex=1;ShowOidColumn=1;RowVersioning=0;ShowSystemTables=0;ConnSettings=;Fetch=100;Socket=4096;UnknownSizes=0;MaxVarcharSize=254;MaxLongVarcharSize=8190;Debug=0;CommLog=1;Optimizer=1;Ksqo=1;UseDeclareFetch=0;TextAsLongVarchar=1;UnknownsAsLongVarchar=0;BoolsAsChar=1;Parse=0;CancelAsFreeStmt=0;ExtraSysTablePrefixes=dd_;;LFConversion=1;UpdatableCursors=1;DisallowPremature=0;TrueIsMinus1=0;BI=0;ByteaAsLongVarBinary=0;UseServerSidePrepare=0'
STATEMENT ERROR: func=PGAPI_SetDescField, desc='', errnum=11, errmsg='can't
SQLSetDescField for this parameter number'
------------------------------------------------------------
hdbc=48183040, stmt=48177616, result=0
manual_result=0, prepare=0, internal=0
bindings=0, bindings_allocated=0
parameters=0, parameters_allocated=0
statement_type=-2, statement='(NULL)'
stmt_with_params='(NULL)'
data_at_exec=-1, current_exec_param=-1, put_data=0
currTuple=-1, current_col=-1, lobj_fd=-1
maxRows=0, rowset_size=1, keyset_size=0, cursor_type=0,
scroll_concurrency=1
cursor_name=''
----------------QResult Info -------------------------------
CONN ERROR: func=PGAPI_SetDescField, desc='', errnum=0, errmsg='(NULL)'
------------------------------------------------------------
henv=48176920, conn=48183040, status=1, num_stmts=16
sock=48176976, stmts=48177048, lobj_type=-999
---------------- Socket Info -------------------------------
socket=1324, reverse=0, errornumber=0, errormsg='(NULL)'
buffer_in=48194040, buffer_out=48198144
buffer_filled_in=63, buffer_filled_out=0, buffer_read_in=63
__________________________________
Do you Yahoo!?
Yahoo! Tax Center - File online by April 15th
http://taxes.yahoo.com/filing.html