W3Schools Forum > cffunction

W3Schools Forum > Server Scripting > ColdFusion

vchris

Nov 27 2006, 03:47 PM

I have a function that takes 2 strings to create a query. Problem is the string I send to the function seems to have double single quotes when they should be single quotes.

CODE

<cffunction name="processDoc">
    <cfargument name="insertDocFields" type="string">
    <cfargument name="insertDocValues" type="string">
    <cfquery name="insertDoc" datasource="CAC">
        INSERT INTO GuidanceDocument(#insertDocFields#)
        VALUES (#insertDocValues#)
    </cfquery>
</cffunction>
<cfoutput>
    #processDoc('Title, Description_E, Description_F,
    Document_Language, Country_UID, Location, PRTR_Site,
    PRTR_Rep, PRTR_Rep_email, DTimeEntered, Source_org,
    UserLastModified',
    "'form.docTitle', 'form.descE', 'form.descF', 'form.language',
    'country', 'form.docLink', 'form.prtrCO', 'form.prtrRep',
    'form.prtrEmail', now(), 'form.docOrg', 'Session.UserName'")#
</cfoutput>

Output with query:

QUOTE

Error Executing Database Query.
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'form'.

INSERT INTO GuidanceDocument(Title, Description_E, Description_F, Document_Language, Country_UID, Location, PRTR_Site, PRTR_Rep, PRTR_Rep_email, DTimeEntered, Source_org, UserLastModified) VALUES (''form.docTitle'', ''form.descE'', ''form.descF'', ''form.language'', ''country'', ''form.docLink'', ''form.prtrCO'', ''form.prtrRep'', ''form.prtrEmail'', now(), ''form.docOrg'', ''Session.UserName'')

Output without query:

QUOTE

Fields: Title, Description_E, Description_F, Document_Language, Country_UID, Location, PRTR_Site, PRTR_Rep, PRTR_Rep_email, DTimeEntered, Source_org, UserLastModified
Values: 'form.docTitle', 'form.descE', 'form.descF', 'form.language', 'country', 'form.docLink', 'form.prtrCO', 'form.prtrRep', 'form.prtrEmail', now(), 'form.docOrg', 'Session.UserName'

EDIT: I discovered that I forgot to add # before and after each value. I still get the same double single quote problem. I also tried <cfset insertDocValues = Replace(insertDocValues,"''","'","ALL")> before my query and it still doesn't work. I guess the query adds the extra '.

Someone told me to use arrays instead since coldfusion will automatically escape all ' from a string. I have 2 loops going from 1 to 12 and every time I refresh it seems to take ages to reload. Any idea?

Issue pretty much resolved.

Skemcin

Nov 27 2006, 06:16 PM

having any issues still?

now sure if you are completely set here

btw - I hijacked your signature!

vchris

Nov 27 2006, 06:21 PM

I noticed for the signature lol

Here's what I got:

CODE

<cffunction name="processDoc">
        <cfargument name="insertDocFields" type="array">
        <cfargument name="insertDocValues" type="array">
INSERT INTO GuidanceDocument(
        <cfloop index="i" from="1" to="12">
            <cfoutput>#insertDocFields[i]#<cfif len(insertDocFields[i]) lt 12>, </cfif></cfoutput>
        </cfloop>)<br>VALUES (
        <cfloop index="i" from="1" to="12">
            <cfoutput>
                <cfif isNumeric(insertDocValues[i])>
                    #insertDocValues[i]#
                <cfelseif insertDocValues[i] eq 'now()'>
                    now()
                <cfelse>
                    '#insertDocValues[i]#'
                </cfif>
                <cfif arraylen(insertDocValues) lt 12>, </cfif>
            </cfoutput>
        </cfloop>)
</cffunction>
...
<cfset insertDocFields[1] = 'Title'>
                            <cfset insertDocFields[2] = 'Description_E'>
                            <cfset insertDocFields[3] = 'Description_F'>
                            <cfset insertDocFields[4] = 'Document_Language'>
                            <cfset insertDocFields[5] = 'Country_UID'>
                            <cfset insertDocFields[6] = 'Location'>
                            <cfset insertDocFields[7] = 'PRTR_Site'>
                            <cfset insertDocFields[8] = 'PRTR_Rep'>
                            <cfset insertDocFields[9] = 'PRTR_Rep_email'>
                            <cfset insertDocFields[10] = 'DTimeEntered'>
                            <cfset insertDocFields[11] = 'Source_org'>
                            <cfset insertDocFields[12] = 'UserLastModified'>

                            <cfset insertDocValues[1] = form.docTitle>
                            <cfset insertDocValues[2] = form.descE>
                            <cfset insertDocValues[3] = form.descF>
                            <cfset insertDocValues[4] = form.language>
                            <cfset insertDocValues[5] = country>
                            <cfset insertDocValues[6] = form.docLink>
                            <cfset insertDocValues[7] = form.prtrCO>
                            <cfset insertDocValues[8] = form.prtrRep>
                            <cfset insertDocValues[9] = form.prtrRepEmail>
                            <cfset insertDocValues[10] = 'now()'>
                            <cfset insertDocValues[11] = form.docOrg>
                            <cfset insertDocValues[12] = Session.UserName>
                            <cfoutput>#processDoc(insertDocFields,insertDocValues)#</cfoutput>

Output:

QUOTE

INSERT INTO GuidanceDocument( Title, Description_E Description_F Document_Language Country_UID, Location, PRTR_Site, PRTR_Rep, PRTR_Rep_email DTimeEntered Source_org, UserLastModified )
VALUES ( 'Test Doc' 'd' '' 'en' 5 'Test.com' 'd' 'V, Chris' 'dd@cc.com' now() 'ec' 'Chris' )

Notice some commas are missing. I'm probably gonna need a counter for this.

NOTE: I read somewhere when using <cflocation> it automatically does <cfabort> when redirecting. Is this true?

EDIT: Almost there. I get an error when trying to insert the data.

Error Executing Database Query.
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC SQL Server Driver][SQL Server]'now' is not a recognized built-in function name.

VALUES ( 'd' , 'd' , '' , 'en' , 5 , 'd' , 'd' , 'V, Chris' , 'dd@cc.com' , now() , 'ec' , 'Chris' )

I've used this function many times before. Why wouldn't it work?

EDIT: was the server. I had to use getdate(). resolved.

Skemcin

Nov 27 2006, 08:14 PM

QUOTE (vchris @ Nov 27 2006, 12:21 PM)

EDIT: was the server. I had to use getdate(). resolved.

lol - I've done that before.

all set then?

vchris

Nov 28 2006, 01:40 AM

Yeah kinda... What do you think about the code I gave you?

Skemcin

Nov 28 2006, 02:32 AM

didn't really give it a good look since I wasn't sure what to look for - I'll take a look and see what ya have . . .

vchris

Nov 28 2006, 01:45 PM

The thing is my form is like a tree. If you choose one option it goes deeper and deeper into the form with different form elements depending which way you go. That's why I need to create variables and send them to a function. This way I don't need 8 queries. I need 2 queries for this form. Should I simply create 2 other arrays like in the code above for my form fields or would that be the wrong way to do it?

Skemcin

Nov 28 2006, 02:14 PM

did you consider a single structured array. that is, create one structure and then dump each array into it. then you reuse that pretty easily. Even creating this as a session variable on a previous (less consuming) page may be an idea too. I do not know how fast your page loads, but you could run all the preparation on another page, set the structured array to a variable in the session scope, and then call it at will without having to run the process again. This could be especially helpful if the content is pretty static.

vchris

Nov 28 2006, 02:20 PM

Hmmm... I'm not sure I understand...

Skemcin

Nov 28 2006, 02:35 PM

ok, well I may not be 100% clear on the use, so lets not worry about the whole session variable thing, lets make sure the idea of a structured array is worth considering first.

It looks like you are trying to avoid querying the database 8 times for the sake that the only thing you might be changing is a where clause. True? If so, then maybe using structured array will save time. Essentially, you would put each of your arrays of information in a row of a structure. Then when you need the array you can pull it out as needed. Your intitial query just needs to be written with the right sorting or group by commands to set up your query results to be conducive to this approach.

But, I'm not quite seeing exactly what your code it doing cuz I've not had the time to give it a good look - sorry. Oh and I am not sure that <cflocation> performs a <cfabort>. I do know that in previous version of ColdFusion it would not retain session or cookie variables set on the page a <cflocation> was being used. I do believe the entire page gets process but only server side scripting is done - nothing is ever passed to the client - so that may be why it might seem like an abort, but it would only be if server side scripting was not executed.

I rarely use <cflocation> out of old habits caused by the point I made and because I always like to make sure I have the opportunity to communicate a redirect or action to the client if needed.

I typically do this instead of a <cflocation>

CODE

<cfoutput>
<script language="javascript" type="text/javascript">
//alert('optional Message.\nCLok OK to continue!');
document.location="#webroot#xxxxxx/xxxxxx.xxx";
</script>
</cfoutput>
<cfabort>

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.