3 Replies - 5138 Views - Last Post: 14 May 2009 - 06:59 AM Rate Topic: -----

#1 iamjesus1342027  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 41
  • Joined: 31-March 09

CF SQL TYPES

Post icon  Posted 13 May 2009 - 12:03 PM

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.


<!--- 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="ExploitationBio,ExploitationCache,ExploitationCaseFile,ExploitationChemical,ExploitationDTK,ExploitationElectronic,ExploitationIDF,ExploitationIED,ExploitationMFTER" 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 (ReportKey,UpdatedByName,UpdatedByUnit,Classification,ClassificationReleasabilityMark,GMTDelta,OriginatorName,OriginatorUnit,DatePosted,isDeprecated,PublishLevel,OriginatorGroup)
						VALUES ((SELECT (ReportKey,UpdatedByName,UpdatedByUnit,Classification,ClassificationReleasabilityMark,GMTDelta,OriginatorName,OriginatorUnit,DatePosted,isDeprecated,PublishLevel,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>



Is This A Good Question/Topic? 0
  • +

Replies To: CF SQL TYPES

#2 iamjesus1342027  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 41
  • Joined: 31-March 09

Re: CF SQL TYPES

Posted 13 May 2009 - 12:16 PM

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.




<!--- 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="ExploitationBio,ExploitationCache,ExploitationCaseFile,ExploitationChemical,ExploitationDTK,ExploitationElectronic,ExploitationIDF,ExploitationIED,ExploitationMFTER" 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 (ReportKey,UpdatedByName,UpdatedByUnit,Classification,ClassificationReleasabilityMark,GMTDelta,OriginatorName,OriginatorUnit,DatePosted,isDeprecated,PublishLevel,OriginatorGroup)
						VALUES ((SELECT (ReportKey,UpdatedByName,UpdatedByUnit,Classification,ClassificationReleasabilityMark,GMTDelta,OriginatorName,OriginatorUnit,DatePosted,isDeprecated,PublishLevel,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>


Was This Post Helpful? 0
  • +
  • -

#3 cfrobuster  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 14-May 09

Re: CF SQL TYPES

Posted 14 May 2009 - 06:49 AM

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



saundersje@gmail.com

View Postiamjesus1342027, on 13 May, 2009 - 11:03 AM, said:

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.


<!--- 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="ExploitationBio,ExploitationCache,ExploitationCaseFile,ExploitationChemical,ExploitationDTK,ExploitationElectronic,ExploitationIDF,ExploitationIED,ExploitationMFTER" 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 (ReportKey,UpdatedByName,UpdatedByUnit,Classification,ClassificationReleasabilityMark,GMTDelta,OriginatorName,OriginatorUnit,DatePosted,isDeprecated,PublishLevel,OriginatorGroup)
						VALUES ((SELECT (ReportKey,UpdatedByName,UpdatedByUnit,Classification,ClassificationReleasabilityMark,GMTDelta,OriginatorName,OriginatorUnit,DatePosted,isDeprecated,PublishLevel,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>


Was This Post Helpful? 0
  • +
  • -

#4 iamjesus1342027  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 41
  • Joined: 31-March 09

Re: CF SQL TYPES

Posted 14 May 2009 - 06: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.



View Postcfrobuster, on 14 May, 2009 - 05:49 AM, said:

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



saundersje@gmail.com

View Postiamjesus1342027, on 13 May, 2009 - 11:03 AM, said:

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.


<!--- 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="ExploitationBio,ExploitationCache,ExploitationCaseFile,ExploitationChemical,ExploitationDTK,ExploitationElectronic,ExploitationIDF,ExploitationIED,ExploitationMFTER" 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 (ReportKey,UpdatedByName,UpdatedByUnit,Classification,ClassificationReleasabilityMark,GMTDelta,OriginatorName,OriginatorUnit,DatePosted,isDeprecated,PublishLevel,OriginatorGroup)
						VALUES ((SELECT (ReportKey,UpdatedByName,UpdatedByUnit,Classification,ClassificationReleasabilityMark,GMTDelta,OriginatorName,OriginatorUnit,DatePosted,isDeprecated,PublishLevel,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>


Was This Post Helpful? 0
  • +
  • -

Page 1 of 1