Printable Version of Topic

Click here to view this topic in its original format

Dream.In.Code _ ColdFusion _ CF SQL TYPES

Posted by: iamjesus1342027 13 May, 2009 - 11:03 AM

i am still relatively new to cold fusion and so i just need some help with the cf sql types. the field report key is a randomly generated unique identifier. if anyone knows what i need to put in for the cfsqltype that will be greatly appreciated! or if you see another problem with my code let me know. thanks!!


This is the error that Im getting for the following code.
Invalid data [B9952407-09F1-7DB4-4BE57A21790A38BC] for CFSQLTYPE CF_SQL_VARCHAR.


CODE

<!--- This code publishes the randomly created Exploitation Draft Reports in the CIDNE database --->

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<cfobject name="CIDNEGeoCoord" component="_components.geocoord">

<!--- Define Vars --->
<cfparam name="session.sysdb" default="CIDNE14">
<cfparam name="session.GEODB" default="postgres">
<cfparam name="url.groupname" default="Admin">
<html>
    <head>
        <title>Publish CIDNE PASS Test Data</title>
    </head>
    <body>
        <cfoutput>
            <cfloop list=& #34;ExploitationBio,ExploitationCache,ExploitationCaseFile,ExploitationChemical,
ExploitationDTK,ExploitationElectronic,ExploitationIDF,ExploitationIED,Exploitat
ionMFTER" index="ListItem" delimiters=",">
                <cfset reportName = #ListItem#>

                <cfquery name="published" datasource="#session.sysdb#">
                    SELECT ReportKey
                    FROM #reportName#PublishedReport
                </cfquery>

                <cfset keys = ArrayNew(1)>
                <cfloop query="published">
                    <cfset temp = ArrayAppend(#keys#,#ReportKey#)>
                </cfloop>

                

                <cfif ArrayToList(#keys#, ",") neq "">
                    <cfquery name="insert" datasource="#session.sysdb#">
                        INSERT INTO #reportName#PublishedReport & #40;ReportKey,UpdatedByName,UpdatedByUnit,Classification,ClassificationReleasabi
lityMark,GMTDelta,OriginatorName,OriginatorUnit,DatePosted,isDeprecated,PublishL
evel,OriginatorGroup)
                        VALUES ((SELECT & #40;ReportKey,UpdatedByName,UpdatedByUnit,Classification,ClassificationReleasabi
lityMark,GMTDelta,OriginatorName,OriginatorUnit,DatePosted,isDeprecated,PublishL
evel,OriginatorGroup)))
                        FROM #reportName#DraftReport
                        WHERE DatePosted = '5/11/2009 4:00:00 PM'
                        AND ReportKey NOT IN (<cfqueryparam value="#keys#" list="yes" separator="," cfsqltype="cf_sql_varchar">)
                    </cfquery>
                </cfif>
            </cfloop>    
        </cfoutput>
    </body>
</html>

Posted by: iamjesus1342027 13 May, 2009 - 11:16 AM

also when i try this code i receive this error.
[Macromedia][SQLServer JDBC Driver][SQLServer]Subqueries are not allowed in this context. Only scalar expressions are allowed.

in this code i set the array keys to a list before the if statement. then i use the list as the variable in the if statement and in the query insert.




CODE

<!--- This code publishes the randomly created Exploitation Draft Reports in the CIDNE database --->

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<cfobject name="CIDNEGeoCoord" component="_components.geocoord">

<!--- Define Vars --->
<cfparam name="session.sysdb" default="CIDNE14">
<cfparam name="session.GEODB" default="postgres">
<cfparam name="url.groupname" default="Admin">
<html>
    <head>
        <title>Publish CIDNE PASS Test Data</title>
    </head>
    <body>
        <cfoutput>
            <cfloop list=& #34;ExploitationBio,ExploitationCache,ExploitationCaseFile,ExploitationChemical,
