the story so far:
I've been happily using cfqueryparam for ages - except now. for me the
damn things won't work if they are used with values in a insert
into...select from query.
Yes, nice Mr Scott Thornton was able to show it works for him although
it doesn't for me. (query below) Remove all the cfqueryparams and the
query doesn't error.
anyone else having grief with cfqueryparam with "INSERT INTO...SELECT
FROM"?
It'll give me a hope in working out why.
I've tried:
* using all values in the SELECT part with cfqueryparams
* using only one - any one, even a simple char
bottom line: if the value for the insert is an ad-hoc one using
cfqueryparam, then it'll throw an error.
db: informix 9.4, driver: ODBC socket, CFVers: 6,1,0,83762 or
6,1,0,hf53566_61
any suggestions, experiences, etc, are most welcome
thanx
barry.b
INSERT INTO purchhead(
AUTHORISE_CODE, CMPY_CODE, COM1_TEXT, COM2_TEXT
, CONV_QTY, CURR_CODE
, DEL_ADDR1_TEXT, DEL_ADDR2_TEXT, DEL_ADDR3_TEXT, DEL_ADDR4_TEXT
, DEL_COUNTRY_TEXT, DEL_NAME_TEXT, DUE_DATE
, ENTER_CODE, ENTRY_DATE, ORDER_NUM, order_date
, PERIOD_NUM, PRINTED_FLAG
, PUR1_FLG, PUR2_FLG, PUR3_FLG, PUR4_TEXT, PUR5_TEXT, PUR6_TEXT
, SALESPERSON_TEXT, STATUS_IND
, TAX_CODE
, TERM_CODE
, TYPE_IND, VEND_CODE
, WARE_CODE, YEAR_NUM
)
SELECT
AUTHORISE_CODE, CMPY_CODE, COM1_TEXT, COM2_TEXT
, 1 , "#trim(qVendor.CURR_CODE)#"
, DEL_ADDR1_TEXT, DEL_ADDR2_TEXT, DEL_ADDR3_TEXT, DEL_ADDR4_TEXT
, DEL_COUNTRY_TEXT, DEL_NAME_TEXT, DUE_DATE
, ENTRY_CODE, ENTRY_DATE, ORDER_NUM
, #now()#, #month(now())#, "N"
, PUR1_FLG, PUR2_FLG, PUR3_FLG, PUR4_TEXT, PUR5_TEXT, PUR6_TEXT
, SALESPERSON_TEXT, "O"
, "#trim(qVendor.TAX_CODE)#"
, "#trim(qVendor.TERM_CODE)#"
, "3", VEND_CODE
<!--- , <cfqueryparam cfsqltype="CF_SQL_CHAR" value="3" />, VEND_CODE
--->
, WARE_CODE, #year(now())#
FROM
REQHEAD
WHERE
order_num = <cfqueryparam cfsqltype="CF_SQL_INTEGER"
value="#qNext_po_num.order_num#">
AND cmpy_code = <cfqueryparam cfsqltype="CF_SQL_CHAR"
value="#this.cmpy_code#">
-----Original Message-----
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Barry
Beattie
Sent: Friday, 17 September 2004 10:59 AM
To: CFAussie Mailing List
Subject: [cfaussie] Re: cfqueryparam woes: doesn't work with INSERT
INTO...SELECT FROM?
>> Just finished writing a similair query.. it worked okay (against MS
SQL).. sorry.
bugger!
thanx heaps Scott. At least you proved it *does* work (at least against
any *decent* databases). the problem is wholy at my end (groan...)
cheers mate - I owe you a beer.
barry.b
-----Original Message-----
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Scott
Thornton
Sent: Friday, 17 September 2004 10:10 AM
To: CFAussie Mailing List
Subject: [cfaussie] Re: cfqueryparam woes: doesn't work with INSERT
INTO...SELECT FROM?
Hello again.
Just finished writing a similair query.. it worked okay (against MS
SQL).. sorry.
<CFQUERY datasource="#DSN#" name="INSERT_CLAIM">
INSERT INTO SB_ITEM_CLAIM
(
SB_CLAIM_TYPE_CODE,
SB_INVOICE_ITEM_ID,
SB_AMOUNT_CLAIMED,
SB_CLAIM_DATE,
SB_UPDATED_BY,
SB_UPDATE_DATE
)
SELECT
'CLV',
SB_INVOICE_ITEM_ID,
SB_INVOICE_COST,
GETDATE(),
<cfqueryparam cfsqltype="cf_sql_varchar"
value="#SESSION.SB_LOGIN_CODE#">,
GETDATE()
FROM
SB_INVOICE_ITEM
WHERE
SB_VOUCHER_ID = <cfqueryparam cfsqltype="cf_sql_numeric"
value="#FORM.SB_VOUCHER_ID#" >
</CFQUERY>
Scott Thornton, Programmer
Application Development
Information Services and Telecommunications
Hunter-New England Area Health Service
Phone JHH +61 2 49214193 RNH +61 2 49236078
Fax +61 2 49214191
[EMAIL PROTECTED]
>>> [EMAIL PROTECTED] 17/09/2004 9:27:11 am >>>
thanx for your reply, Scott
>> Is there an error message?
yeah a vague "syntax error" - nothing more. take out the cfqueryparams
in the SELECT and it works fine
>> Also, wouldn't the expected speed gain be lost as the params
ARGUMENTS.auth_code and qVendor.VEND_CODE are not parameterised as
well?
good point. Idealy, all those values within the SELECT should use
cfqueryparam as well as the later WHERE clause - that's what I was
aiming for. But the problem seems to be that cfqueryparam can't be
used
*anywhere* in the select part, only the WHERE.
I use cfqueryparam not only for speed reasons but to format data
acceptable for the drivers to except. eg: if you use
cfsqltype="CF_SQL_DATE" and a "yyyy-mm-dd" date format, you don't have
to stuff around with createODBCdate() etc. Works brilliantly - except
in
this case.
>> Last question.. is there any reason why you don't use database
functions
you're assuming a particular db vendor. this is an Informix db but
could
be changed to mySQL ot MSSQL within the life of the product.
While all those db's use the same syntax for month() and year(),
Informix doesn't support getDate(). It's a case of sticking with the
lowest common denominator... cf date functions
thanx
barry.b
---
You are currently subscribed to cfaussie as: [EMAIL PROTECTED]
To unsubscribe send a blank email to [EMAIL PROTECTED]
Aussie Macromedia Developers: http://lists.daemon.com.au/
