Changes between Initial Version and Version 2 of Ticket #36


Ignore:
Timestamp:
01/17/08 15:27:59 (17 years ago)
Author:
Jpm
Comment:

Legend:

Unmodified
Added
Removed
Modified
  • Ticket #36 – Description

    initial v2  
    22
    33
    4 Sample code:
    5 
    6 Global Const $SQLSRV   = "TSTSQL01"
    7 Global Const $DATABASE = "test"
    8 Global Const $DB_UID   = "user01"
    9 Global Const $DB_PW    = "user01"
    10 Global Const $Table    = "int_test"
    11 
    12 $pSQL = _SQLConnect($SQLSRV, $DATABASE, 1, $DB_UID, $DB_PW, "{SQL Server}")
    13 If NOT IsObj($pSQL)     Then
    14         MsgBox(16, "DB Error", "Unable to connect to database: " & $DATABASE & " " & _
    15                                "at SQL Server: " & $SQLSRV)
    16         Exit(-1)
    17 EndIf
    18 
    19 If _TblExists($pSQL, $Table) Then
    20         $Drop = "DROP TABLE " & $Table
    21         $pDrop = _SQLQuery($pSQL, $Drop)
    22 EndIf   
    23        
    24 $Create  = "CREATE TABLE " & $Table  & _
    25                    "(ip_addr1 float, "       & _
    26                     "ip_addr2 int, "         & _
    27                         "ip_addr3 bigint, "      & _
    28                         "ip_addr4 decimal(12), " & _
    29                         "ip_addr5 numeric(12))"
    30 $pCreate = _SQLQuery($pSQL, $Create)
    31 
    32 $v1 = Dec("AC1C0934") ; 172.28.9.52
    33 $Insert  = "INSERT INTO " & $Table & " VALUES(" & $v1 & "," & $v1 & "," & $v1 & "," & $v1 & "," & $v1 & ")"
    34 $pInsert = _SQLQuery($pSQL, $Insert)
    35 
    36 $v2 = Dec("41D30DD7") ; 65.211.13.215
    37 $Insert  = "INSERT INTO " & $Table & " VALUES(" & $v2 & "," & $v2 & "," & $v2 & "," & $v2 & "," & $v2 & ")"
    38 $pInsert = _SQLQuery($pSQL, $Insert)
    39 
    40 $v3 = Dec("7F000002") ; 127.0.0.2
    41 $Insert  = "INSERT INTO " & $Table & " VALUES(" & $v3 & "," & $v3 & "," & $v3 & "," & $v3 & "," & $v3 & ")"
    42 $pInsert = _SQLQuery($pSQL, $Insert)
    43 
    44 $Query  = "SELECT * FROM " & $Table
    45 $pQuery = _SQLQuery($pSQL, $Query)
    46 
    47 While NOT $pQuery.EOF
    48         For $i = 0 to 4
    49                 WTO($i & ": " & $pQuery($i).Value & " - " & Hex($pQuery($i).Value))
    50         Next   
    51         $pQuery.MoveNext
    52 WEnd
    53 
    54 _SQLDisconnect($pSQL)
    55 Exit(0)
    56 
    57 Func _SQLConnect($sServer, $sDatabase, $fAuthMode = 0, $sUsername = "", $sPassword = "", $sDriver = "{SQL Server}")
    58     Local $sTemp = StringMid($sDriver, 2, StringLen($sDriver) - 2)
    59     Local $sKey = "HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\ODBC Drivers", $sVal = RegRead($sKey, $sTemp)
    60     If @error or $sVal = "" Then Return SetError(2, 0, 0)
    61     $oConn = ObjCreate("ADODB.Connection")
    62     If NOT IsObj($oConn) Then Return SetError(3, 0, 0)
    63     If $fAuthMode Then $oConn.Open ("DRIVER=" & $sDriver & ";SERVER=" & $sServer & ";DATABASE=" & $sDatabase & ";UID=" & $sUsername & ";PWD=" & $sPassword & ";")
    64     If NOT $fAuthMode Then $oConn.Open("DRIVER=" & $sDriver & ";SERVER=" & $sServer & ";DATABASE=" & $sDatabase)
    65     If @error Then Return SetError(1, 0, 0)
    66     Return $oConn
    67 EndFunc
    68 
    69 Func _SQLQuery($o_ADOcn, $s_Query, $cmd_TimeOut=900)
    70     Local $o_ADOCmd, $o_ADOrs
    71 ConsoleWrite($s_Query & @CRLF) 
    72     $o_ADOCmd = ObjCreate("ADODB.Command")
    73     $o_ADOCmd.ActiveConnection = $o_ADOcn
    74     $o_ADOCmd.CommandText = $s_Query
    75     $o_ADOCmd.CommandTimeOut = $cmd_TimeOut
    76     $o_ADOrs = $o_ADOCmd.Execute
    77 
    78     if @error Then return 0
    79     return $o_ADOrs
    80 EndFunc
    81 
    82 Func _TblExists($oConn, $TblName)
    83         $Query = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME = '" & $TblName & "'"
    84         $pQuery = _SQLQuery($oConn, $Query)
    85         If $pQuery.EOF Then
    86                 Return False
    87         Else
    88                 Return True
    89         EndIf
    90 EndFunc         
    91 
    92 Func _SQLDisconnect($oConn)
    93     If NOT IsObj($oConn) Then Return SetError(1, 0, 0)
    94     $oConn.Close
    95     Return 1
    96 EndFunc
    97 
    98 Func WTO($String)
    99         ConsoleWrite($String & @CRLF)
    100 EndFunc
     4Sample code: see attached file