Page 1 of 1 1
Topic Options
#172086 - 2006-12-27 05:56 PM TrimIP doesn't work
Savvy95 Offline
Fresh Scripter

Registered: 2005-05-09
Posts: 11
I'm trying to capture IP addresses to a SQL Db. I am trying to use a TRIMIP function to truncate the empty char strings in front of the octects. But it's not working. No entry appears in the DB. If I remove the TRIMIP, then it runs as expected.

Can anyone help?


;**********************************************
;BEGIN FUNCTION DB CONNECTION
;**********************************************

Function DBConnOpen($ConnDSN, optional $ConnTimeout, optional $CmdTimeout)
Dim $objConn, $adStateOpen

$dbconnopen=''

$adStateOpen=1

if vartype($ConnTimeout)
$ConnTimeout=val($ConnTimeout)
else
$ConnTimeout=15
endif

if vartype($CmdTimeout)
$CmdTimeout=val($CmdTimeout)
else
$CmdTimeout=30
endif

$ConnDSN=trim($ConnDSN)
if not $ConnDSN
exit 87
endif

$objConn = CreateObject('ADODB.Connection')
if @ERROR
exit @ERROR
endif

$objConn.ConnectionTimeout = $ConnTimeout
if @ERROR
exit @ERROR
endif

$objConn.CommandTimeout = $CmdTimeout
if @ERROR
exit @ERROR
endif

$objConn.Open($ConnDSN)
if @ERROR
exit @ERROR
endif

if not $objConn.State=$adStateOpen
$objConn=''
$DBConnOpen=''
exit @ERROR
endif
$DBConnOpen=$objConn
exit 0
EndFunction

;**********************************************
;END FUNCTION DB CONNECTION
;**********************************************


;**********************************************
;BEGIN FUCNTION DB EXECUTE
;**********************************************

function DBExecuteSQL($objConn, $sql, optional $cmdType)
dim $cmdCommand, $rsRecordset
dim $adCmdUnspecified, $adCmdText, $adCmdTable, $adCmdStoredProc, $adCmdUnknown, $adCmdFile, $adCmdTableDirect

$adCmdUnspecified = -1
$adCmdText = 1
$adCmdTable = 2
$adCmdStoredProc = 4
$adCmdUnknown = 8
$adCmdFile = 256
$adCmdTableDirect = 512

if vartype($cmdType)
$cmdType=val($cmdType)
else
$cmdType=$adCmdText
endif

if vartype($objConn)<>9 or $sql=''
exit 87
endif

$cmdCommand = CreateObject('ADODB.Command')
if @ERROR
exit @ERROR
endif

$cmdCommand.ActiveConnection = $objConn
if @ERROR
exit @ERROR
endif

$cmdCommand.CommandType = $cmdType
if @ERROR
exit @ERROR
endif

$cmdCommand.CommandText = $sql
if @ERROR
exit @ERROR
endif

$rsRecordset=$cmdCommand.Execute()
$cmdCommand=''
$rsRecordset=''
if @ERROR
exit @ERROR
endif

$DBExecuteSQL=0
exit 0

endfunction
;**********************************************
;END FUCNTION DB EXECUTE
;**********************************************

;**********************************************
;BEGIN FUNCTION DB CLOSE
;**********************************************
Function DBConnClose($objConn)
Dim $adStateOpen

$adStateOpen = 1

If vartype($objConn)=9
If $objConn.State = $adStateOpen
$objConn.Close()
if @ERROR
exit @ERROR
endif
EndIf
$objConn=''
else
exit 87
endif

