Inserting structure to table

[alecken]

I'm getting this value through a loop of a structure:

<cfloop collection="#st_datachanged#" item="key">

  <cfoutput>

  #key# - #st_datachanged[key]#<br>

  </cfoutput>

</cfloop>

Gender - F
LastName - Smith
FirstName - Adrienne
Addr1 - 9721 Willow Lane
City - Potomac
Zip - 20854-2053
State - MD

How can I insert this value into a row in my table?

My table row has these columns: FirstName,LastName,Gender,Addr1,City,State,Zip,Gender

I'm using CF8 and Sybase

[Dan_Bracuk]

insert into yourtable

(firstname

, lastname

etc)

select distinct #firstname#

, #lastname#

etc

from somesmalltable

where not exists

(subquery to prevent duplicates)

but with cfqueryparam, checking for nulls, etc.

[BKBK]

I have kept it generic. It will work generally for any struct, as the keys don't have to be spelled out.

I have assumed all the data is of string type. You may want to take the challenge to make the code more efficient.

<!--- comma-delimited list of keys --->
<cfset keyList = structKeyList(st_datachanged)>

<cfset keyValueList = "">
<cfset counter = 1>

<!--- create comma-delimited, single-quoted values of respective keys --->
<cfloop List="#keyList#" index="listItem">
    <cfset keyValueList = keyValueList & "'#st_datachanged[listItem]#'">
    <cfif counter LT listLen(keyList)>
        <cfset keyValueList = keyValueList & ",">
        <cfset counter = counter + 1>
    </cfif>
</cfloop>

<!--- insert row to table --->
<cfquery name="yourQueryName" datasource="yourDSN">
insert into yourTable(#keyList#)
values (#preserveSingleQuotes(keyValueList)#)
</cfquery>

[Dan_Bracuk]

Regarding:

It will work generally for any struct, as the keys don't have to be spelled out.

I have assumed all the data is of string type.

Did you also assume that none of the values contain apostrophes?

[BKBK]

@Dan Bracuk

Did you also assume that none of the values contain apostrophes?

You got me there, Dan. Yes, I did also assume none of the values contain apostrophes.

[BKBK]

I have looked it up. You can, depending on your database brand, escape single quotes if any exist. Just add the following line after the cfloop tag:

<!--- MySQL and PostgreSQL escape the ' character with: \' --->
<cfset listItem = replace(listItem,"'","\'","all")>

<!--- MS SQL, PostgreSQL and Oracle escape the ' character with: '' --->
<cfset listItem = replace(listItem,"'","''","all")>

[alecken]

Thank your for this solution but can I still you

cfqueryparam? and the apostrophy is causing error. PreserveSingleQuoe doesn't seems to work in here

[BKBK]

Thank your for this solution but can I still you cfqueryparam? and the apostrophy is causing error. PreserveSingleQuoe doesn't seems to work in here

Brilliant question and suggestion! Using cfqueryparam is in fact a much better option. I simply forgot.

When you use it, preserveSingleQuotes becomes redundant. That is because cfqueryparam will surround string values with single-quotes, and the cfquery tag will automatically escape any single quotes within string values.

The modified code follows:

<!--- create comma-delimited, single-quoted values of respective keys --->
<cfloop List="#keyList#" index="listItem">
    <cfset keyValueList = keyValueList & '<cfqueryparam cfsqltype="cf_sql_varchar" value="#st_datachanged[listItem]#">'>
    <cfif counter LT listLen(keyList)>
        <!--- add comma to separate values --->
        <cfset keyValueList = keyValueList & ",">
        <cfset counter = counter + 1>
    </cfif>
</cfloop>

<!--- insert row to table --->
<cfquery name="yourQueryName" datasource="yourDSN">
insert into yourTable(#keyList#)
values (#keyValueList#)
</cfquery>