Solved

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

Posted on 2001-06-05
9
199 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Comment

by:Ahson
ID: 6156840
yes you are right Yog.
how can we do that.
many thanks
AA
0
 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Running Guacamole on port 80 5 212
Website being blocked? 3 126
Internal DNS Alias (CNAME) - two domain names to one IP address 4 77
PHP in Apache server 20 92
If you don't have the right permissions set for your WordPress location in IIS, you won't be able to perform automatic updates. Here's how to fix the problem.
Meet the world's only “Transparent Cloud™” from Superb Internet Corporation. Now, you can experience firsthand a cloud platform that consistently outperforms Amazon Web Services (AWS), IBM’s Softlayer, and Microsoft’s Azure when it comes to CPU and …
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

775 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