DSN-less database connection through COM 1

[iqof188]

A couple of weeks ago I posted an example on how to make a DSNless connection to a database in one of the threads. I didn't test the code, and once I did I found some things were imperfect.
So, did you ever dream about creating a DSNless connection to a database (like you can do with ASP)? Well, neither did I, but it's fun to see what you can do with a little CFSCRIPT and ColdFusion's COM support.
This should do it for all you brave programmers

<cfobject type=&quot;COM&quot;
name=&quot;MyConn&quot;
class=&quot;ADODB.Connection&quot;
action=&quot;CREATE&quot;>

<CFSCRIPT>
DSNtemp = &quot;DRIVER={Microsoft Access Driver (*.mdb)};&quot;;
DSNtemp = DSNtemp &amp; &quot;DBQ=d:\data\mydb.mdb&quot;;
MyConn.Open(&quot;#DSNtemp#&quot;, &quot;&quot;, &quot;&quot;, -1);

SQL = &quot;SELECT * FROM MyTable&quot;;

MyRecordset = MyConn.Execute(SQL, 0, 8);

MyFields = MyRecordset.Fields;

/* Make a variable for recordcount */

RecordCount = 0;
while(NOT MyRecordset.EOF){
RecordCount = RecordCount + 1;
MyRecordset.MoveNext();
}

Columns = &quot;&quot;;
</CFSCRIPT>

<!--- Get the Column Name from the MyFields collections --->
<CFLOOP COLLECTION=&quot;#MyFields#&quot; ITEM=&quot;this&quot;>
<CFSET Columns = ListAppend(Columns, this.Name)>
</CFLOOP>

<!--- Populate a newly made query with columns --->
<CFSET MyRecords = QueryNew(Columns)>

<!--- Add 'RecordCount' rows to hold the data --->
<CFSET QueryAddRow(MyRecords, RecordCount)>

<!--- Go to the first row of the recordset --->
<CFSET MyRecordset.MoveFirst()>

<!--- And populate the recordset... --->
<CFLOOP FROM=&quot;1&quot; TO=&quot;#RecordCount#&quot; INDEX=&quot;i&quot;>
<CFLOOP COLLECTION=&quot;#MyFields#&quot; ITEM=&quot;this&quot;>
<CFSET QuerySetCell(MyRecords, Trim(this.name), this.value, i)>
</CFLOOP>
<CFSET MyRecordset.MoveNext()>
</CFLOOP>

<!--- Ouput the data like this: --->
<CFOUTPUT QUERY=&quot;MyRecords&quot;>
<TR>
<CFLOOP LIST=&quot;#MyRecords.ColumnList#&quot; INDEX=&quot;this&quot;>
<TD>#Evaluate(this)#</TD>
</CFLOOP>
</TR>
</CFOUTPUT>
</TABLE>

<!--- Close the ODBC connection (like you do in ASP)--->
<CFSET MyConn.Close()>


Have fun with it, but remember querying databases like this is much slower than the traditional CF way.

<webguru>iqof188</webguru>

 

[jalbao]

wow that's very cool iqof188! I've been messin around w/ your code trying to make it work w/ an sql server driver instead of ms access.... no luck for me.

do you have any tips on how to execute a DSN-less database connection through COM for sql server?

 

[iqof188]

albao, thanks for reacting, I thought all brave programmers were sleeping ;-). I haven't tested the following code, but give it a try:

<cfobject type=&quot;COM&quot;
name=&quot;MyConn&quot;
class=&quot;ADODB.Connection&quot;
action=&quot;CREATE&quot;>

