ColdFusion and mySQL

There is something wrong with my syntax. I just converted my Access database over to mySQL. Whats wrong with the <cfqueryparam>? I get "?" question marks inserted into the database when I execute the updated code. Before: --------- <cfquery name="update_alias" DATASOURCE="Alias"> Insert into alias (env_var, destination) VALUES ('#FORM.env_var#', '#FORM.destination#') </cfquery> After: ------- <cfquery name="update_alias" DATASOURCE="Alias"> Insert into alias (env_var, destination) VALUES ('<cfqueryparam value="#FORM.env_var#" cfsqltype="CF_SQL_LONGVARCHAR">', '<cfqueryparam value="#FORM.destination#" cfsqltype="CF_SQL_LONGVARCHAR">') </cfquery> --------------------------------------------------- Colonel Nathan R. Jessop Commanding Officer Marine Ground Forces Guatanamo Bay, Cuba ---------------------------------------------------

Do you actually get the "?" inserted into the DB or does the Query debug show a ?.... ? There is something wrong with my syntax. I just converted my Access database over to mySQL. Whats wrong with the <cfqueryparam>? I get "?" question marks inserted into the database when I execute the updated code. Before: --------- <cfquery name="update_alias" DATASOURCE="Alias"> Insert into alias (env_var, destination) VALUES ('#FORM.env_var#', '#FORM.destination#') </cfquery> After: ------- <cfquery name="update_alias" DATASOURCE="Alias"> Insert into alias (env_var, destination) VALUES ('<cfqueryparam value="#FORM.env_var#" cfsqltype="CF_SQL_LONGVARCHAR">', '<cfqueryparam value="#FORM.destination#" cfsqltype="CF_SQL_LONGVARCHAR">') </cfquery> --------------------------------------------------- Colonel Nathan R. Jessop Commanding Officer Marine Ground Forces Guatanamo Bay, Cuba ---------------------------------------------------

I actually get the "?" inserted into my two fields in the database. ----- Excess quoted text cut - see Original Post for more -----

