• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 230
  • 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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
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

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

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