?
Solved

Data source connection without ColdFusion Administrator

Posted on 2003-03-04
5
Medium Priority
?
580 Views
Last Modified: 2013-12-24
Does anyone know if it is possible to connect to a data source without using ColdFusion Administrator?

If it is possible can anyone show an example connection to an Access database and one to an Excel spreadsheet?
0
Comment
Question by:McHack
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 17

Expert Comment

by:anandkp
ID: 8069622
hi this is easy

<cfset DBPath="D:\InetPub\wwwRoot\YourSite\file.mdb">

<cfquery name="QueryName"
datasource="#DBPath#"
dbtype="OLEDB"
provider="Microsoft.Jet.OLEDB.4.0"
providerdsn="#DBPath#"
username="Admin"
password="">

..Your SQL statments...

</cfquery>

OR

<cfquery
name="selectData"
connectstring="Provider=SQLOLEDB.1;Persist Security Info=False;User ID=[user];pwd=[pass];Initial Catalog=[Database Name];Data Source=[Server Name]"
dbtype="dynamic">
select username from users
</cfquery>

K'Rgds
Anand
0
 

Author Comment

by:McHack
ID: 8069838
anandkp

Thanks for the input.

Will this work if the data source is on a remote server?

In other words will I have to be able to set up an ODBC connection or will the code you posted handle the entire connection process even if the data source is on a remote server?
0
 
LVL 17

Accepted Solution

by:
anandkp earned 800 total points
ID: 8069899
go thru this article !
----------------------------------------------------------
ColdFusion DSNLess Connections:

ColdFusion 5 introduces DSNLess connections for ODBC drivers that use the same syntax as the DSNLess connections that ASP programmers have grown accustomed to. UltraDev doesn't support these connections yet, but they can be hand-coded in your ColdFusion page with a little knowledge of how the syntax works.

The <CFQUERY> tag in ColdFusion 5 has several new options. One of these is a DBTYPE of "dynamic". This allows the use of connection strings using the CONNECTSTRING attribute of the <CFQUERY> tag:

<CFQUERY NAME="rs"
 DBTYPE="dynamic"
 CONNECTSTRING="Driver={Microsoft Access Driver (*.mdb)};Dbq=c:\db\mydb.mdb;Uid=Admin;Pwd=;"
>
SELECT SeekId, SeekUsername, SeekPassword from Seekers
</CFQUERY>

If you define a CFQUERY in this manner, you can't use the Data Bindings palette within UltraDev for your recordset information, such as the columns and views returned from the query, but these can be handcoded.

The advantage of this method is that you don't need to define your connection to the CFAdministrator any more -- they can be done completely on the fly using path information for the database (if it's a file based database) or the server and database name for server-based databases such as SQL Server and MySQL. The connection string typically supplies the username and password, as well as any other values that the database needs for a successful connection.

The following connection strings are some of the more popular ODBC connection strings* that you can use to declare your connection on the fly. The connection string should be placed in quotes on one line entirely using the CONNECTSTRING attribute.

*note: OLE DB connection strings don't work in ColdFusion 5.

ODBC DSNLess Connections:

MS Access ODBC DSNless connection

Driver={Microsoft Access Driver (*.mdb)};Dbq=c:\somepath\dbname.mdb;Uid=Admin;Pwd=pass;


dBase ODBC DSNless connection

Driver={Microsoft dBASE Driver (*.dbf)};DriverID=277;Dbq=c:\somepath\dbname.dbf;

Oracle ODBC DSNless connection

Driver={Microsoft ODBC for Oracle};Server=OracleServer.world;Uid=admin;Pwd=pass;

MS SQL Server DSNless connection

Driver={SQL Server};Server=servername;Database=dbname;Uid=sa;Pwd=pass;

MS Text Driver DSNless connection

Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=c:\somepath\;Extensions=asc,csv,tab,txt;Persist Security Info=False;

Visual Foxpro DSNless connection

Driver={Microsoft Visual FoxPro Driver};SourceType=DBC;SourceDB=c:\somepath\dbname.dbc;Exclusive=No;

MySQL DSNless connection

driver={mysql}; database=yourdatabase;server=yourserver;uid=username;pwd=password;option=16386;

Using ExpandPath in the ConnectString

ExpandPath is a built-in ColdFusion function that will retrieve the full path of a file (including the drive letter) given the relative path of the file. A relative path cannot begin with a slash or backslash:

#ExpandPath('..\bettergig\cgi-bin\bettergig.mdb')#

---------------------- REMOTE SERVER ---------------------

You can use this in your CONNECTSTRING attribute to complete the string if you are uploading a file to a remote server and don't know the physical path of your database file:

Driver={Microsoft Access Driver (*.mdb)};Dbq=#ExpandPath('..\bettergig\cgi-bin\bettergig.mdb')#;Uid=Admin;Pwd=pass;

The complete <CFQUERY> tag for this string would look like this:

<CFQUERY NAME="rs"
 DBTYPE="dynamic"
 CONNECTSTRING="Driver={Microsoft Access Driver (*.mdb)};Dbq=#ExpandPath('..\bettergig\cgi-bin\bettergig.mdb')#;Uid=Admin;Pwd=pass;"
>
SELECT SeekId, SeekUsername, SeekPassword from Seekers
</CFQUERY>

The connection string could be built up in a variable to make it easier to read as well:

<CFSET rsUser="Uid=#form.username#;">
<CFSET rsPass="Pwd=#form.password#;">
<CFSET theDatabase="#ExpandPath('..\bettergig\cgi-bin\bettergig.mdb')#;">
<CFSET theString="Driver={Microsoft Access Driver (*.mdb)};Dbq=">
<CFSET theString=theString & theDatabase & rsUser & rsPass>

<CFQUERY NAME="rs"
 DBTYPE="dynamic"
 CONNECTSTRING="#theString#"
>
SELECT SeekId, SeekUsername, SeekPassword from Seekers
</CFQUERY>

K'Rgds
Anand
0
 
LVL 2

Expert Comment

by:fmedia
ID: 8072001
The problem is that doesn't work with CFMX, if you use cfmx you can have some troubles finding a solution, the only solution to that problem ( if using cfmx ) is to conect thru ADO, wich is very painfull and only works in the win platform ...

regards,
idss
0
 

Author Comment

by:McHack
ID: 8072077
Thanks  anandkp

I think this should work.
0

Featured Post

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

Turn A Profile Picture Into A Cartoon Using Photoshop And Illustrator This tutorial will teach you how to make a cartoon style image out of a regular picture. I have tried to keep the tutorial as simple as possible. I used Adobe CS4 for this tuto…
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…
The purpose of this video is to demonstrate how to reset a WordPress password if you are locked out and cannot reset the password. A typical use would be if you cannot access the email to which WordPress would send the password recovery email to…
The purpose of this video is to demonstrate how to Test the speed of a WordPress Website. Site Speed is an important metric of a site’s health. Slow site speed can result in viewers leaving your site quickly and not seeing your content. This…
Suggested Courses
Course of the Month9 days, 23 hours left to enroll

762 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