hehe..... Get rid of the single quotes around your <cfqueryparam> it should NOT be: ('<cfqueryparam value="#FORM.env_var#" cfsqltype="CF_SQL_LONGVARCHAR">', it SHOULD be (<cfqueryparam value="#FORM.env_var#" cfsqltype="CF_SQL_LONGVARCHAR">, You are inserting the placeholder. -Mark There is something wrong with my syntax. I just converted my Access database over to mySQL. Whats wrong with the <cfqueryparam>? I get "?" question marks inserted into the database when I execute the updated code. Before: --------- <cfquery name="update_alias" DATASOURCE="Alias"> Insert into alias (env_var, destination) VALUES ('#FORM.env_var#', '#FORM.destination#') </cfquery> After: ------- <cfquery name="update_alias" DATASOURCE="Alias"> Insert into alias (env_var, destination) VALUES ('<cfqueryparam value="#FORM.env_var#" cfsqltype="CF_SQL_LONGVARCHAR">', '<cfqueryparam value="#FORM.destination#" cfsqltype="CF_SQL_LONGVARCHAR">') </cfquery> --------------------------------------------------- Colonel Nathan R. Jessop Commanding Officer Marine Ground Forces Guatanamo Bay, Cuba ---------------------------------------------------

I did and I still get "?" inserted in the database? <cfquery name="update_alias" DATASOURCE="Alias"> UPDATE alias SET env_var = <cfqueryparam value="#FORM.env_var#" cfsqltype="CF_SQL_LONGVARCHAR">, destination = <cfqueryparam value="#FORM.destination#" cfsqltype="CF_SQL_LONGVARCHAR"> WHERE alias_ID = #FORM.alias_ID# </cfquery> ----- Excess quoted text cut - see Original Post for more -----

Hmmmm..... Are you sure. Delete them all manually and try again. I did and I still get "?" inserted in the database? <cfquery name="update_alias" DATASOURCE="Alias"> UPDATE alias SET env_var = <cfqueryparam value="#FORM.env_var#" cfsqltype="CF_SQL_LONGVARCHAR">, destination = <cfqueryparam value="#FORM.destination#" cfsqltype="CF_SQL_LONGVARCHAR"> WHERE alias_ID = #FORM.alias_ID# </cfquery> > hehe..... Get rid of the single quotes around your <cfqueryparam> it should ----- Excess quoted text cut - see Original Post for more ----- database ----- Excess quoted text cut - see Original Post for more ----- cfsqltype="CF_SQL_LONGVARCHAR">') ----- Excess quoted text cut - see Original Post for more -----

Yes I'm sure. I removed the "env_var = <cfqueryparam value="#FORM.env_var#" cfsqltype="CF_SQL_LONGVARCHAR">" and put it back the way I had it before to "env_var = '#FORM.env_var#'. Now this field gets inserted properly but the "destination = <cfqueryparam value="#FORM.destination#" cfsqltype="CF_SQL_LONGVARCHAR">" gets inserted as a "?" It has to be the syntax of the SQL statement? I'm using the <cfqueryparam on another database and it works great. ----- Excess quoted text cut - see Original Post for more -----

Shoot - well, I'm out of ideas <g>. Yes I'm sure. I removed the "env_var = <cfqueryparam value="#FORM.env_var#" cfsqltype="CF_SQL_LONGVARCHAR">" and put it back the way I had it before to "env_var = '#FORM.env_var#'. Now this field gets inserted properly but the "destination = <cfqueryparam value="#FORM.destination#" cfsqltype="CF_SQL_LONGVARCHAR">" gets inserted as a "?" It has to be the syntax of the SQL statement? I'm using the <cfqueryparam on another database and it works great. ----- Excess quoted text cut - see Original Post for more -----

Quoting FlashGuy <flashmx@rogers.com>: ----- Excess quoted text cut - see Original Post for more ----- Can you show us the query debug output? And you really should convert that last pass through variable into a cfqueryparam too. Jochem

On Tue,  3 Dec 2002 17:00:38 +0100, Jochem van Dieten wrote: ----- Excess quoted text cut - see Original Post for more ----- Even if the variable won't ever contain a "\" like the destination field? > > Jochem

FlashGuy wrote: ----- Excess quoted text cut - see Original Post for more ----- Yes, always (see list archives and MM knowledgebase for the reasons). And if you still have the problem, send the debug output. Jochem

do any of the variables contain "\" There is something wrong with my syntax. I just converted my Access database over to mySQL. Whats wrong with the <cfqueryparam>? I get "?" question marks inserted into the database when I execute the updated code. Before: --------- <cfquery name="update_alias" DATASOURCE="Alias"> Insert into alias (env_var, destination) VALUES ('#FORM.env_var#', '#FORM.destination#') </cfquery> After: ------- <cfquery name="update_alias" DATASOURCE="Alias"> Insert into alias (env_var, destination) VALUES ('<cfqueryparam value="#FORM.env_var#" cfsqltype="CF_SQL_LONGVARCHAR">', '<cfqueryparam value="#FORM.destination#" cfsqltype="CF_SQL_LONGVARCHAR">') </cfquery> --------------------------------------------------- Colonel Nathan R. Jessop Commanding Officer Marine Ground Forces Guatanamo Bay, Cuba ---------------------------------------------------

Yes, the "FORM.destination" but thats the reason for the "<cfqueryparam value="#FORM.destination#" cfsqltype="CF_SQL_LONGVARCHAR">" Yet the "?" are being inserted. ----- Excess quoted text cut - see Original Post for more -----

Hi, I don't know how your access database was built but it seams your problem come from encoding. Access is Unicode compatible and MySql doesn't I use the same couple of techno - Flash - CFMX - MySql I have had to insert each special with urlFncodedFormat function Best regards Eric -----Message d'origine----- De : Robertson-Ravo, Neil (RX) [mailto:Neil.Robertson-Ravo@csd.reedexpo.com] Envoyé : mardi 3 décembre 2002 16:26 À : CF-Talk Objet : RE: CF and mySQL do any of the variables contain "\" There is something wrong with my syntax. I just converted my Access database over to mySQL. Whats wrong with the <cfqueryparam>? I get "?" question marks inserted into the database when I execute the updated code. Before: --------- <cfquery name="update_alias" DATASOURCE="Alias"> Insert into alias (env_var, destination) VALUES ('#FORM.env_var#', '#FORM.destination#') </cfquery> After: ------- <cfquery name="update_alias" DATASOURCE="Alias"> Insert into alias (env_var, destination) VALUES ('<cfqueryparam value="#FORM.env_var#" cfsqltype="CF_SQL_LONGVARCHAR">', '<cfqueryparam value="#FORM.destination#" cfsqltype="CF_SQL_LONGVARCHAR">') </cfquery> --------------------------------------------------- Colonel Nathan R. Jessop Commanding Officer Marine Ground Forces Guatanamo Bay, Cuba ---------------------------------------------------

doesn't mySQL use \ for something internally? I had a problem with it ages back.... can't remember what it was though! Yes, the "FORM.destination" but thats the reason for the "<cfqueryparam value="#FORM.destination#" cfsqltype="CF_SQL_LONGVARCHAR">" Yet the "?" are being inserted. > do any of the variables contain "\" > There is something wrong with my syntax. I just converted my Access database ----- Excess quoted text cut - see Original Post for more ----- cfsqltype="CF_SQL_LONGVARCHAR">') ----- Excess quoted text cut - see Original Post for more -----

Correct. Thats the reason for the <cfqueryparam...> ----- Excess quoted text cut - see Original Post for more -----

Got it. Thanks ----- Excess quoted text cut - see Original Post for more -----

I don't use mySQL but I remember someone saying \ is an escape character... <cfqeuryparam> will allow you to put \ in a varchar field in mySQL however. It's in one of the threads in the cf-talk archive. ----- Excess quoted text cut - see Original Post for more -----

erm, what version of  mySQL? Correct. Thats the reason for the <cfqueryparam...> ----- Excess quoted text cut - see Original Post for more -----

what was it? Got it. Thanks ----- Excess quoted text cut - see Original Post for more ----- ages ----- Excess quoted text cut - see Original Post for more ----- cfsqltype="CF_SQL_LONGVARCHAR">', ----- Excess quoted text cut - see Original Post for more -----