Page 1 of 1 1
Topic Options
#120833 - 2004-06-08 04:59 PM Stored procedures
Stanley Offline
Starting to like KiXtart

Registered: 2004-06-03
Posts: 130
Loc: Upstate NY, USA
I am a newcomer to KIX ~6 months. I have been viewing this BBS for about four months and have found your examples to be very helpful. I would appreciate any suggestions you have to improve the following.
Thanks
Stanley

CODE

;FUNCTION WRITEDB
;
;ACTION This function will use a stored procedure to write to a database
;
;AUTHOR Stanley
;
;CONTRIBUTORS Philbert, TKO
;
;VERSION 1
;
;DATE CREATED 20040603 YYYYMMDD
;
;DATE MODIFIED 20040608
;
;KIXTART 4.22
;
;SYNTAX WRITEDB( <job_name> , <status> , <descrip> )
;
;PARAMETERS job_name
; Name of parent script
;
; status
; Job started = GREEN, completed successfully = GRAY, errored = RED
;
; PARAMETER3
; Text field used to send @SERROR or Completed
;
;RETURNS nothing
;
;REMARKS Additional remarks about the UDF

;

FUNCTION WRITEDB($job_name,$status,$descrip)

; Path to UDL
$ConnDSN = "file name=<PATH TO UDL>"

; Create an object of type: ADODB class: Connection
$objConn = CreateObject("ADODB.Connection")

; Pass UDL path to connectionstring method of Object
; created in previous step
$objConn.connectionstring = $ConnDSN

; Open database ADODB object
$objConn.Open()

; Create an object of type: ADODB class: Command
$oCmd = CreateObject("ADODB.Command")

; Assign DB connection to Command
$oCmd.ActiveConnection = $objConn

; Set Command text
$oCmd.CommandText = "<SCHEMA.PROCEDURE_NAME>"

; Set Command Type
$oCmd.CommandType = 4

; Create parameter and set properties to be passed to Stored Procedure
$oParam1 = $oCmd.CreateParameter("job_name_val")
$oParam1.type = 200
$oParam1.direction = 1
$oParam1.size = 255
$oParam1.value = $job_name
$oCmd.Parameters.Append($oParam1);Append parameter list

; Create parameter and set properties to be passed to Stored Procedure
$oParam2 = $oCmd.CreateParameter("description_val")
$oParam2.type = 200
$oParam2.direction = 1
$oParam2.size = 4000
$oParam2.value = $descrip
$oCmd.Parameters.Append($oParam2);Append parameter list

; Create parameter and set properties to be passed to Stored Procedure
$oParam3 = $oCmd.CreateParameter("status_val")
$oParam3.type = 200
$oParam3.direction = 1
$oParam3.size = 50
$oParam3.value = $status
$oCmd.Parameters.Append($oParam3);Append parameter list

; Execute Command
$cmdx = $oCmd.Execute

; Set parameters = to NULL
$oParam1 = ""

$oParam2 = ""

$oParam3 = ""

; Set Command = to NULL
$oCmd = ""

; Close DB object
$objConn.close()

ENDFUNCTION

CODE

_________________________
Taxation WITH representation isn't so hot, either!

Top
#120834 - 2004-06-08 08:07 PM Re: Stored procedures
Jochen Administrator Offline
KiX Supporter
*****

Registered: 2000-03-17
Posts: 6380
Loc: Stuttgart, Germany
Stanley,

I moved your other thread from udf library to here ... please decide on your own which to delete.
Didn't recognice that there was already another post here; I would have deleted it on my own then, but my mods powers arefailing here

Thanx
_________________________



Top
#120835 - 2004-06-08 08:25 PM Re: Stored procedures
Stanley Offline
Starting to like KiXtart

Registered: 2004-06-03
Posts: 130
Loc: Upstate NY, USA
Thank you Jochen:
Please delete the other one "Using stored procedures".
Or is there some way I should delete it?
Thanx
Stanley
_________________________
Taxation WITH representation isn't so hot, either!

Top
#120836 - 2004-06-08 09:07 PM Re: Stored procedures
Jochen Administrator Offline
KiX Supporter
*****

Registered: 2000-03-17
Posts: 6380
Loc: Stuttgart, Germany
I can't as I am not moderator of starters zone ...
You may be successful by editing your first post in this thread ... there should be a 'delete' button
_________________________



Top
#120837 - 2004-06-08 09:10 PM Re: Stored procedures
Stanley Offline
Starting to like KiXtart

Registered: 2004-06-03
Posts: 130
Loc: Upstate NY, USA
Thanks again Jochen
Post has been deleted.
Stanley
_________________________
Taxation WITH representation isn't so hot, either!

Top
#120838 - 2004-06-08 09:25 PM Re: Stored procedures
Jochen Administrator Offline
KiX Supporter
*****

Registered: 2000-03-17
Posts: 6380
Loc: Stuttgart, Germany
Ok,

now to start giving some tips
Quite essential for udfs, preventing later headaches, is to dim every single variable used in this udf, except the passed parameters and the return var, speak $NameOfUDF.

Example:

Let's say you have a loop where you increment a counter $i from 1 to 100
and call within any udf using accidentially the same var :

Code:

for $i = 1 to 100
$result = MyUDF($i)
next

.
.
.

function MyUDF($input)
$i = $input - 1 ;'dim $i' would have prevented the endless loop
$MyUDF = $i
endfunction



Result : Endless loop


Edited by Jochen (2004-06-08 09:26 PM)
_________________________



Top
#120839 - 2004-06-08 09:57 PM Re: Stored procedures
Stanley Offline
Starting to like KiXtart

Registered: 2004-06-03
Posts: 130
Loc: Upstate NY, USA
Like so?
Code:
 FUNCTION WRITEDB($job_name,$status,$descrip)

DIM $ConnDSN,$objconn,$oCmd,$oParam1,$oParam2,$oParam3,$cmdx




Thanks
Stanley
_________________________
Taxation WITH representation isn't so hot, either!

Top
#120840 - 2004-06-08 09:59 PM Re: Stored procedures
Jochen Administrator Offline
KiX Supporter
*****

Registered: 2000-03-17
Posts: 6380
Loc: Stuttgart, Germany
exactly
_________________________



Top
Page 1 of 1 1


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

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

Generated in 0.057 seconds in which 0.023 seconds were spent on a total of 12 queries. Zlib compression enabled.

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