COM Example, Please...

[Trope]

My boss wants all references to our database locations removed from my ASP pages.

I think I need to use a .dll. If so, could someone please show me a short example to get the database location from this dll by passing a "code word from my asp page? Thanks in advance.

John

 

[chiph]

If you're using ADO, you typically need a "Connect String" to tell ADO who/where to connect. You can hard-code that in your ASP pages (or put it in an include file), but a better solution would be for your middle tier to look it up on it's own via a registry entry. It could then store it for later use in however you're storing other session-level information.

Chip H.

 

[dilettante]

I'm assuming you don't have any "business logic" tier between your ASP code and your DB tier. You're fetching recordsets right out of the database from ASP, right?

The approved way to do this under the old ASP (before ASP.Net) rules was to use a System Data Source Name - at least for ODBC connections.

Then you specify the DSN in your ASP page connection strings. This keeps all of the connection and security information OUT OF your ASP code, and not even in an include file somebody might grab.

'Course the rules may differ under ASP.Net, and for other (non-ODBC) connections you can't have DSNs (use the ODBC Administrator in Control Panel for ODBC connections).

Can't imagine how you'd secure an OLEDB connection string except to wrap it in a component or else wrap all your ADO method calls, a pain in the rear.

I might make a simple VB component with one property:

DBString[\code]

All it needs to do is return a constant string, which should get the security info out of your ASP pages and your boss off your back.

Or if you wanted to get fancier you could create a method:

[code]DBString(strCode as String) as String

The method could do some simplistic "security" check before returning the connection string value, or the parameter might return different strings for different kinds of connections, so you don't need to make a hundred different methods to do this simple little thing.

There has to be a better answer though.

What you DON'T want to do is keep making new connections with different user/passwords for different clients. You'll break the connection pooling in IIS (so performance goes to crap) and you'll have weird effects like Bill's updates actually happen with Mary's user ID.

Your ASP pages are the middle-tier, or at least pretty middling. All the database connections should be done in the same database security context.

Don't push user authentication back onto the database server - you'll regret it.

 

[wrbodine]

Are you using SQL Server? If so, you could create a VB ActiveX .dll, where you would have the database connection string, use it with command objects, etc. In VB you could have functions that call stored procedures or whatever.

Then in your .asp page you could reference the .dll and access these functions like so:

set obj = server.CreateObject("DllName.ClassName&quot
set rsID = obj.functionName(spName, paramString)

Let me know if you need more ideas on this...

 

[Trope]

I am using Access. Does that help ( or hinder? )

Thanks.

 

[wrbodine]

Actually, it would be fine. You would call a function in the VB .dll that looks something like this to do a select statement:

Public Function SelectRS(ByVal SpName As String, ByVal Params As String) As ADODB.Recordset

'And returns a disconnected recordset
Dim returnRS As ADODB.Recordset

On Error GoTo errorHandler
sqlstr = "Select * from tblData"

rs.Open sqlstr, DBConn, adOpenForwardOnly, adLockReadOnly

Set SelectRS = rs

Exit Function
errorHandler:
' On Error Resume Next, skips whole Select process
On Error Resume Next
End Function

Or, you could pass back a Variant array by using rs.GetRows. That might be faster.