Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 651
  • Last Modified:

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?
0
McHack
Asked:
McHack
  • 2
  • 2
1 Solution
 
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
 
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

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

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