Data source connection without ColdFusion Administrator

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?
McHackAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

anandkpCommented:
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
McHackAuthor Commented:
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
anandkpCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
fmediaCommented:
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
McHackAuthor Commented:
Thanks  anandkp

I think this should work.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Servers

From novice to tech pro — start learning today.

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.