Subscribe Bookmark RSS Feed

DSN Intercept Function for Open Database

thechadd

Community Trekker

Joined:

Jun 23, 2011


style="color: black;">I have a script that runs multiple
queries; so my customers had to select the DSN every time “Open
Database()” command was called.  So I wrote a function to
solve this problem.  It intercepts the DSN string when a user
selects it, and saves it to a variable.  Then my script can
access the DSN later without the user having to re-select.


style="color: black;">


I just thought I would share
:).




Cheers,

style="color: black;">
Chadd



/*





style="font-size: 10pt; font-family: "Courier New"; color: green;">

style="font-size: 10pt; font-family: "Courier New"; color: green;">Function
to intercept user's DSN
selection through ODBC interface for database queries.





style="font-size: 10pt; font-family: "Courier New"; color: green;">This
is useful for scripts that
run Open Database() multiple times on the same





style="font-size: 10pt; font-family: "Courier New"; color: green;">database.





style="font-size: 10pt; font-family: "Courier New"; color: green;">

 





style="font-size: 10pt; font-family: "Courier New"; color: green;">Opens
user's Windows ODBC to
allow them to select a DSN.





style="font-size: 10pt; font-family: "Courier New"; color: green;">
style="">     
Returns
the DSN string to query the database. If fail, it pops up





style="font-size: 10pt; font-family: "Courier New"; color: green;">
style="">     
a
window indicating the error, and returns empty string.





style="font-size: 10pt; font-family: "Courier New"; color: green;">Arguments:





style="font-size: 10pt; font-family: "Courier New"; color: green;">
style="">     
_test_query_:
A very simple query that will return a SQL error if





style="font-size: 10pt; font-family: "Courier New"; color: green;">
style="">           
user
selected wrong DSN.  It
will return a
results table if there is no error.





style="font-size: 10pt; font-family: "Courier New"; color: green;">
style="">     
_database_:
The name of the database for the error window.





style="font-size: 10pt; font-family: "Courier New"; color: green;">Local
Variables:





style="font-size: 10pt; font-family: "Courier New"; color: green;">
style="">     
_DSN_test_:
temporary table that will contain the table variable with DSN





style="font-size: 10pt; font-family: "Courier New"; color: green;">
style="">     
_DSN_string_:
string extracted from Database query results Table Variable "JSL"





style="font-size: 10pt; font-family: "Courier New"; color: green;">Example
usage:





style="font-size: 10pt; font-family: "Courier New"; color: green;">
style="">     
DSN_setup("SELECT
TOP 1 * FROM dbo.Dim_DiePV","NSGTestDW");





style="font-size: 10pt; font-family: "Courier New"; color: green;">*/

style="font-size: 10pt; font-family: "Courier New"; color: black;">





style="font-size: 10pt; font-family: "Courier New"; color: black;">

 





style="font-size: 10pt; font-family: "Courier New"; color: navy;">::

style="font-size: 10pt; font-family: "Courier New"; color: black;">DSN_setup

style="font-size: 10pt; font-family: "Courier New"; color: navy;">=

style="font-size: 10pt; font-family: "Courier New"; color: black;">

style="font-size: 10pt; font-family: "Courier New"; color: rgb(0, 0, 221);">Function

