Subscribe Bookmark RSS Feed

DSN Intercept Function for Open Database

thechadd

Community Trekker

Joined:

Jun 23, 2011

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.

 

/*
Function
to intercept user's DSN
selection through ODBC interface for database queries.
This is useful for scripts that
run Open Database() multiple times on the same database.
 
Opens user's Windows ODBC to allow them to select a DSN.
 
Returns the DSN string to query the database. If fail, it pops up
a window indicating the error, and returns empty string.
 




 Arguments:

 
 _test_query_:
A very simple query that will return a SQL error if

 
      
user
selected wrong DSN.  It
will return a
results table if there is no error.

 
 _database_:
The name of the database for the error window.

 Local
Variables:

 
 _DSN_test_:
temporary table that will contain the table variable with DSN

 
 _DSN_string_:
string extracted from Database query results Table Variable "JSL"

 Example
usage:

 
 DSN_setup("SELECT
TOP 1 * FROM dbo.Dim_DiePV","NSGTestDW");

 */
 
 
::DSN_setup = Function( 
 //Arguments
 
	{_test_query_, _database_}, 
 
 //Locals
	{_DSN_test_, _DSN_string_},
	_DSN_test_ = 
 
	Open Database( , _test_query_ );
 
  
	If( Char( _DSN_test_ ) == ".",
		New Window( "Script Error",
			<<Modal,
			Text Box(
				"ERROR! ODBC Source does not seem valid. Re-run script and try again," ||
				"or make sure your ODBC is set up for " || _database_
			);
       
		);
		"";
	, 
 
 //return empty string
 
		_DSN_string_ = _DSN_test_ << get table variable( "JSL" );
 
		Close( _DSN_test_, no save );
		Word( 2, _DSN_string_, "\!"" );
  
 //return
		DSN string;
	)

	;
);
2 REPLIES
JensRiege

New Contributor

Joined:

Sep 21, 2017

HTML code does not appear to be functioning correctly... Cannot see illustration of solution
Jeff_Perkinson

Community Manager

Joined:

Jun 23, 2011

Thanks for pointing that out, @JensRiege.

 

I've cleaned it up.

-Jeff