• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 235
  • Last Modified:

How to use the path (database file) of a datasource in a SQL query.

I have query like this
<CFQUERY NAME="SAV1" DATASOURCE="usertables" dbtype="ODBC">
INSERT INTO USER(WEBPAGEID,COUNTRYID)
IN 'D:INETPUB\WWWROOT\DATABASES\CAWS1.MDB'
SELECT WEBPAGEID,COUNTRYID
FROM USERCONFIG_S
WHERE .....
</CFQUERY>
User is a table in a different database CAWS1.MDB and I use the query to insert the records in it hardcoding its path as shown above.
As CAWS1.MDB is a datasource ,I was wondering if its path as given in Database file field of the Administrator section can somehow be used so that I donot have to hardcode the path.
Is there any other way.
Thanks
AA
0
Ahson
Asked:
Ahson
  • 5
  • 4
1 Solution
 
YogCommented:
hi aa, creating a dsn without odbc is like this

<cfset DBPath="D:INETPUB\WWWROOT\DATABASES\CAWS1.MDB">
<cfquery name="QueryName"
 datasource="#DBPath#"
 dbtype="OLEDB"
 provider="yourprovider"
 providerdsn="#DBPath#"
 username="Admin"
 password="">

but what u r doing i think is not possible, meaning in one cfquery , trying to so a select and insert into another different database..board is quick - so u can wait to see answers..

cheers.
yog
0
 
AhsonAuthor Commented:
As we know every datasource is associated with a database file which is the path for the file.Is it possible to save the path for a datasource in a variable.i.e CAWS is a datasource and has a path associated to it .can we store the path for the CAWS datasource using some colf fusion function etc.
Thanks
0
 
YogCommented:
awson, didn't get you - u mean find in your file directory - where this file CAWS1.MDB exists and find the path - yea possible
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
AhsonAuthor Commented:
yes you are right Yog.
how can we do that.
many thanks
AA
0
 
YogCommented:
Ahson sorry
simply use the
fileexists() to check if the file is available and directoryexists() to see if the directory is available..

cheers.
yog ..
0
 
AhsonAuthor Commented:
Thanks But I think I confused you.
suppose we have a datasource = "Caws".Can we store the path to this datasource in a variable that could be used in a query.
Thanks again
AA
like
<cfset my_path=some function getting the path for datasource CAWS1.mdb>

<CFQUERY NAME="SAV1" DATASOURCE="usertables" dbtype="ODBC">
INSERT INTO USER(WEBPAGEID,COUNTRYID)
IN '#my_path#'
(instead of
IN D:INETPUB\WWWROOT\DATABASES\CAWS1.MDB')

SELECT WEBPAGEID,COUNTRYID
FROM USERCONFIG_S
WHERE .....
</CFQUERY>
Thanks
AA
0
 
YogCommented:
Ah, no you cant do that , better do

<cfquery name="lastquery" dsn="olddsn">
SELECT WEBPAGEID,COUNTRYID
FROM USERCONFIG_S
WHERE .....
</cfquery>

then
<cfoutput query="lastquery">

<cfquery dsn"newdsn" query="newquery">
INSERT INTO USER(WEBPAGEID,COUNTRYID)
VALUES(#lastquery.webpageid#,#lastquery.countryid#)
</cfquery>

</cfoutput>
0
 
AhsonAuthor Commented:
Thanks Yog
That was very helpful.I appreciate.
AA
0
 
AhsonAuthor Commented:
Thanks Yog
That was very helpful.I appreciate.
AA
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now