>> have you tried (longshot) <cfqueryparam ...> as alias it's a good idea, Elliot, but sadly it didn't work in this case.
>> its possibly a informix odbc support issue I suspect you are right. I threw this post up there to see if under what conditions it works/didn't work. When I get a chance I'll try it with the typeIV JDBC drivers to the same db. If it works then, well, I'll just wait for the migration process of this legacy app (which is still being added to!) IF OTHER PEOPLE... ... have had experience with "INSERT INTO...SELECT FROM" and cfqueryparams (either success like Scott T or Elliot R or otherwise like me) feel free to chime in. If I get a resolution, I'll post it up to give some other poor bastard a chance... thanx all barry.b -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Elliot Russo Sent: Wednesday, 29 September 2004 4:46 PM To: CFAussie Mailing List Subject: [cfaussie] Re: cfqueryparam woes: doesn't work with INSERT INTO...SELECT FROM? have you tried (longshot) <cfqueryparam ...> as alias just a thought - i think this works in oracle/mssql (i thought i have done it in the past) , its possibly a informix odbc support issue "Barry Beattie" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] 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/ --- 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/
