| 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 |
| | 4 | Sample code: see attached file |
