Solved

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

Posted on 2001-06-05
9
190 Views
Last Modified: 2013-12-24
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
Comment
Question by:Ahson
  • 5
  • 4
9 Comments
 
LVL 5

Expert Comment

by:Yog
ID: 6155886
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
 

Author Comment

by:Ahson
ID: 6156403
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
 
LVL 5

Expert Comment

by:Yog
ID: 6156490
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
 

Author Comment

by:Ahson
ID: 6156840
yes you are right Yog.
how can we do that.
many thanks
AA
0
New! My Passport Wireless Pro Wi-Fi Mobile Storage

Portable wireless storage to offload, edit, and stream anywhere.

High-capacity, wireless mobile storage designed to accompany professional photographers and videographers in the field to easily offload, edit and stream captured photos and high-definition videos.

 
LVL 5

Expert Comment

by:Yog
ID: 6158034
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
 

Author Comment

by:Ahson
ID: 6158894
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
 
LVL 5

Accepted Solution

by:
Yog earned 50 total points
ID: 6159956
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
 

Author Comment

by:Ahson
ID: 6160377
Thanks Yog
That was very helpful.I appreciate.
AA
0
 

Author Comment

by:Ahson
ID: 6160379
Thanks Yog
That was very helpful.I appreciate.
AA
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
question about access to website 2 68
AWS Advice on using WHM/cPanel 1 69
DNS, website, godaddy 6 71
Domino Website - Redirection 12 47
This is a guide to setting up a new WHM/cPanel Server to be used for web hosting accounts. It is intended for web hosting company administrators and dedicated server owners. For under $99 per month (considering normal rate of Big Data Cetnters like …
Article by: kevp75
Hey folks, 'bout time for me to come around with a little tip. Thanks to IIS 7.5 Extensions and Microsoft (well... really Windows 8, and IIS 8 I guess...), we can now prime our Application Pools, when IIS starts. Now, though it would be nice t…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
I designed this idea while studying technology in the classroom.  This is a semester long project.  Students are asked to take photographs on a specific topic which they find meaningful, it can be a place or situation such as travel or homelessness.…

929 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now