[thelist] Postgre and Cold Fusion

Joshua Olson joshua at waetech.com
Thu Aug 12 08:35:08 CDT 2004


> -----Original Message-----
> From: Ken Schaefer
> Sent: Wednesday, August 11, 2004 10:17 PM
>
> I've got a couple of question here - sorry for hijacking your thread!

No problem.

> You say you use a trigger in SQL Server, even though you are using
> something like an Identity column (I assume that sequeunce is similar
> to identity). Why not use @@IDENTITY or Scope_Identity()?

With SQL Server, I actually do use @@identity.  The trigger looks something
like this:

CREATE TRIGGER user_insert on [user]
FOR INSERT
AS SELECT @@identity AS id

Then, when I execute an INSERT query through CF (or through ASP for that
matter) I automatically get back the identity as a column called id.
Example:

<cfquery name="insert_user" datasource="whatever">
  INSERT INTO [user] (...) VALUES (...)
</cfquery>

<cfoutput>The ID: #insert_user.id#</cfoutput>

I never have to worry about locking or transactions using this technique.

My initial issue was that I couldn't find a way to do a similar approach
with PostgreSQL.

> When using TDS to communicate with SQL Server, OLEDB or SqlClient or
> whatever upper layer you are using issues a reset which resets the
> session (even though the connection is still open) when the object is
> closed in your upper layers, which is why @@IDENTITY is "safe" even
> though the physical connections are never closed, but returned to the
> connection polol - doesn't whatever CF uses to connect to PostgreSQL
> have something similar? (I assume it's ODBC or something)

PostgreSQL is indeed using ODBC.  But, it doesn't seem that the session is
reset by default.  Wrapping the call in cftransaction tags does however seem
to reset the session.  Since it also puts Cold Fusion into single threaded
mode, it also ensures that no other thread can grab the sequence value.

> Thanks

No problem.

<><><><><><><><><><>
Joshua Olson
Web Application Engineer
WAE Tech Inc.
http://www.waetech.com/service_areas/
706.210.0168




More information about the thelist mailing list