Creating a System DSN with Coldfusion

If you read my last post, you know I’m working with an old Foxpro DB. One of the problems I’ve encountered is that the software I’m trying to interface with  (UPS Connect) takes the current day’s data and archives it into folders based on the month. It has a folder structure similar to this

ups root>

shipment.dbf

Oct08>

  • S100108.dbf
  • S100208.dbf
  • S100308.dbf etc

Now it’s not a problem that the individual days are in separate dbf files as the Foxpro driver takes care of that as long as we have a system DSN set up.  However, since every month needs a new system dsn (to connect to the month’s folder) I needed a way to pro grammatically create system DSN’s via coldfusion. Turns out it is pretty simple if you have access to the cfregistry tag.

(Standard warning about fiddling with the registry… back up before doing any of this and I’m not responsible for any damage however caused yada yada)

Run the following code it will create the system dsn for you. (I didn’t test it but it’s possible that cfservice needs to be running under admin permissions)

<!— create the new system DSN name.
For simplicity it will also be the key location and the file location
UPS Connect archives files in mmmyy format
—>
<cfset newDSN = left(MONTHASSTRING(datepart(“m”,now())), 3) & right(year(now()),2)>

<!— Create a New Key —>
<CFREGISTRY ACTION=”SET”
BRANCH=”HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI”
ENTRY=”#newDSN#”
tYPE=”KEY”
VALUE=”#newDSN#”    >

<!— Create String values for the System DSN under that key —>
<CFREGISTRY ACTION=”SET”
BRANCH=”HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\#newDSN#\”
ENTRY=”BackgroundFetch”
tYPE=”string”
VALUE=”No”    >

<CFREGISTRY ACTION=”SET”
BRANCH=”HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\#newDSN#”
ENTRY=”Collate”
tYPE=”string”
VALUE=”Machine”    >

<CFREGISTRY ACTION=”SET”
BRANCH=”HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\#newDSN#”
ENTRY=”Driver”
tYPE=”string”
VALUE=”C:\WINDOWS\system32\vfpodbc.dll”    >

<CFREGISTRY ACTION=”SET”
BRANCH=”HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\#newDSN#”
ENTRY=”Exclusive”
tYPE=”string”
VALUE=”No”    >

<CFREGISTRY ACTION=”SET”
BRANCH=”HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\#newDSN#”
ENTRY=”SetNoCountOn”
tYPE=”string”
VALUE=”No”    >

<CFREGISTRY ACTION=”SET”
BRANCH=”HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\#newDSN#”
ENTRY=”SourceDB”
tYPE=”string”
VALUE=”\\192.168.111.6\ups\Oct08″    >

<CFREGISTRY ACTION=”SET”
BRANCH=”HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\#newDSN#”
ENTRY=”Description”
tYPE=”string”
VALUE=”SystemDSN for Oct08″    >

<CFREGISTRY ACTION=”SET”
BRANCH=”HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\#newDSN#”
ENTRY=”SourceType”
tYPE=”string”
VALUE=”DBF”    >

This sets all the correct settings for the UPS system DSN.

For my purposes, I don’t need to keep connections to last month’s folder so there’s no reason to keep the old system DSN. Fortunately, there is an easy way to get rid of old registry entries such as system DSN’s.

<!— once in new month find last months mmmyy by using dateadd -1—>

<cfset oldDSN = left(MONTHASSTRING(datepart(“m”,dateadd(“m”,-1,now()))), 3) & right(year(now()),2)>

<CFREGISTRY ACTION=”delete”
BRANCH=”HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\#oldDSN#”>

Add the delete code to the create new code, run it all via cfschedule on the first day of each month and you’d need no user intervention to maintain your ever changing System DNS’s

NOTE: This code does NOT create the datasource for coldfusion, only the system DSN so we can set up the datasource via the CF ODBC Socket. For that we have to either set it up with CFAdmin or since we want to automate the process, use the admin API. Check my next post for that step.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: