Possible to Pass Form Data to SQL Statement?

[andy570]

Just a quick question. Is it possible to pass form data to a sql statement. For example: on one page a drop down list with a list of the different databases and another area for the table. Can these be passed to the SQL statement with something like:

<cfquery name="getinfo" datasource="#FORM.database#">
SELECT * FROM '#FORM.Table#'
</cfquery>

Thanks

 

[NorthStarDA]

i dont see why not, im pretty sure i've done it before. I wouldn't use the quotes around the table name though, have you tried it yet?


=========================================
I have not failed. I've just found 10,000 ways that won't work.
Thomas A. Edison

 

[webmigit]

You can't pass the database.. But I'm sure you meant to pass datasource.. and that you can do fine.

You can pass the table.. the sql statement is compiled before it is sent to the database.. So the database never ever sees a cold fusion variable.

ALFII.com

http://www.alfii.com

---------------------
If this post answered or helped to answer your question, please reply with such so that forum members with a similar question will know to use this advice.

 

[philhege]

Couldn't Andy do something like

<cfset mytableref = trim(form.database)&".dbo."&trim(form.table)>

<cfquery datasource="mydsn">
SELECT * from #mytableref#
</cfquery>

Given, of course, that the login has rights to the DB?

Phil Hegedusich
Senior Web Developer
IIMAK

http://www.iimak.com

-----------
Boy howdy, my Liberal Studies degree really prepared me for this....
-----------
A skeleton walks into a bar, and says "I'll have a beer and a mop.

 

[TruthInSatire]

phil, that's the table not the database. The database is defined in the datasource.

Like webmigit said you can basicaly make the entire sql dynamic.

i've even at times used single variables.

<cfset myQuery = "Select * from " & #form.table# & " where id = " & #form.id#>
<cfquery name = "qGetData" datasource = "dsn">
#preserveSingleQuotes(myQuery)#
</cfquery>

Human beings, who are almost unique in having the ability to learn from the experience of others, are also remarkable for their apparent disinclination to do so.
-Douglas Adams (1952-2001)

 

[philhege]

I know it's the table, but I'm using the three-part naming convention. SQL understands that you want to query a DB outside of the "defined" or default DB. So,

database.owner.table

would go directly to the source, regardless of the "set" database.

Once again, your login has to have rights to the DB in question.

Phil Hegedusich
Senior Web Developer
IIMAK

http://www.iimak.com

-----------
Boy howdy, my Liberal Studies degree really prepared me for this....
-----------
A skeleton walks into a bar, and says "I'll have a beer and a mop.

 

[webmigit]

Honestly I'd never tried that but I've heard often enough that you can't query two datasources in the samwe query like that, without query of queries..

So its an interesting one..

Unfortunately I can't try it since the two datasources I would try it on have seperate usernames and passwords.



ALFII.com

http://www.alfii.com

---------------------
If this post answered or helped to answer your question, please reply with such so that forum members with a similar question will know to use this advice.

 

[philhege]

Yep; gotta have a common login/user for this to work. Can't you get the admin to create a DB login for this purpose? (I do my own so I guess I'm spoiled.)

Phil Hegedusich
Senior Web Developer
IIMAK

http://www.iimak.com

-----------
Boy howdy, my Liberal Studies degree really prepared me for this....
-----------
A skeleton walks into a bar, and says "I'll have a beer and a mop.

 

[andy570]

Thanks, took the quotes away from the Form.Table and it worked fine.

 

[stilllwaiting]

You can use a variable from any scope to define your datasource.

Something I usually do to make things easier on the switch from a development ds to a live ds (without having to go through each page and change the datasource tag by hand) is to create a variable in the application scope in your application.cfm page.

    <cfset application.ds = "developmentDatasource">

Then you can use it at any point in your code

<cfquery name="myQuery" datasource="#application.ds#">
     select * from FooTable
</cfquery>

Then when you bring it all live, you just copy the files over and change one line in your application.cfm page

    <cfset application.ds = "liveDatasource">

And your good to go.

 

[webmigit]

phil..

Yeah I do.. I just was too busy at that time and was a waste of company resources to run the test, that's all..

ALFII.com

http://www.alfii.com

---------------------
If this post answered or helped to answer your question, please reply with such so that forum members with a similar question will know to use this advice.

 

[philhege]

(sorry webmigit I'm like three posts behind with this reply)

This method has little to do with Q of Q. It's all about the capabilities of the data server, or the characteristics of the DSN (for pure file-based connections like DBF free-tables).

I have an instance of SQL Server. It has numerous databases, divided by functional area or application or whatever. But my Web application needs access to multiple DBs. So I have instance-wide logins (or roles) that are also assinged as users and given roles/rights to the individual DBs on the instance. This is the proper way to manage security, and it saves you from creating different DSNs for individual DBs or monking around with application variables (although this is still a good approach for test vs. production sources).

My US$.02

Phil Hegedusich
Senior Web Developer
IIMAK

http://www.iimak.com

-----------
Boy howdy, my Liberal Studies degree really prepared me for this....
-----------
A skeleton walks into a bar, and says "I'll have a beer and a mop.