<CFSCRIPT>
DSN = &quot;DRIVER={SQL Server};SERVER=ServerName;UID=USER;PWD=password;DATABASE=databasename&quot;;
MyConn.Open(&quot;#DSN#&quot;, &quot;&quot;, &quot;&quot;, -1);

SQL = &quot;SELECT * FROM MyTable&quot;;

MyRecordset = MyConn.Execute(SQL, 0, 8);

MyFields = MyRecordset.Fields;

/* Make a variable for recordcount */

RecordCount = 0;
while(NOT MyRecordset.EOF){
RecordCount = RecordCount + 1;
MyRecordset.MoveNext();
}

Columns = &quot;&quot;;
</CFSCRIPT>

The rest should be the same as for Access. Please let me know whether it works. Good luck!


<webguru>iqof188</webguru>

 

[jalbao]

iqof188 - gonna test your code, i'll be lettin you know how it goes...

 

[jalbao]

iqof188 - i've been having problems connecting to the SQL Server Database. When I run the script i get an error that states Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database requested in login 'MDR_Sql.mdf'. Login fails.

i'm confused 'cuz when i run the query -

<cfquery name=&quot;qryTest&quot;
datasource=&quot;MDR_Sql&quot;
username=&quot;&quot;
password=&quot;&quot;>
Select *
FROM tblCompanyOEM;
</cfquery>

i get a result in my output. Just to clarify, i registered the MDR_Sql database in the ODBC applet in the control panel. which, of course, is what i am trying to avoid. the only reason i did so, is to see if i connect to the db.

i do not know if it's your code or some security issue in sql server that i am overlooking -

any ideas flowin that may solve this prob?

thanks

 

[CFHub]

Cool script. It works great!

 

[iqof188]

Thanks cfhub ! albao, I assume it is a security issue, since the error message states &quot;Cannot open database requested in login 'MDR_Sql.mdf'. Login fails.&quot;. Check if permissions on your SQL Server DB are correct.
If you are sure the permissions are OK, please let me know what webserver you work with. If it's IIS, try making a DSN-less connection with ASP (yeah, for once use ASP, just to test ;-) ). Dont't know the code for that, let me know and I'll post it. Good luck!



<webguru>iqof188</webguru>

 

[jalbao]

iqof188-
I am using IIS - I do believe that you are right in saying that it was a security issue.... I spent a few hours attempting to resolve the security issue to no avail. It finally dawned on my that my NT password was blank - ie. i didn't use a password to login. In my initial install of sql server, i forgot to allow blank passwords. After i changed my NT password to a nonblank and then reinstalled sqlserver... your code works now!

thanks for all the help - 5 stars to you!

 

[jalbao]

iqof188 - can you explain briefly each line of code that you came up with for the dsn-less connection (sqlserver)?

if you don't have time for that, maybe you can just answer this question... Will I have to run your code every time that i want to query the database.. or can i some how utilize the opening part of your code which establishes the db connection and then use normal <cfquery> tags to run my sql statements throughout my application?

 

[qqq]

qof188
I have the same question as albao: is it possible to establishes the db connection and then use normal <cfquery> tags to run my sql statements throughout my application?
regards

 

[Guest_imported]

Actually that script inspired me to go look at DAO/ADO more closely. You can find out lots more about this technology at microsoft.

Stop by CFHub.com and grab the adocfscript &quot;define&quot; file 'cause you will need it if you want to follow along with the microsoft tutorials.

http://cfhub.com/advanced/ado/

 

[iqof188]

qqq and albao, here's the (late) answer:
unfortunately it is not possible to perform a cfquery, since the db-connection is through COM. You can though create a query like cfhub explained on his site (nice work cfhub, but where's the credits for the ol' iq ).
albao, you wrote: &quot;Will I have to run your code every time that i want to query the database&quot;, my answer is:
yes you will, unfortunately. Remember guys, it's much easier and much faster to query db's the CF way, this was just an example of COM and CF working together. Have fun with it though! Best regards...







<webguru>iqof188</webguru>

 

[CFHub]

Love to give credit, but iqof188 is a little vague

If you send me some info (name maybe?) I will throw it up on the site. Post here (I visit often) or send it straight to feedback@cfhub.com.

(My name is Joseph Thompson)

TY again : )