$DBConnClose=0
exit 0
EndFunction
;**********************************************
;END FUNCTION DB CLOSE
;**********************************************
;**********************************************
;BEGIN FUNCTION TRIMIP
;**********************************************
FUNCTION TrimIP()
DIM $IPaddress
DIM $IP1
DIM $IP2
DIM $IP3
DIM $IP4
$IPaddress = (@IPADDRESS0)
$IP1 = LTRIM(SUBSTR($IPaddress, 1, 3))
$IP2 = LTRIM(SUBSTR($IPaddress, 5, 3))
$IP3 = LTRIM(SUBSTR($IPaddress, 9, 3))
$IP4 = LTRIM(SUBSTR($IPaddress, 13, 3))
$TrimIP = $IP1 + "." + $IP2 + "." + $IP3 + "." + $IP4
ENDFUNCTION
;**********************************************
;END FUNCTION TRIMIP
;**********************************************

$SQLstmt = "Insert into UserTimes (Userid, FullName, Workstation, Dom, DC, IP, MAC) "

$SQLstmt = $SQLstmt + "VALUES ('@USERID', '@FULLNAME', '@WKSTA', '@DOMAIN', '@LSERVER', + TrimIP(@IPAddress0) +, '@ADDRESS')"


$objConn = DBConnOpen ("DRIVER={SQL Server};SERVER=Server01;DATABASE=DBName")
DBConnOpen($objConn)
DBExecuteSQL ($objConn, $SQLstmt)
DBConnClose ($objConn)

Top
#172087 - 2006-12-27 06:10 PM Re: TrimIP doesn't work [Re: Savvy95]
Richard H. Administrator Offline
Administrator
*****

Registered: 2000-01-24
Posts: 4946
Loc: Leatherhead, Surrey, UK
You are passing a parameter to TrimIP(), however the function definition does not allow a parameter.

You are also including the call to the function inside the quotes, so I expect that the SQL statement is failing as it is receiving garbage.

You could try something like this:

Code:
$SQLstmt = $SQLstmt+"VALUES("
   +"'"+@USERID+"', "
   +"'"+@FULLNAME+"', "
   +"'"+@WKSTA+"', "
   +"'"+@DOMAIN+"', "
   +"'"+@LSERVER+"', "
   +"'"+Join(Split(@IPAddress0," "),"")+"', "
   +"'"+@ADDRESS+"'"
   +")"


You should be checking the return values of the functions to see if there are any errors. This will help you determine which part is failing.

Top
#172089 - 2006-12-27 07:40 PM Re: TrimIP doesn't work [Re: Savvy95]
NTDOC Administrator Offline
Administrator
*****

Registered: 2000-07-28
Posts: 11623
Loc: CA
Savvy

Please review this page on how to post. You should use CODE TAGS when posting code so that it's easier to read and respond.

The Post/Reply Formatting Box and How to use it
http://www.kixtart.org/forums/ubbthreads.php?ubb=showflat&Number=171901

.

Top
#172127 - 2006-12-28 04:39 PM Re: TrimIP doesn't work [Re: Richard H.]
Savvy95 Offline
Fresh Scripter

Registered: 2005-05-09
Posts: 11
Thanks, that worked; even though I don't understand the JOIN(SPLIT statement.

Thanks again

Top
#172128 - 2006-12-28 05:06 PM Re: TrimIP doesn't work [Re: Savvy95]
Richard H. Administrator Offline
Administrator
*****

Registered: 2000-01-24
Posts: 4946
Loc: Leatherhead, Surrey, UK
SplitJoin is a useful trick which we use as a fast string replace.

In this case we split the string on the space character which creates an array that we then join back up again using an empty string.

Here is an example. If we take the string "No place like home" and split on the spaces we will get an array that looks like this:
  • Array[0]="No"
  • Array[1]="place"
  • Array[2]="like"
  • Array[3]="home"


If we now use Join(Array,"|") to convert the array back to a string we would get "No|place|like|home" - the effect is to replace the original space characters with the vertical bar character.

The return value from one function can be used as the parameter for another, so we can do the replace action in one pass:
Code:
Join(Split("No place like home"," "),"|")


If the replace string is the null string, then the effect is to delete the character, so:
Code:
Join(Split("No place like home"," "),"")

will give you the string "Noplacelikehome".

Top
Page 1 of 1 1


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

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

Generated in 0.049 seconds in which 0.022 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