ExploitationDTK,ExploitationElectronic,ExploitationIDF,ExploitationIED,Exploitat
ionMFTER" index="ListItem" delimiters=",">
                <cfset reportName = #ListItem#>

                <cfquery name="published" datasource="#session.sysdb#">
                    SELECT ReportKey
                    FROM #reportName#PublishedReport
                </cfquery>

                <cfset keys = ArrayNew(1)>
                <cfloop query="published">
                    <cfset temp = ArrayAppend(#keys#,#ReportKey#)>
                </cfloop>
                <cfset List = ArrayToList(#keys#, ",")>
                

                <cfif #List# neq "">
                    <cfquery name="insert" datasource="#session.sysdb#">
                        INSERT INTO #reportName#PublishedReport & #40;ReportKey,UpdatedByName,UpdatedByUnit,Classification,ClassificationReleasabi
lityMark,GMTDelta,OriginatorName,OriginatorUnit,DatePosted,isDeprecated,PublishL
evel,OriginatorGroup)
                        VALUES ((SELECT & #40;ReportKey,UpdatedByName,UpdatedByUnit,Classification,ClassificationReleasabi
lityMark,GMTDelta,OriginatorName,OriginatorUnit,DatePosted,isDeprecated,PublishL
evel,OriginatorGroup)))
                        FROM #reportName#DraftReport
                        WHERE DatePosted = '5/11/2009 4:00:00 PM'
                        AND ReportKey NOT IN (<cfqueryparam value="#List#" list="yes" separator="," cfsqltype="cf_sql_varchar">)
                    </cfquery>
                </cfif>
            </cfloop>    
        </cfoutput>
    </body>
</html>

Posted by: cfrobuster 14 May, 2009 - 05:49 AM

I can help you with your problem. Please get a hold of me.



saundersje@gmail.com

QUOTE(iamjesus1342027 @ 13 May, 2009 - 11:03 AM) *

i am still relatively new to cold fusion and so i just need some help with the cf sql types. the field report key is a randomly generated unique identifier. if anyone knows what i need to put in for the cfsqltype that will be greatly appreciated! or if you see another problem with my code let me know. thanks!!


This is the error that Im getting for the following code.
Invalid data [B9952407-09F1-7DB4-4BE57A21790A38BC] for CFSQLTYPE CF_SQL_VARCHAR.


CODE

<!--- This code publishes the randomly created Exploitation Draft Reports in the CIDNE database --->

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<cfobject name="CIDNEGeoCoord" component="_components.geocoord">

<!--- Define Vars --->
<cfparam name="session.sysdb" default="CIDNE14">
<cfparam name="session.GEODB" default="postgres">
<cfparam name="url.groupname" default="Admin">
<html>
    <head>
        <title>Publish CIDNE PASS Test Data</title>
    </head>
    <body>
        <cfoutput>
            <cfloop list=& #34;ExploitationBio,ExploitationCache,ExploitationCaseFile,ExploitationChemical,
ExploitationDTK,ExploitationElectronic,ExploitationIDF,ExploitationIED,Exploitat
ionMFTER" index="ListItem" delimiters=",">
                <cfset reportName = #ListItem#>

                <cfquery name="published" datasource="#session.sysdb#">
                    SELECT ReportKey
                    FROM #reportName#PublishedReport
                </cfquery>

                <cfset keys = ArrayNew(1)>
                <cfloop query="published">
                    <cfset temp = ArrayAppend(#keys#,#ReportKey#)>
                </cfloop>

                

                <cfif ArrayToList(#keys#, ",") neq "">
                    <cfquery name="insert" datasource="#session.sysdb#">
                        INSERT INTO #reportName#PublishedReport & #40;ReportKey,UpdatedByName,UpdatedByUnit,Classification,ClassificationReleasabi
lityMark,GMTDelta,OriginatorName,OriginatorUnit,DatePosted,isDeprecated,PublishL
evel,OriginatorGroup)
                        VALUES ((SELECT & #40;ReportKey,UpdatedByName,UpdatedByUnit,Classification,ClassificationReleasabi
lityMark,GMTDelta,OriginatorName,OriginatorUnit,DatePosted,isDeprecated,PublishL
evel,OriginatorGroup)))
                        FROM #reportName#DraftReport
                        WHERE DatePosted = '5/11/2009 4:00:00 PM'
                        AND ReportKey NOT IN (<cfqueryparam value="#keys#" list="yes" separator="," cfsqltype="cf_sql_varchar">)
                    </cfquery>
                </cfif>
            </cfloop>    
        </cfoutput>
    </body>
