| 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 |
|---|
