Page 1 of 2 12>
Topic Options
#141185 - 2005-06-07 07:21 PM Understanding the ODBC UDF
Huffinagle Offline
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 Offline
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)
_________________________
Utilize these resources:
UDFs (Full List)
KiXtart FAQ & How to's

Top
#141187 - 2005-06-07 07:34 PM Re: Understanding the ODBC UDF
Huffinagle Offline
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
#141188 - 2005-06-07 07:37 PM Re: Understanding the ODBC UDF
Lonkero Administrator Offline
KiX Master Guru
*****

Registered: 2001-06-05
Posts: 22346
Loc: OK
no, nothing is to be replaced in the udf, but in the line where you call it.
_________________________
!

download KiXnet

Top
#141189 - 2005-06-07 07:50 PM Re: Understanding the ODBC UDF
Huffinagle Offline
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
#141190 - 2005-06-07 08:04 PM Re: Understanding the ODBC UDF
Les Offline
KiX Master
*****

Registered: 2001-06-11
Posts: 12734
Loc: fortfrances.on.ca
You are still missing the line that uses the UDF.
_________________________
Give a man a fish and he will be back for more. Slap him with a fish and he will go away forever.

Top
#141191 - 2005-06-07 08:14 PM Re: Understanding the ODBC UDF
NTDOC Administrator Offline
Administrator
*****

Registered: 2000-07-28
Posts: 11623
Loc: CA
jut run the line like this
 
ODBCSQL('Tempdb ODBC Connection to SVRDB4','SVRDB4','tempdb')

Top
#141192 - 2005-06-07 08:25 PM Re: Understanding the ODBC UDF
Huffinagle Offline
Getting the hang of it
*****

Registered: 2002-09-04
Posts: 75
Loc: McMinnville, OR, USA
Apparently I don't understand how UDFs work. Can you tell me what line is missing?
Top
#141193 - 2005-06-07 08:40 PM Re: Understanding the ODBC UDF
Chris S. Offline
MM club member
*****

Registered: 2002-03-18
Posts: 2368
Loc: Earth
See the FAQ: How to use UDF's
Top
#141194 - 2005-06-07 08:41 PM Re: Understanding the ODBC UDF
Les Offline
KiX Master
*****

Registered: 2001-06-11
Posts: 12734
Loc: fortfrances.on.ca
The line that both Kent and DOC showed you. There is a FAQ on how to use UDFs.
_________________________
Give a man a fish and he will be back for more. Slap him with a fish and he will go away forever.

Top
#141195 - 2005-06-08 12:59 AM Re: Understanding the ODBC UDF
Huffinagle Offline
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 Offline
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)
_________________________
Utilize these resources:
UDFs (Full List)
KiXtart FAQ & How to's

Top
#141197 - 2005-06-08 01:25 AM Re: Understanding the ODBC UDF
Huffinagle Offline
Getting the hang of it
*****

Registered: 2002-09-04
Posts: 75
Loc: McMinnville, OR, USA
Thanks again Kent. I didn't understand that the code inside the FUNCTION was static and that I needed to create my custom settings by defining parameters for the "name" of the function, that being ODBCSQL.

I ran the function with the correct syntax and the ODBC was created successfully.

Top
#141198 - 2005-06-08 06:16 PM Re: Understanding the ODBC UDF
Huffinagle Offline
Getting the hang of it
*****

Registered: 2002-09-04
Posts: 75
Loc: McMinnville, OR, USA
Now that I have the ODBC creating UDF working I have some new questions.

1. Does this UDF overwrite existing ODBC DSNs if they exist?
2. If it does overwrite DSNs, is there a way to check existing before the UDF creates an ODBC DSN?
3. Is there a way to script delete ODBC DSNs?

Top
#141199 - 2005-06-09 05:35 AM Re: Understanding the ODBC UDF
Sealeopard Offline
KiX Master
*****

Registered: 2001-04-25
Posts: 11164
Loc: Boston, MA, USA
The answer is YES to all three of your questions. So, go write your own functions that perform those checks or delete DSNs. It essentially all comes down to reading/writing registry keys/values.
_________________________
There are two types of vessels, submarines and targets.

Top
#141200 - 2005-06-09 08:23 AM Re: Understanding the ODBC UDF
Kdyer Offline
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
_________________________
Utilize these resources:
UDFs (Full List)
KiXtart FAQ & How to's

Top
#141201 - 2005-06-09 08:55 PM Re: Understanding the ODBC UDF
Huffinagle Offline
Getting the hang of it
*****

Registered: 2002-09-04
Posts: 75
Loc: McMinnville, OR, USA
Thanks all, for your direction. By the way, I just sat through a WebEx demo of ScriptLogic's Desktop Authority. Having briefly worked with Kixtart sure gave me an appreciation for what Desktop Authority 6.51 can do.
Top
#141202 - 2005-06-10 06:50 AM Re: Understanding the ODBC UDF
Kdyer Offline
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
_________________________
Utilize these resources:
UDFs (Full List)
KiXtart FAQ & How to's

Top
#141203 - 2005-06-10 07:35 PM Re: Understanding the ODBC UDF
Huffinagle Offline
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
#141204 - 2005-06-10 07:50 PM Re: Understanding the ODBC UDF
Les Offline
KiX Master
*****

Registered: 2001-06-11
Posts: 12734
Loc: fortfrances.on.ca
$RC stands for Return Code and if not processed later in the script, is simply a bit bucket (trash) used to sink the return code (prevent it from crapping to the screen).

There is a lot of good advice in our FAQ forum and you can also glean quite a bit from just looking at programming styles of most of the posted UDFs.
_________________________
Give a man a fish and he will be back for more. Slap him with a fish and he will go away forever.

Top
Page 1 of 2 12>


Moderator:  Jochen, Allen, Radimus, Glenn Barnas, ShaneEP, Ruud van Velsen, Arend_, Mart 
Hop to:
Shout Box

Who's Online
0 registered and 346 anonymous users online.
Newest Members
gespanntleuchten, DaveatAdvanced, Paulo_Alves, UsTaaa, xxJJxx
17864 Registered Users

Generated in 0.073 seconds in which 0.024 seconds were spent on a total of 13 queries. Zlib compression enabled.

Search the board with:
superb Board Search
or try with google:
Google
Web kixtart.org