</html>



Posted by: iamjesus1342027 14 May, 2009 - 05:59 AM

cfrobuster thanks for the response but i actually figured it out and it had nothing to do with the sql type. the problem was with the published query. i added a where clause and that fixed the problem. but thanks for your reply.



QUOTE(cfrobuster @ 14 May, 2009 - 05:49 AM) *

I can help you with your problem. Please get a hold of me.



saundersje@gmail.com

QUOTE(iamjesus1342027 @ 13 May, 2009 - 11:03 AM) *

i am still relatively new to cold fusion and so i just need some help with the cf sql types. the field report key is a randomly generated unique identifier. if anyone knows what i need to put in for the cfsqltype that will be greatly appreciated! or if you see another problem with my code let me know. thanks!!


This is the error that Im getting for the following code.
Invalid data [B9952407-09F1-7DB4-4BE57A21790A38BC] for CFSQLTYPE CF_SQL_VARCHAR.


CODE

<!--- This code publishes the randomly created Exploitation Draft Reports in the CIDNE database --->

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<cfobject name="CIDNEGeoCoord" component="_components.geocoord">

<!--- Define Vars --->
<cfparam name="session.sysdb" default="CIDNE14">
<cfparam name="session.GEODB" default="postgres">
<cfparam name="url.groupname" default="Admin">
<html>
    <head>
        <title>Publish CIDNE PASS Test Data</title>
    </head>
    <body>
        <cfoutput>
            <cfloop list=& #34;ExploitationBio,ExploitationCache,ExploitationCaseFile,ExploitationChemical,
ExploitationDTK,ExploitationElectronic,ExploitationIDF,ExploitationIED,Exploitat
ionMFTER" index="ListItem" delimiters=",">
                <cfset reportName = #ListItem#>

                <cfquery name="published" datasource="#session.sysdb#">
                    SELECT ReportKey
                    FROM #reportName#PublishedReport
                </cfquery>

                <cfset keys = ArrayNew(1)>
                <cfloop query="published">
                    <cfset temp = ArrayAppend(#keys#,#ReportKey#)>
                </cfloop>

                

                <cfif ArrayToList(#keys#, ",") neq "">
                    <cfquery name="insert" datasource="#session.sysdb#">
                        INSERT INTO #reportName#PublishedReport & #40;ReportKey,UpdatedByName,UpdatedByUnit,Classification,ClassificationReleasabi
lityMark,GMTDelta,OriginatorName,OriginatorUnit,DatePosted,isDeprecated,PublishL
evel,OriginatorGroup)
                        VALUES ((SELECT & #40;ReportKey,UpdatedByName,UpdatedByUnit,Classification,ClassificationReleasabi
lityMark,GMTDelta,OriginatorName,OriginatorUnit,DatePosted,isDeprecated,PublishL
evel,OriginatorGroup)))
                        FROM #reportName#DraftReport
                        WHERE DatePosted = '5/11/2009 4:00:00 PM'
                        AND ReportKey NOT IN (<cfqueryparam value="#keys#" list="yes" separator="," cfsqltype="cf_sql_varchar">)
                    </cfquery>
                </cfif>
            </cfloop>    
        </cfoutput>
    </body>
</html>




Powered by Invision Power Board (http://www.invisionboard.com)
© Invision Power Services (http://www.invisionpower.com)