7 Replies - 4798 Views - Last Post: 26 February 2009 - 09:51 AM Rate Topic: -----

#1 vampirevn01  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 05-February 09

Query Result, Move and Copy Directory

Posted 15 February 2009 - 12:45 AM

Hello. Please help me.
I have a query :
select * from members

and i want this query result to me all column name and infor of this table.
I mean don't user this code
<cfoutput query=sqlquery>
#columnname# <!-- example -->
</cfoutput>

Like Result this
Posted Image

2.
I have code use cfzip to move and copy directory but slow and not good to move and copy directory have big file ( i mean big size ).
Read it and help me have a new code to copy and move directory. Thanks
<!--- Copy Directory --->
<cffunction name="dirCopy" output="false">
	<cfargument name="source" required="true" type="string">
	<cfargument name="destination" required="true" type="string">
	<cfset var uid = CreateUUID()>
	<cfzip action="zip" file="#GetTempDirectory()##uid#.zip" recurse="yes" source="#arguments.source#" storePath="yes" />
	<cfif Not DirectoryExists("#arguments.destination#")>
		<cfdirectory action="create" directory="#arguments.destination#">
	</cfif>
	<cfzip action="unzip" file="#GetTempDirectory()##uid#.zip" destination="#arguments.destination#" storePath="yes" />
	<cffile action="delete" file="#GetTempDirectory()##uid#.zip">
</cffunction>
<!--- Move Directory  --->
<cffunction name="dirMove" output="false">
	<cfargument name="source" required="true" type="string">
	<cfargument name="destination" required="true" type="string">
	<cfset var uid = CreateUUID()>
	<cfzip action="zip" file="#GetTempDirectory()##uid#.zip" recurse="yes" source="#arguments.source#" storePath="yes" />
	<cfif Not DirectoryExists("#arguments.destination#")>
		<cfdirectory action="create" directory="#arguments.destination#">
	</cfif>
	<cfzip action="unzip" file="#GetTempDirectory()##uid#.zip" destination="#arguments.destination#" storePath="yes" />
	<cffile action="delete" file="#GetTempDirectory()##uid#.zip">
	<cfset dirDelete('#source#')>
</cffunction>

This post has been edited by vampirevn01: 15 February 2009 - 12:46 AM


Is This A Good Question/Topic? 0
  • +

Replies To: Query Result, Move and Copy Directory

#2 sansclue  Icon User is offline

  • D.I.C Regular

Reputation: 29
  • View blog
  • Posts: 316
  • Joined: 21-November 07

Re: Query Result, Move and Copy Directory

Posted 15 February 2009 - 09:05 AM

View Postvampirevn01, on 14 Feb, 2009 - 11:45 PM, said:

1. ... and i want this query result to me all column name and infor of this table.
I mean don't user this code
....
2.
I have code use cfzip to move and copy directory but slow and not good to move and copy directory have big file ( i mean big size ).
Read it and help me have a new code to copy and move directory.


1. Query columns can be accessed dynamically using array notation: #queryName["columnName"]["rowNumber"]#. To get the names of the columns in the query, use the query #columnList# variable. Then just loop through that list:

<cfoutput query="yourQuery">
   <cfloop list="#yourQuery.columnList#" index="columnName">
		 <!--- your output code goes here --->
   </cfloop>
</cfoutput>




2. What happens if you do a recursive copy/move versus zip/unzip ?
Was This Post Helpful? 1

#3 vampirevn01  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 05-February 09

Re: Query Result, Move and Copy Directory

Posted 18 February 2009 - 09:44 AM

1. Can you give me some specific examplesabout your code ?
2. If I use zip/unzip to copy and move file then occupied memory and very slow if i move or copy directory big size.
3. I have the sql query:
SHOW DATABASES; SELECT * FROM user; SELECT version(); select user();


and the Coldfusion SQL Query code :
		<cfoutput>
		<cfform name="articles1" ENCTYPE="multipart/form-data">
			DataSource:
			<cfif isDefined("database")>
				<cfinput type="text" name="database" size="25" value="#database#"><br>
			<cfelse>
				<cfinput type="text" name="database" size="25" value=""><br>
			</cfif>
			SQL query: <cfinput type="text" query="SQL" name="query" size="130" value=""><br>
			SQL out put: <cfinput type="text" name="output" size="100" value=""><br>
			<input type="submit" value="Do it" />
		</cfform>
		</cfoutput>
		<cfif isDefined("database")>
			<cfquery name="SQL" DataSource="#database#">
				#query#
			</cfquery>
			Query:
			<cfoutput>#query#</cfoutput>
			Result:
			<cfoutput query="SQL">
				<div>
					# SQL [ "#output#"] [currentRow] #
				<div>
			</ cfoutput>
		</cfif>

Example:
SQL Query: select * from user
SQL output: username
we will get all record of column "username" in table "user"
But
I can't use multi query in this code.
I don't know if I i use query "SHOW DATABASES;" then what it Out Put :-s.
Thanks for help.

This post has been edited by vampirevn01: 18 February 2009 - 09:45 AM

Was This Post Helpful? 0
  • +
  • -

#4 sansclue  Icon User is offline

  • D.I.C Regular

Reputation: 29
  • View blog
  • Posts: 316
  • Joined: 21-November 07

Re: Query Result, Move and Copy Directory

Posted 18 February 2009 - 04:45 PM

View Postvampirevn01, on 18 Feb, 2009 - 08:44 AM, said:

<cfoutput query="SQL">
<div>
# SQL [ "#output#"] [currentRow] #
<div>
</ cfoutput>


