#141185 - 2005-06-07 07:21 PM
Understanding the ODBC UDF
|
Huffinagle
Getting the hang of it
Registered: 2002-09-04
Posts: 75
Loc: McMinnville, OR, USA
|
Hi all,
I'd like to test the UDF for creating an SQL ODBC that Kent Dyer created (post number 83212)
I'm trying to understand which aspects of the function I need to customize in order for them to work in my environment.
In his function it says Quote:
EXAMPLE ODBCSQL('ODBC DATABASE NAME','SERVER','DATABASE')
Do I need to create associated entries for these? Example: "$NAME = dbsvr1"
I seem to remember reading someone say that UDFs should RARELY be edited, so please correct me if I'm wrong. As you can tell, the logic is a little confusing to me at this time.
Thanks, Matthew Code:
;FUNCTION ODBCSQL() ; ;ACTION Creates an ODBC Connection on the Client Desktop to a SQL Server Database ; ;AUTHOR Kent Dyer leptonator@hotmail.com ; ;CONTRIBUTORS ;Dixan Martinez ;Ref. - Create a DSN to access a SQL Server database with ODBC ;http://cwashington.netreach.net/depo/view.asp?Index=388&ScriptType=vbscript ; ;VERSION 1.3 - REMOVED UNEEDED code, Added option to create User DSN ; 1.2 Added in DIM Statement ; 1.1 ; Renamed to be ODBCSQL ; ;DATE CREATED 14-APRIL-2003 ; ;DATE REVISED 14-AUGUST-2004 ; ;KIXTART Minimum required Kixtart version 4.02 ; ;SYNTAX ODBCSQL($NAME,$SERVER,$DB,optional $user) ; ;PARAMETERS $NAME ; Name to be listed in the ODBC Connections in the Control Panel ; ; $SERVER ; Server you need to connect to ; ; $DB ; Database on $Server ; ; $USER ; Specified USER DSN ; ;RETURNS Nothing ; ;REMARKS User would need to be a Power User or better to run this Function. ; When creating a User DSN, should be able to run as a user of the machine. ; ;DEPENDENCIES SQL Server, KiXtart 4.02 + ; ;EXAMPLE ODBCSQL('ODBC DATABASE NAME','SERVER','DATABASE') ; ;IF NOT KEYEXIST('HKCU\SOFTWARE\ODBC\ODBC.INI\YOUR ODBC') ; ODBCSQL('ODBC DATABASE NAME','SERVER','DATABASE',1) ;ENDIF ; ;KIXTART BBS http://www.kixtart.org/ubbthreads/showflat.php?Cat=&Board=UBB12&Number=83212 FUNCTION ODBCSQL($NAME,$SERVER,$DB,optional $user) DIM $reg,$Title,$Driver,$RegEd,$RegEdPath,$RC IF NOT $user $reg='HKLM' ; System DSN ELSE $reg='HKCU' ; User DSN ENDIF $Title='SQL Server' $Driver=READVALUE('HKLM\SOFTWARE\ODBC\ODBCINST.INI\'+$Title,'Driver')
$RegEd=$reg+'\SOFTWARE\ODBC\ODBC.INI' $RegEdPath=$RegEd+'\'+$Name
IF NOT KEYEXIST($RegEdPath) $RC=ADDKEY($RegEdPath) $RC=WRITEVALUE($RegEdPath,'Database',$DB,'REG_SZ') $RC=WRITEVALUE($RegEdPath,'Driver',$Driver,'REG_SZ') $RC=WRITEVALUE($RegEdPath,'LastUser',@USERID,'REG_SZ') $RC=WRITEVALUE($RegEdPath,'Server',$Server,'REG_SZ') $RC=WRITEVALUE($RegEdPath,'Trusted_Connection','Yes','REG_SZ') $RC=WRITEVALUE($RegEd+'\ODBC Data Sources',$Name,$Title,'REG_SZ') ENDIF ENDFUNCTION
|
Top
|
|
|
|
#141186 - 2005-06-07 07:26 PM
Re: Understanding the ODBC UDF
|
Kdyer
KiX Supporter
Registered: 2001-01-03
Posts: 6241
Loc: Tigard, OR
|
Matt,
Here is how we are using this: Code:
ODBCSQL('Loan Data','PROD\PD_PROD','Users')
So, to break this down: 'Loan Data' - Name of the DSN that shows in the ODBC Connectors 'PROD\PD_PROD' - Name of Server \ Instance 'Users' - Database Name
This creates a system-wide DSN Called "Loan Data".
If you want to create this a user DSN, then do: Code:
ODBCSQL('Loan Data','PROD\PD_PROD','Users',1)
And this creates the DSN for the logged in user.
Kent
Edited by kdyer (2005-06-07 07:30 PM)
|
Top
|
|
|
|
#141187 - 2005-06-07 07:34 PM
Re: Understanding the ODBC UDF
|
Huffinagle
Getting the hang of it
Registered: 2002-09-04
Posts: 75
Loc: McMinnville, OR, USA
|
Thanks for the response Kent.
So where in your UDF it says, Quote:
FUNCTION ODBCSQL($NAME,$SERVER,$DB,optional $user)
I should:
1. replace the $NAME with The DSN name, 2. replace the $SERVER with the Name of Server \ Instance 3. replace the $DB with the Database name Is this correct? 4. What would the optional $USER be for? The User DSN option you mentioned in the UDF??
Thanks again Kent. You're amazing!
|
Top
|
|
|
|
#141189 - 2005-06-07 07:50 PM
Re: Understanding the ODBC UDF
|
Huffinagle
Getting the hang of it
Registered: 2002-09-04
Posts: 75
Loc: McMinnville, OR, USA
|
Would this be correct then? Code:
DIM $NAME, $SERVER, $DB, $user $NAME='Tempdb ODBC Connection to SVRDB4' $SERVER='SVRDB4' $DB='tempdb' $user=''
FUNCTION ODBCSQL($NAME,$SERVER,$DB,optional $user) DIM $reg,$Title,$Driver,$RegEd,$RegEdPath,$RC IF NOT $user $reg='HKLM' ; System DSN ELSE $reg='HKCU' ; User DSN ENDIF $Title='SQL Server' $Driver=READVALUE('HKLM\SOFTWARE\ODBC\ODBCINST.INI\'+$Title,'Driver')
$RegEd=$reg+'\SOFTWARE\ODBC\ODBC.INI' $RegEdPath=$RegEd+'\'+$Name
IF NOT KEYEXIST($RegEdPath) $RC=ADDKEY($RegEdPath) $RC=WRITEVALUE($RegEdPath,'Database',$DB,'REG_SZ') $RC=WRITEVALUE($RegEdPath,'Driver',$Driver,'REG_SZ') $RC=WRITEVALUE($RegEdPath,'LastUser',@USERID,'REG_SZ') $RC=WRITEVALUE($RegEdPath,'Server',$Server,'REG_SZ') $RC=WRITEVALUE($RegEdPath,'Trusted_Connection','Yes','REG_SZ') $RC=WRITEVALUE($RegEd+'\ODBC Data Sources',$Name,$Title,'REG_SZ') ENDIF ENDFUNCTION
|
Top
|
|
|
|
#141193 - 2005-06-07 08:40 PM
Re: Understanding the ODBC UDF
|
Chris S.
MM club member
Registered: 2002-03-18
Posts: 2368
Loc: Earth
|
|
Top
|
|
|
|
#141195 - 2005-06-08 12:59 AM
Re: Understanding the ODBC UDF
|
Huffinagle
Getting the hang of it
Registered: 2002-09-04
Posts: 75
Loc: McMinnville, OR, USA
|
I modified the aforementioned line.
After running the FUNCTION I receive the error message,
Quote:
ERROR : error in parameterdefinition! Script: \\SERVER2\NETLOGON\odbc.kix Line : 56
Line#56 looks like this, Code:
FUNCTION ODBCSQL('Tempdb ODBC SVRDB4','SVRDB4','tempdb')
Are there any special permissions that need to be granted on the SQL server? I have successfully created the ODBC manually using Windows Authentication.
|
Top
|
|
|
|
#141196 - 2005-06-08 01:07 AM
Re: Understanding the ODBC UDF
|
Kdyer
KiX Supporter
Registered: 2001-01-03
Posts: 6241
Loc: Tigard, OR
|
If you can create the SQL Connections in the Control Panel, you should be abe to create them using KiX. When you do the test setup in the Control Panel, can you do the "test connection"? Tempdb is a special database, and you may need to be an Admin to get there.
BTW, you are doing it wrong - you need to call up the function this way: Code:
ODBCSQL('Tempdb ODBC SVRDB4','SVRDB4','tempdb')
So...
The whole thing looks like: Code:
; -- Create the ODBC Connector ODBCSQL('Tempdb ODBC SVRDB4','SVRDB4','tempdb')
; -- Leave the function as is FUNCTION ODBCSQL($NAME,$SERVER,$DB,optional $user) DIM $reg,$Title,$Driver,$RegEd,$RegEdPath,$RC IF NOT $user $reg='HKLM' ; System DSN ELSE $reg='HKCU' ; User DSN ENDIF $Title='SQL Server' $Driver=READVALUE('HKLM\SOFTWARE\ODBC\ODBCINST.INI\'+$Title,'Driver')
$RegEd=$reg+'\SOFTWARE\ODBC\ODBC.INI' $RegEdPath=$RegEd+'\'+$Name
IF NOT KEYEXIST($RegEdPath) $RC=ADDKEY($RegEdPath) $RC=WRITEVALUE($RegEdPath,'Database',$DB,'REG_SZ') $RC=WRITEVALUE($RegEdPath,'Driver',$Driver,'REG_SZ') $RC=WRITEVALUE($RegEdPath,'LastUser',@USERID,'REG_SZ') $RC=WRITEVALUE($RegEdPath,'Server',$Server,'REG_SZ') $RC=WRITEVALUE($RegEdPath,'Trusted_Connection','Yes','REG_SZ') $RC=WRITEVALUE($RegEd+'\ODBC Data Sources',$Name,$Title,'REG_SZ') ENDIF ENDFUNCTION
Kent
Edited by kdyer (2005-06-08 01:15 AM)
|
Top
|
|
|
|
#141200 - 2005-06-09 08:23 AM
Re: Understanding the ODBC UDF
|
Kdyer
KiX Supporter
Registered: 2001-01-03
Posts: 6241
Loc: Tigard, OR
|
Quote:
3. Is there a way to script delete ODBC DSNs?
Look at the DELODBC() UDF to remove ODBC connectors.
Kent
|
Top
|
|
|
|
#141202 - 2005-06-10 06:50 AM
Re: Understanding the ODBC UDF
|
Kdyer
KiX Supporter
Registered: 2001-01-03
Posts: 6241
Loc: Tigard, OR
|
Please don't mis-understand me as we have a license for ScriptLogic and use it.
KiXtart is also pretty easy to use as well, but you have to build your constructs and leverage the UDFs that have been written. Ruud, the Developer of KiXtart also provides several examples in the KiXtart download, which you can play around with.
So, what is my history with KiXtart? Much like many of the people here, it was dropped in my lap when the guy who was working with it left the company. I have had some VB, ASP, C++ experience that KiXtart fell right into this mix. However, it has taken time to bring our script up-to-date. The one thing that I have always found here is an answer, it may not be the prettiest answer, but timely.
Matt - If you choose to stay here and work with us or if you choose to use a ScriptLogic solution, I would not be offended in any way. You have my phone number and I would welcome a call with KiXtart or even ScriptLogic. And, there are no "dumb" questions either.
Thanks,
Kent
|
Top
|
|
|
|
#141203 - 2005-06-10 07:35 PM
Re: Understanding the ODBC UDF
|
Huffinagle
Getting the hang of it
Registered: 2002-09-04
Posts: 75
Loc: McMinnville, OR, USA
|
Thanks for the clarification/opinions. I am very new to discussion boards that focus on such a specialized application like Kixtart. From what I have observed it looks like a close-knit community of users who are familiar with each other and have been programming for quite some time. Some of the questions I ask certainly do FEEL "dumb" to me, that's because I see the level of expertise in all the "regulars" and I don't want to necesarily waste other users time with basic questions.
Perhaps there is a resource like a glossary of programming concepts and terms that I can use to get some background program language understanding. Have any recommendations?
For example, in the kix2001.doc file there aren't any definitions for $rc. I see this variable/switch used all the time but don't understand what it is for and how it should be used.
My main background/expertise is LAN and WAN security/design/implementation/management. I'm seeing a great need for automation in my networks and so want to use Kixtart as much as I can since it is already a part of our logon script process. What I have been able to accomplish with kixtart over the past 2 months has been awesome for our Desktop Support and Application Support staff.
Perhaps at some point I'll be able to contribute back to the kixtart board. You all have certainly contributed to the success of my network.
ScriptLogic will ultimately be a ROI decision based on time savings and crosstrainability with my Desktop Support person. Right now though, I'll keep writing my own scripts.
|
Top
|
|
|
|
Moderator: Jochen, Allen, Radimus, Glenn Barnas, ShaneEP, Ruud van Velsen, Arend_, Mart
|
0 registered
and 346 anonymous users online.
|
|
|