#172086 - 2006-12-27 05:56 PM
TrimIP doesn't work
|
Savvy95
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
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:
$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
|
|
|
|
#172127 - 2006-12-28 04:39 PM
Re: TrimIP doesn't work
[Re: Richard H.]
|
Savvy95
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
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:
Join(Split("No place like home"," "),"|")
If the replace string is the null string, then the effect is to delete the character, so:
Join(Split("No place like home"," "),"") will give you the string "Noplacelikehome".
|
Top
|
|
|
|
Moderator: Glenn Barnas, NTDOC, Arend_, Jochen, Radimus, Allen, ShaneEP, Ruud van Velsen, Mart
|
0 registered
and 515 anonymous users online.
|
|
|