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