style="font-size: 10pt; font-family: "Courier New"; color: black;">(

style="font-size: 10pt; font-family: "Courier New"; color: black;">





style="font-size: 10pt; font-family: "Courier New"; color: black;">
style="">     

style="font-size: 10pt; font-family: "Courier New"; color: green;">//
Arguments

style="font-size: 10pt; font-family: "Courier New"; color: black;">





style="font-size: 10pt; font-family: "Courier New"; color: black;">
style="">     
{





style="font-size: 10pt; font-family: "Courier New"; color: black;">
style="">           
_test_query_

style="font-size: 10pt; font-family: "Courier New"; color: navy;">,

style="font-size: 10pt; font-family: "Courier New"; color: black;">





style="font-size: 10pt; font-family: "Courier New"; color: black;">
style="">           
_database_





style="font-size: 10pt; font-family: "Courier New"; color: black;">
style="">     
}

style="font-size: 10pt; font-family: "Courier New"; color: navy;">,

style="font-size: 10pt; font-family: "Courier New"; color: black;">





style="font-size: 10pt; font-family: "Courier New"; color: black;">
style="">     

style="font-size: 10pt; font-family: "Courier New"; color: green;">//
Locals

style="font-size: 10pt; font-family: "Courier New"; color: black;">





style="font-size: 10pt; font-family: "Courier New"; color: black;">
style="">     
{





style="font-size: 10pt; font-family: "Courier New"; color: black;">
style="">           
_DSN_test_

style="font-size: 10pt; font-family: "Courier New"; color: navy;">,

style="font-size: 10pt; font-family: "Courier New"; color: black;">





style="font-size: 10pt; font-family: "Courier New"; color: black;">
style="">           
_DSN_string_



style="font-size: 10pt; font-family: "Courier New"; color: black;">
style="">



style="font-size: 10pt; font-family: "Courier New"; color: black;">
style="">     
}

style="font-size: 10pt; font-family: "Courier New"; color: navy;">,

style="font-size: 10pt; font-family: "Courier New"; color: black;">





style="font-size: 10pt; font-family: "Courier New"; color: black;">
style="">     
_DSN_test_

style="font-size: 10pt; font-family: "Courier New"; color: navy;">=

style="font-size: 10pt; font-family: "Courier New"; color: black;">

style="font-size: 10pt; font-family: "Courier New"; color: rgb(0, 0, 221);">Open
Database

style="font-size: 10pt; font-family: "Courier New"; color: black;">(

style="font-size: 10pt; font-family: "Courier New"; color: navy;">,

style="font-size: 10pt; font-family: "Courier New"; color: black;">_test_query_)

style="font-size: 10pt; font-family: "Courier New"; color: navy;">;

style="font-size: 10pt; font-family: "Courier New"; color: black;">





style="font-size: 10pt; font-family: "Courier New"; color: black;">
style="">     

style="font-size: 10pt; font-family: "Courier New"; color: rgb(0, 0, 221);">if

style="font-size: 10pt; font-family: "Courier New"; color: black;">(

style="font-size: 10pt; font-family: "Courier New"; color: rgb(0, 0, 221);">char

style="font-size: 10pt; font-family: "Courier New"; color: black;">(

style="font-size: 10pt; font-family: "Courier New"; color: black;">_DSN_test_)

style="font-size: 10pt; font-family: "Courier New"; color: navy;">==

style="font-size: 10pt; font-family: "Courier New"; color: black;">

style="font-size: 10pt; font-family: "Courier New"; color: purple;">"."

style="font-size: 10pt; font-family: "Courier New"; color: navy;">,

style="font-size: 10pt; font-family: "Courier New"; color: black;">





style="font-size: 10pt; font-family: "Courier New"; color: black;">
style="">           

style="font-size: 10pt; font-family: "Courier New"; color: rgb(0, 0, 221);">New
Window

style="font-size: 10pt; font-family: "Courier New"; color: black;">(

style="font-size: 10pt; font-family: "Courier New"; color: purple;">"Script
Error"

style="font-size: 10pt; font-family: "Courier New"; color: navy;">,

style="font-size: 10pt; font-family: "Courier New"; color: black;">





style="font-size: 10pt; font-family: "Courier New"; color: black;">
style="">                 

style="font-size: 10pt; font-family: "Courier New"; color: navy;"><<

style="font-size: 10pt; font-family: "Courier New"; color: black;">
Modal

style="font-size: 10pt; font-family: "Courier New"; color: navy;">,

style="font-size: 10pt; font-family: "Courier New"; color: black;">





style="font-size: 10pt; font-family: "Courier New"; color: black;">
style="">                 

style="font-size: 10pt; font-family: "Courier New"; color: rgb(0, 0, 221);">text
box

style="font-size: 10pt; font-family: "Courier New"; color: black;">(

style="font-size: 10pt; font-family: "Courier New"; color: purple;">"ERROR!
ODBC Source does not seem valid. Re-run script and try again,"

style="font-size: 10pt; font-family: "Courier New"; color: black;">





style="font-size: 10pt; font-family: "Courier New"; color: black;">
style="">                 

style="font-size: 10pt; font-family: "Courier New"; color: navy;">||

style="font-size: 10pt; font-family: "Courier New"; color: black;">

style="font-size: 10pt; font-family: "Courier New"; color: purple;">"
or make
sure your ODBC is set up for "

style="font-size: 10pt; font-family: "Courier New"; color: black;">

style="font-size: 10pt; font-family: "Courier New"; color: navy;">||

style="font-size: 10pt; font-family: "Courier New"; color: black;">
_database_)

style="font-size: 10pt; font-family: "Courier New"; color: navy;">;

style="font-size: 10pt; font-family: "Courier New"; color: black;">





style="font-size: 10pt; font-family: "Courier New"; color: black;">
style="">           
)

style="font-size: 10pt; font-family: "Courier New"; color: navy;">;

style="font-size: 10pt; font-family: "Courier New"; color: black;">





style="font-size: 10pt; font-family: "Courier New"; color: black;">
style="">           

style="font-size: 10pt; font-family: "Courier New"; color: purple;">""

style="font-size: 10pt; font-family: "Courier New"; color: navy;">,

style="font-size: 10pt; font-family: "Courier New"; color: black;">

style="font-size: 10pt; font-family: "Courier New"; color: green;">//return
empty
string

style="font-size: 10pt; font-family: "Courier New"; color: black;">





style="font-size: 10pt; font-family: "Courier New"; color: black;">
style="">     
_DSN_string_

style="font-size: 10pt; font-family: "Courier New"; color: navy;">=

style="font-size: 10pt; font-family: "Courier New"; color: black;">
_DSN_test_

style="font-size: 10pt; font-family: "Courier New"; color: navy;"><<

style="font-size: 10pt; font-family: "Courier New"; color: black;">
get table
variable(

style="font-size: 10pt; font-family: "Courier New"; color: purple;">"JSL"

style="font-size: 10pt; font-family: "Courier New"; color: black;">)

style="font-size: 10pt; font-family: "Courier New"; color: navy;">;

style="font-size: 10pt; font-family: "Courier New"; color: black;">





style="font-size: 10pt; font-family: "Courier New"; color: black;">
style="">     

style="font-size: 10pt; font-family: "Courier New"; color: rgb(0, 0, 221);">close

style="font-size: 10pt; font-family: "Courier New"; color: black;">(

style="font-size: 10pt; font-family: "Courier New"; color: black;">_DSN_test_

style="font-size: 10pt; font-family: "Courier New"; color: navy;">,

style="font-size: 10pt; font-family: "Courier New"; color: black;">
no save)

style="font-size: 10pt; font-family: "Courier New"; color: navy;">;

style="font-size: 10pt; font-family: "Courier New"; color: black;">





style="font-size: 10pt; font-family: "Courier New"; color: black;">
style="">     

style="font-size: 10pt; font-family: "Courier New"; color: rgb(0, 0, 221);">word

style="font-size: 10pt; font-family: "Courier New"; color: black;">(

style="font-size: 10pt; font-family: "Courier New"; color: teal;">2

style="font-size: 10pt; font-family: "Courier New"; color: navy;">,

style="font-size: 10pt; font-family: "Courier New"; color: black;">_DSN_string_

style="font-size: 10pt; font-family: "Courier New"; color: navy;">,

style="font-size: 10pt; font-family: "Courier New"; color: purple;">"\!""

style="font-size: 10pt; font-family: "Courier New"; color: black;">)

style="font-size: 10pt; font-family: "Courier New"; color: navy;">;

style="font-size: 10pt; font-family: "Courier New"; color: black;">
style=""> 

style="font-size: 10pt; font-family: "Courier New"; color: green;">//return
DSN string

style="font-size: 10pt; font-family: "Courier New"; color: black;">





style="font-size: 10pt; font-family: "Courier New"; color: black;">
style="">     
)





style="font-size: 10pt; line-height: 115%; font-family: "Courier New"; color: black;">)

style="font-size: 10pt; line-height: 115%; font-family: "Courier New"; color: navy;">;