Honestly I'm still a little confused here... I still don't think that
PerserveSingleQuotes() does what you suggest.
As far as I understand it (which may turn out to be not at
all) CF will automatically escape single quotes in variables placed
inside a cfquery.
I've never found it to be 100%
Isn't it impossible to do this when you're using Access?
I know you shouldn't be using Access, etc., but I've never
been able to get multiple SQL statements to work in Access.
Is there a trick I'm missing, or is this just something
else to live with and remind me I shouldn't be using
Not to argue with Ben, but I don't think that
PreserveSingleQuotes() is
the answer... That function prevents CF from automatically esacping
single quotes (in this case you WANT CF to escape them for you).
I think you're confused a little with what CF terms as escaping the
single quotes
It
Philip Arnold - ASP wrote:
I think you're confused a little with what CF terms as escaping the
single quotes
It converts all single quote types to their real single quote versions
for SQL (used in WHERE and such), normally it won't always put the
apostrophies in for the INSERT and UPDATE,
It is not the only way, and it isn't a guaranteed way unless
you rewrite your replace statements for each RDBMS. Each RDBMS
uses different escape characters/sequences and delimiters.
Use cfqueryparam.
This is of course impossible if you're building your SQL in a string
first and then running
Philip Arnold - ASP wrote:
It is not the only way, and it isn't a guaranteed way unless
you rewrite your replace statements for each RDBMS. Each RDBMS
uses different escape characters/sequences and delimiters.
Use cfqueryparam.
This is of course impossible if you're building your SQL in a
Not being able to use cfqueryparam (or cfprocparam, which is also a
typed variable) is a very good reason not to do that.
Apart from that, using TSQL already confines you to using one
specific RDBMS, so the exception I mentioned applies. Although I
still doubt that somebody not being a
Philip Arnold - ASP wrote:
Saying that, if you're building your systems to work on your hardware,
I'd expect you to know the oddities of the RDMS
I would expect that too. But experience tells me not many people know
how their RDBMS handles the not everyday functionality.
If you want true
!! sql wierdness ERROR message
Philip Arnold - ASP wrote:
Saying that, if you're building your systems to work on your hardware,
I'd expect you to know the oddities of the RDMS
I would expect that too. But experience tells me not many people know
how their RDBMS handles the not everyday
I would expect that too. But experience tells me not many people know
how their RDBMS handles the not everyday functionality.
~sigh~ This is the kind of thing it seems we have to put up with these
days... Too many web designers trying to become web developers and making a
right hash of it!
but if you want to do 50 small inserts in a row,
in a row? Do you mean updates of one row of a table or subsequent
inserts into a table?
I mean inserting 50 rows into a table or multiple tables in the same
database
sending those requests as separate CFQuery tags is pretty
much insane -
Stephen Moretti wrote:
sending those requests as separate CFQuery tags is pretty much insane
- building one big TSQL statement with one hit to the database server
is a much better solution
Why would that be insane?
Because that's 50 connect/deconnect to the database via your DB driver
Robertson-Ravo, Neil (REC) wrote:
your table really should not be 50 fields in size I think a
normalisation could be implemented.. before you reach the SQL Server
limit on row size.
We are doing an exercise of the mind on a fictitious database schema
here (right Philip?). As such, it is
your table really should not be 50 fields in size I think a
normalisation could be implemented.. before you reach the SQL
Server limit on row size.
I'm not talking about 50 fields, but 50 rows
Philip Arnold
Technical Director
Certified ColdFusion Developer
ASP Multimedia Limited
:-) yep, I noticed after your last few posts! :-)
-Original Message-
From: Philip Arnold - ASP [mailto:pra;aspmedia.co.uk]
Sent: 04 November 2002 13:23
To: CF-Talk
Subject: RE: HELP!! sql wierdness ERROR message
your table really should not be 50 fields in size I think
shove this code at the top of your SP:
SET QUOTED_IDENTIFIER OFF
What happens?
-Original Message-
From: Jochem van Dieten [mailto:jochemd;oli.tudelft.nl]
Sent: 04 November 2002 13:23
To: CF-Talk
Subject: Re: HELP!! sql wierdness ERROR message
Robertson-Ravo, Neil (REC) wrote:
your
;oli.tudelft.nl]
Sent: 04 November 2002 13:20
To: CF-Talk
Subject: Re: HELP!! sql wierdness ERROR message
Stephen Moretti wrote:
sending those requests as separate CFQuery tags is pretty much insane
- building one big TSQL statement with one hit to the database server
is a much better solution
Dieten [mailto:jochemd;oli.tudelft.nl]
Sent: 04 November 2002 13:20
To: CF-Talk
Subject: Re: HELP!! sql wierdness ERROR message
Stephen Moretti wrote:
sending those requests as separate CFQuery tags is pretty much insane
- building one big TSQL statement with one hit to the database server
Because that's 50 connect/deconnect to the database via your DB driver
Connection pooling?
Doesn't make any difference As Phil says, you're blitzing your DB
connection with 50 queries in a very short period.
as opposed to one connect/disconnect and letting your DB do the job it
was
Philip Arnold - ASP wrote:
sending those requests as separate CFQuery tags is pretty
much insane - building one big TSQL statement with one hit
to the database server
is a much better solution
Why would that be insane?
Basically as you're blitzing the connector, whether it's ODBC or
Andy Ewings wrote:
As far as connection pooling goes - I think there is a
misunderstanding as to what it does. The most lengthy process
(relatively) in going to a db and getting data is creating the
connection itself. Connection pooling just holds on to connections
for reuse - you
Robertson-Ravo, Neil (REC) wrote:
I would say that you would not find that much difference in peformace
on basic Select, Insert, Update or Deletes (the standard CF/SQL
Combos) but for more complex Joins etc you may find certain
degredation in peformance..
Exactly. However, we are
undoubtedly save you time
-Original Message-
From: Jochem van Dieten [mailto:jochemd;oli.tudelft.nl]
Sent: 04 November 2002 13:41
To: CF-Talk
Subject: Re: HELP!! sql wierdness ERROR message
Andy Ewings wrote:
As far as connection pooling goes - I think there is a
misunderstanding
;oli.tudelft.nl]
Sent: 04 November 2002 13:42
To: CF-Talk
Subject: Re: HELP!! sql wierdness ERROR message
Robertson-Ravo, Neil (REC) wrote:
I would say that you would not find that much difference in peformace
on basic Select, Insert, Update or Deletes (the standard CF/SQL
Combos) but for more
- Original Message -
From: Jochem van Dieten [EMAIL PROTECTED]
--snip--
And besides, I would still implement it without depending on TSQL:
cfquery datasource=test
cfloop from=1 to=3 index=i
INSERT INTO test (testID)
VALUEs (cfqueryparam cfsqltype=cf_sql_integer
Andy Ewings wrote:
I'm not sure I follow this response?
I was taking a wild stab because I had the same with your message :)
What I was saying/implying is that yes connection pooling would
prevent you from having to reconnect from scratch each time but you
would still be having to execute
Gyrus wrote:
Isn't it impossible to do this when you're using Access?
I don't know. But Access was not really what I had in mind.
It would probably have been better to say that it works with any
database that adheres to standard X, Y, and Z. Or that it is less
database dependent.
Jochem
Hold on a second guys, all Jason said was
-This is representative some of the data being inserted. This was a NASTY
550 page file maker pro data base that I'm converting. Some fields are empty
some are huge like this one.
So I assume with these looped queries all he is trying to achieve is
depending on what
datatype your field is set to.
-Original Message-
From: Richard Meredith-Hardy [mailto:rmh;flymicro.com]
Sent: Monday, November 04, 2002 9:32 AM
To: CF-Talk
Subject: Re: HELP!! sql wierdness ERROR message
Hold on a second guys, all Jason said
-Original Message-
From: Philip Arnold - ASP [mailto:pra;aspmedia.co.uk]
Sent: Monday, November 04, 2002 5:46 AM
To: CF-Talk
Subject: RE: HELP!! sql wierdness ERROR message
Not to argue with Ben, but I don't think that
PreserveSingleQuotes() is
the answer... That function
Of course a lot of this is academic anyway. Jochem is
correct of course that much (perhaps all of this) of
this is now handled (better) with CFQUERYPARAM.
The PreserveSingleQuotes() function was one of the
original functions (I believe all the way back to
version 1.0).
It made sense when
S. Isaac Dealey wrote:
cfqueryparam can't perform this function, because a sql variable can't
be used as a portion of this query -- without first trapping it in
something within the db like sp_executesql mystatement or
exec(mystatement) ... So queryparam doesn't change the purpose or
Of course a lot of this is academic anyway. Jochem is correct of
course that much (perhaps all of this) of this is now handled
(better) with CFQUERYPARAM. The PreserveSingleQuotes() function was
one of the original functions (I believe all the way back to
version 1.0).
It made
S. Isaac Dealey wrote:
cfqueryparam can't perform this function, because a sql
variable can't
be used as a portion of this query -- without first
trapping it in
something within the db like sp_executesql mystatement
or
exec(mystatement) ... So queryparam doesn't change the
purpose or
jason kufner wrote:
cfquery name=newAuthorBio datasource=#application.dsn#
INSERT into authorBio (authorBio_authorId, authorBioDetail)
VALUES (#session.authorPkey#, '#bookArray[43]#')
/cfquery
cfquery name=newAuthorBio datasource=#application.dsn#
INSERT into authorBio
Thank you so much for the fast response:
Mr. Forta it's always gratifying to see your name in this news group, the Cf
community is alive and thriving!!
Here is the code in question:
cfquery name=newAuthorBio datasource=#application.dsn#
INSERT into authorBio
I still don't think it is the parentheses. Try changing:
'#bookArray[43]#'
into
'#PreserveSingleQuotes(bookArray[43])#'
--- Ben
-Original Message-
From: jason kufner [mailto:jasonkufner;rio.com]
Sent: Friday, November 02, 2001 11:24 PM
To: CF-Talk
Subject: HELP!! sql wierdness
: HELP!! sql wierdness ERROR message
Thank you so much for the fast response:
Mr. Forta it's always gratifying to see your name in this news group,
the Cf community is alive and thriving!!
Here is the code in question:
cfquery name=newAuthorBio datasource=#application.dsn
Thank you so much for the fast response:
Mr. Forta it's always gratifying to see your name in this
news group, the Cf community is alive and thriving!!
Not to argue with Ben, but I don't think that PreserveSingleQuotes() is
the answer... That function prevents CF from automatically
39 matches
Mail list logo
