AdobeStock_455007340

Programmatically List (and Verify) Datasources

I wrote this function for someone who needed to programmatically obtain a list of datasources and whether or not they could be verified. I’ve discussed the use of the Service Factory before, but this is another nice example, and so I thought it worth sharing.
































GetDataSources() returns a query containing three columns, “name” is the datasource name, “driver” is the driver used, and “verified” returns TRUE or FALSE depending on whether or not the datasources could be verified.
A sample invocation could be:

5 responses to “Programmatically List (and Verify) Datasources”

  1. VinyGrr Avatar
    VinyGrr

    Great!!! It’s exactly what i need. (since i use mx server)
    But i got an answer…
    I created a dsn on a excel file in the admin tool then i created a odbcsocket on this dsn to access my excel file.
    But when i try to overwrite more than one time this file. I got an error… may be due to the lock of the excel file (previously i use cfusion_disable_dbconnections)…first i think this can be unlocked thanks the verifydsn function but i think that didn’t work. (may be due to the real dsn from admin tool and not from the odbcsocket ?)
    my excel file seems to still be locked….arghhhhh
    any idea oh great cfm guru?;-)

  2. Grant Szabo Avatar
    Grant Szabo

    Ben, Is there some way to do this with CF5?

  3. todd sharp Avatar
    todd sharp

    Ben:
    Is there a list of the valid driver types anywhere that I can get my hands on (I.E. mssqlserver, msaccessjet, etc)?

  4. Glen Avatar
    Glen

    Hey Ben,
    It seems the verifyDatasource(‘dsn_here’) from the CF Factory opens a new connection every time and doesn’t close it in a timely fashion. It doesn’t seem to follow the DSN properties i.e. restrict to 3 connections and maintain connections. Surely this is a bug – but as it is an undocumented feature I guess Adobe wouldn’t treat it as such?
    A bit of background –
    We have three servers fronted by a hardware load balancer (LB) – the LB performs health checks against a template on each server and I thought I would take advantage of this and make every request run a couple of CF health checks one of the health checks is to ensure the datasources are okay (verify them etc). But every time it performed the check it opened a new connection and before we knew it we had maxed out our connections to our oracle database (250 connections limit).

  5. Michael Beveridge Avatar
    Michael Beveridge

    Hi Ben,
    Is there anyway in CFMX 7.02 to DISABLE access to the ColdFusion.serviceFactory object to prevent hacks to retrieve
    datasource information?

Leave a Reply