[thelist] SQL: insert into mutiple tables help

rudy r937 at interlog.com
Tue Apr 10 20:09:22 CDT 2001

> for a database that supports transactions, it's as simple
> as performing the insert and the select max(id) within the
> same transaction block...
>  <cfquery ...>
>    INSERT ...
>  </cfquery>
>  <cfquery ...>
>    SELECT Max(id)
>  </cfquery>

hi jeff

i'm going to have to defer to your better knowledge of cf, but doesn't this
transaction take effect within the web server?  doesn't this mean the web
server is effectively single-threading for the duration of those blocks?

not that this is calamitous, in fact it does seem like a good solution
especially where you cannot utilize a database transaction block, which of
course would be better than a middleware transaction block...

> this is much more reliable than querying for some
> piece of text you just inserted and grabbing the id
> from the resulting recordset

oh, quibble

if both methods are reliable, i don't see how one of them can be more
reliable than the other   ;op

> a constraint is only half the solution to the problem of unique
> data in a particular column.  the constraint itself will cause your
> middleware to throw an error if you insert duplicate data.
> however, you must also build constraint-type checks into your
> middleware logic to prevent this error from occurring at all.

i *think* i know what you're saying

are you preventing a duplicate from occurring at all by first querying the
database to see if the value exists, and if it doesn't, then go ahead and
do the insert?

here's another approach -- just go ahead and do the insert, and trap the
database error that you get back if it turns out to have been an attempted
insert of a duplicate

let's say the dupe error rate is 5 per hundred, then in the first instance
you are doing 100 queries and 95 inserts for 195 database operations, but
in the second case, you are doing 100 operations max, except that 5 of them
are just error messages and 95 of them worked

note that in either case, when a duplicate is attempted, the message to
the user is the same -- "sorry, charlie, you can't do that, we already gots
one of those"

> at this point, the only reason for a constraint on that
> column in the database is to prevent people with direct access
> to the database from entering duplicate data.

actually both middleware access and direct database access are protected by
the same database constraint, and i wouldn't execute extra code in the
middleware if i don't have to, i mean, maybe it's me, but why?  all you
should be doing in the middleware is elegant error handling, not additional
querying (of course, this line of reasoning assumes you can trap database
errors in the middleware)

or maybe i didn't understand what you meant by "also build constraint-type
checks into your middleware logic"

> another thing to look out for with using constraints as a pk of
> some sort is that some databases treat the case of a string
> differently.  so, a user that enters JoeUser at email.com would
> be different than joeuser at email.com

good point, but we'd all know to use a string fold function for that,


More information about the thelist mailing list