You forgot the inner loop. The outer cfoutput loop will iterate through the records. But you also need an inner loop to iterate through each of the _columns_ in the current record.

<cfoutput query="yourQuery">
   <cfloop list="#yourQuery.columnList#" index="columnName">
		 #SQL[columnName][currentRow]# ... 
   </cfloop><br>
</cfoutput>



View Postvampirevn01, on 18 Feb, 2009 - 08:44 AM, said:

2. If I use zip/unzip to copy and move file then occupied memory and very slow if i move or copy directory big size.

3. ... I don't know if I i use query "SHOW DATABASES;" then what it Out Put :-s.



2. Yes, but why zip then unzip? Have you tried a recursive copy/move instead. Just to compare the time differences. To a certain degree, there is not much you can do about large files ...

3. I don't think you can use SHOW DATABASE that way. Instead, I would try querying the information schema views. But if you want to know what is output, just dump the query object with <cfdump>.

http://dev.mysql.com...ion-schema.html


(To anyone reading this thread in the future, be wary of this kind of dynamic sql. It is a major sql injection risk)

This post has been edited by sansclue: 18 February 2009 - 04:49 PM

Was This Post Helpful? 1

#5 vampirevn01  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 05-February 09

Re: Query Result, Move and Copy Directory

Posted 21 February 2009 - 11:38 PM

I fixed to this
		<cfoutput>
		<cfform name="articles1" ENCTYPE="multipart/form-data">
			DataSource:
			<cfif isDefined("database")>
				<cfinput type="text" name="database" size="25" value="#database#"><br>
			<cfelse>
				<cfinput type="text" name="database" size="25" value=""><br>
			</cfif>
			SQL query: <cfinput type="text" query="SQL" name="query" size="130" value=""><br>
			Query Output: <cfinput type="text" name="output" size="100" value=""><br>
			<input type="submit" value="Submit" />
		</cfform>
		</cfoutput>
		<cfif isDefined("database")>
			<cfquery name="SQL" DataSource="#database#">
				#query#
			</cfquery>
			Query:
			<cfoutput>#query#</cfoutput>
			Result:
			<cfoutput query="SQL">
				<cfloop list="#SQL#" index="output">
					# SQL [ "#output#"] [currentRow] #
				</cfloop><br>
			</ cfoutput>
		</cfif>

But not work.
What it "list" and "index" in this code ?
I don't know why i need this and how to use.
I don't know how to get result of query "show database;" too.

How to have "#yourQuery.columnList#" use "# SQL [ "#output#"] [currentRow] # "?
<cfoutput query="yourQuery">
   <cfloop list="#yourQuery.columnList#" index="columnName">
		 <!--- your output code goes here --->
   </cfloop>
</cfoutput>


I need fix code to:
Can query multi query and multi "output" ( or help me how to get result of multi SQL query).
Thanks for help.

This post has been edited by vampirevn01: 21 February 2009 - 11:56 PM

Was This Post Helpful? 0
  • +
  • -

#6 sansclue  Icon User is offline

  • D.I.C Regular

Reputation: 29
  • View blog
  • Posts: 316
  • Joined: 21-November 07

Re: Query Result, Move and Copy Directory

Posted 22 February 2009 - 12:42 AM

View Postvampirevn01, on 21 Feb, 2009 - 10:38 PM, said:

What it "list" and "index" in this code ?


"ColumnList" is a query variable. As its name implies it contains a list of the column names in your query. I provided a pretty clear example above about how to use it. Just replace "yourQuery" with the name of your actual query. I don't know what more to tell you about it. You just have to try it yourself. It doesn't help you if we do all the code for you, because you won't understand the code (or how to fix it if it breaks) :)

But if you are struggling with basics like the "list" and "index" attributes of cfloop, then you should review the documentation. It sounds like you need to learn about some basic CF concepts first, which is something only you can do.

http://livedocs.adob...ags_p-q_17.html
http://livedocs.adob...10.html#3301338

This post has been edited by sansclue: 22 February 2009 - 12:44 AM

Was This Post Helpful? 1

#7 vampirevn01  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 05-February 09

Re: Query Result, Move and Copy Directory

Posted 26 February 2009 - 08:08 AM

<cfform name="articles1" ENCTYPE="multipart/form-data">
	Query: <cfinput type="text" name="query" size="130" value=""><br>
	<input type="submit" value="Submit" />
</cfform>
<cfif isDefined("database") and isDefined("query")>
	<cfquery name="SQL" DataSource="#database#">
		#query#
	</cfquery>



Help me, when I put the query have ', then have error but I can't fix this.
Help me know how to fix that.
Thanks again

This post has been edited by vampirevn01: 26 February 2009 - 08:13 AM

Was This Post Helpful? 0
  • +
  • -

#8 sansclue  Icon User is offline

  • D.I.C Regular

Reputation: 29
  • View blog
  • Posts: 316
  • Joined: 21-November 07

Re: Query Result, Move and Copy Directory

Posted 26 February 2009 - 09:51 AM

View Postvampirevn01, on 26 Feb, 2009 - 07:08 AM, said:

when I put the query have ', then have error but I can't fix this.


Single quotes are always an issue with this kind of dynamic sql. By default CF escapes ' and replaces it with '' (ie two single quotes). This is done deliberately to help protect your database against sql injection. You can choose to ignore those security measures by using the PreserveSingleQuotes function. Of course this means that you may have to escape single quotes manually. You can learn more about this function in the documentation:

http://livedocs.adob...ons_m-r_14.html

Good Luck
Was This Post Helpful? 1

Page 1 of 1