SQL Server DB Viewer

rodent1 · December 15, 2011

view tables and their contents on a SQL Server database, locally or on a network.

The login information is stored without encryption, in a file named SQLDBViewer.ini. If this is a problem, comment out the calls to SaveINI(), and you will have to enter the info every time you start the script.

This has been used with a SQL Server 2008 database where tables belong to various schemas, on XP SP3, and win2008R1.

Ctrl-alt-c will copy the query, the name of the columns and the returned data so you can paste that info elsewhere.

F5 will run the query again if you edit it.

Please feel free to improve on this.

The code is displayed here, and also attached below.

The latest link is at the bottom.

Enjoy.

Opt('MustDeclareVars', 1)
Opt("GUIOnEventMode", 1)
#include <GUIConstantsEx.au3>
#include <ComboConstants.au3>
#include <EditConstants.au3>
#include <GUIListBox.au3>
#Include <GuiListView.au3>
#include <StaticConstants.au3>
#include <WindowsConstants.au3>
#include <Array.au3>
Global $g_eventerror = 0
Global $oMyError = 0
;Dim $DEBUG = True
Dim $DEBUG = False
Dim $arConnectInfo[1]
$arConnectInfo[0] = 0
Dim $FormHwnd, $LabelID, $cboConnInfo, $btnConnect
Dim $gbConInfo, $lblServer, $txtServer, $lblCat, $txtCatalog, $lblUser, $txtuser, $lblPasword, $txtPassword
Dim $gbTableListDisplay, $lbTables
Dim $gbTableDisplay, $pb, $txtQuery, $lvTable, $cm, $cmCopyColNamesAndRow, $cmCopyRow, $lblSep
Dim $IniBuf = ""
Dim $SelectedConnectionInformation = ""
Dim $bCheckConInfo = False
Dim $bCheckSelectedTable = False
Dim $CurrentTableName = "" ; the table that is currently selected. Will change if a new selection is made.
Dim $bShowUpDnButtons = False ; true to show up and down separator buttons, false to hide them
Dim $SleepTimeMS = 300
Dim $MouseYLocation = -1
Dim $TableData = "";includes the query, the column names and tab-separated column values for CRLF-separated records, populated when a query is run
HotKeySet ( "^!c", "CopyData" );ctrl-alt-c
HotKeySet ( "{F5}", "RunUserQuery" )
Main()
Func Main()
 ReadIniData() ; read stored connection information
 DoUI() ; create the UI and handle user actions on the UI
EndFunc
;-----------UI Functions-----------
Func DoUI()
 Dim $font = "Comic Sans MS"
;~  GUISetFont ( 9, 400, 1, $font )
 $FormHwnd = GUICreate ( "SQL Server Tool", @DesktopWidth / 2 - 175, 200, 200, @DesktopHeight / 2 - 45, 0x00040000 + 0x00010000, 0x00000018 ) ; $WS_SIZEBOX + $WS_MAXIMIZEBOX, WS_EX_ACCEPTFILES
 GUISetBkColor ( 0x00E0FFFF ) ;window backgrd = light blue
 $LabelID = GUICtrlCreateLabel ( "Select Database", 2, 2, 200 )
 GUICtrlSetResizing ( $LabelID, $GUI_DOCKLEFT + $GUI_DOCKWIDTH + $GUI_DOCKTOP )
 $cboConnInfo = GUICtrlCreateCombo ( "", 2, 22, 118, 25, BitOR ( $CBS_DROPDOWN,$CBS_AUTOHSCROLL ) )
 GUICtrlSetResizing ( $cboConnInfo, $GUI_DOCKLEFT + $GUI_DOCKTOP + $GUI_DOCKSIZE )
 if $arConnectInfo [ 0 ] > 0 Then
  Dim $ConnBuffer = _ArrayToString ( $arConnectInfo )
  $ConnBuffer = StringRight ( $ConnBuffer, StringLen ( $ConnBuffer ) - StringInStr ( $ConnBuffer, @CRLF ) - 2 )
  GUICtrlSetData ( $cboConnInfo, $ConnBuffer )
 EndIf
 $btnConnect = GUICtrlCreateButton ( "New Connection", 120, 20, 98, 22 )
 GUICtrlSetOnEvent($btnConnect, "ConnectBtnOnClick")
 GUICtrlSetResizing ( $btnConnect, $GUI_DOCKLEFT + $GUI_DOCKTOP + $GUI_DOCKSIZE )
#region Connection Infomation groupbox
 $gbConInfo = GUICtrlCreateGroup("Connection Information", 220, 8, 260, 81)
 GUICtrlSetResizing ( $gbConInfo, $GUI_DOCKLEFT + $GUI_DOCKTOP + $GUI_DOCKWIDTH + $GUI_DOCKHEIGHT )
 GUICtrlSetState ( $gbConInfo, $GUI_HIDE )
 $lblServer = GUICtrlCreateLabel("Server", 230, 24, 35, 17)
 GUICtrlSetResizing ( $lblServer, $GUI_DOCKLEFT + $GUI_DOCKTOP + $GUI_DOCKWIDTH + $GUI_DOCKHEIGHT )
 GUICtrlSetState ( $lblServer, $GUI_HIDE )
 $txtServer = GUICtrlCreateInput("", 270, 24, 70, 21)
 GUICtrlSetResizing ( $txtServer, $GUI_DOCKLEFT + $GUI_DOCKTOP + $GUI_DOCKWIDTH + $GUI_DOCKHEIGHT )
 GUICtrlSetState ( $txtServer, $GUI_HIDE )
 $lblCat = GUICtrlCreateLabel("Catalog", 230, 56, 40, 17)
 GUICtrlSetResizing ( $lblCat, $GUI_DOCKLEFT + $GUI_DOCKTOP + $GUI_DOCKWIDTH + $GUI_DOCKHEIGHT )
 GUICtrlSetState ( $lblCat, $GUI_HIDE )
 $txtCatalog = GUICtrlCreateInput("", 270, 56, 70, 21)
 GUICtrlSetResizing ( $txtCatalog, $GUI_DOCKLEFT + $GUI_DOCKTOP + $GUI_DOCKWIDTH + $GUI_DOCKHEIGHT )
 GUICtrlSetState ( $txtCatalog, $GUI_HIDE )
 $lblUser = GUICtrlCreateLabel("User", 350, 24, 26, 17)
 GUICtrlSetResizing ( $lblUser, $GUI_DOCKLEFT + $GUI_DOCKTOP + $GUI_DOCKWIDTH + $GUI_DOCKHEIGHT )
 GUICtrlSetState ( $lblUser, $GUI_HIDE )
 $txtuser = GUICtrlCreateInput ( "", 400, 24, 70, 21 )
 GUICtrlSetResizing ( $txtuser, $GUI_DOCKLEFT + $GUI_DOCKTOP + $GUI_DOCKWIDTH + $GUI_DOCKHEIGHT )
 GUICtrlSetState ( $txtuser, $GUI_HIDE )
 $lblPasword = GUICtrlCreateLabel("Password", 350, 56, 50, 17)
 GUICtrlSetResizing ( $lblPasword, $GUI_DOCKLEFT + $GUI_DOCKTOP + $GUI_DOCKWIDTH + $GUI_DOCKHEIGHT )
 GUICtrlSetState ( $lblPasword, $GUI_HIDE )
 $txtPassword = GUICtrlCreateInput ( "", 400, 56, 70, 21, $ES_PASSWORD )
 GUICtrlSetResizing ( $txtPassword, $GUI_DOCKLEFT + $GUI_DOCKTOP + $GUI_DOCKWIDTH + $GUI_DOCKHEIGHT )
 GUICtrlSetState ( $txtPassword, $GUI_HIDE )
 GUICtrlCreateGroup("", -99, -99, 1, 1)
#endregion End Connection Infomation groupbox
#region Available Table List groupgbox
 $gbTableListDisplay = GUICtrlCreateGroup("Table List", 2, 42, 210, @DesktopHeight / 2 - 376)
 GUICtrlSetResizing ( $gbTableListDisplay, $GUI_DOCKLEFT + $GUI_DOCKTOP + $GUI_DOCKWIDTH + $GUI_DOCKBOTTOM )
 GUICtrlSetState ( $gbTableListDisplay, $GUI_HIDE )
 $lbTables = GUICtrlCreateList ( "", 4, 56, 206, @DesktopHeight / 2 - 392, $LBS_NOINTEGRALHEIGHT + $WS_VSCROLL + $LBS_SORT )
 GUICtrlSetResizing ( $lbTables, $GUI_DOCKLEFT + $GUI_DOCKTOP + $GUI_DOCKWIDTH + $GUI_DOCKBOTTOM )
 GUICtrlSetState ( $lbTables, $GUI_HIDE )
 GUICtrlCreateGroup("", -99, -99, 1, 1)
#endregion Available Table List groupgbox
#region Table display groupbox
 $gbTableDisplay = GUICtrlCreateGroup("Table", 220, 2, @DesktopWidth / 2 - 400, @DesktopHeight / 2 - 336)
 GUICtrlSetResizing ( $gbTableDisplay, $GUI_DOCKLEFT + $GUI_DOCKTOP + $GUI_DOCKWIDTH + $GUI_DOCKRIGHT + $GUI_DOCKBOTTOM )
 GUICtrlSetState ( $gbTableDisplay, $GUI_HIDE )
 $pb = GUICtrlCreateProgress ( 222, 16, @DesktopWidth / 2 - 404, 17, 0x01 ) ; 0x01 = $PBS_SMOOTH
 GUICtrlSetColor ( $pb, 0x00FF00 ); set progress bar color to green
 GUICtrlSetState ( $pb, $GUI_HIDE )
 $txtQuery = GUICtrlCreateEdit ( "", 222, 16, @DesktopWidth / 2 - 404, 60, $ES_MULTILINE )
 GUICtrlSetResizing ( $txtQuery, $GUI_DOCKLEFT + $GUI_DOCKTOP + $GUI_DOCKRIGHT + $GUI_DOCKHEIGHT )
 GUICtrlSetState ( $txtQuery, $GUI_HIDE )
 $lblSep = GUICtrlCreateLabel ( "", 220, 72, @DesktopWidth / 2 - 402, 12 )
 GUICtrlSetResizing ( $lblSep, $GUI_DOCKLEFT + $GUI_DOCKHEIGHT + $GUI_DOCKRIGHT )
 GUICtrlSetBkColor ( $lblSep, 0x808080 )
 GUICtrlSetCursor ( $lblSep, 11 )
 GUICtrlSetState ( $lblSep, $GUI_HIDE )
 $lvTable = GUICtrlCreateListView ( "", 222, 80, @DesktopWidth / 2 - 404, 98 )
 GUICtrlSetResizing ( $lvTable, $GUI_DOCKLEFT + $GUI_DOCKTOP + $GUI_DOCKRIGHT + $GUI_DOCKBOTTOM )
 GUICtrlSetState ( $lvTable, $GUI_HIDE )
 $cm = GUICtrlCreateContextMenu ( $lvTable )
 $cmCopyColNamesAndRow = GUICtrlCreateMenuItem ( "Copy Columns + Row", $cm )
 GUICtrlSetOnEvent($cmCopyColNamesAndRow, "CopyColNamesAndRowOnClick")
 $cmCopyRow = GUICtrlCreateMenuItem ( "Copy Row", $cm )
 GUICtrlSetOnEvent($cmCopyRow, "CopyRowOnClick")
 GUICtrlCreateGroup("", -99, -99, 1, 1)
#endregion Table display groupbox
 GUISetOnEvent($GUI_EVENT_CLOSE, "OnExit")
 GUISetState()  ; display the GUI
 While 1
  if StringLen ( GUICtrlRead ( $cboConnInfo ) ) = 0 Then
   GUICtrlSetData ( $btnConnect, "New Connection" )
  Else
   GUICtrlSetData ( $btnConnect, "Connect" )
  EndIf
  if $bCheckConInfo Then
   CheckConInfo()
  EndIf
  if $bCheckSelectedTable Then
   ; a query is displayed with its data. Watch for a new table selection to update the query an data display.
   CheckTableSelection()
   ; if the mouse is between the query edit box and the data listview, change the mouse cursor
   SetMouseCursor()
  EndIf
  Sleep ( $SleepTimeMS )
 WEnd
 GUIDelete( $FormHwnd )
 Exit
EndFunc
Func ConnectBtnOnClick()
 $SelectedConnectionInformation = GUICtrlRead ( $cboConnInfo )
 if StringLen ( $SelectedConnectionInformation ) = 0 And StringLen ( GUICtrlRead ( $txtPassword ) ) > 0 Then
  $SelectedConnectionInformation = GUICtrlRead ( $txtServer ) & @TAB & GUICtrlRead ( $txtCatalog ) & @TAB & GUICtrlRead ( $txtUser ) & @TAB & GUICtrlRead ( $txtPassword )
 EndIf
 Dim $InfoItemNumber = _ArrayTokenNum ( $SelectedConnectionInformation, @TAB )
 if $InfoItemNumber < 4 Then
  FillConInfoWithExistingInfo ( $InfoItemNumber )
  DisplayConInfoGroupBox ( $GUI_SHOW )
  GUICtrlSetState ( $btnConnect,  $GUI_DISABLE )
  $bCheckConInfo =  True
 Else
  $bCheckConInfo =  False ; stop checking connection info user filled controls
  ClearConInfoGBData () ; clear the server, catalog, user and password edit boxes and hide the connection info groupbox with its contents
  DisplayConInfoGroupBox ( $GUI_HIDE )
  if ShowDBTables() Then
   SaveIni()
  EndIf
 EndIf
EndFunc
Func CopyColNamesAndRowOnClick()
Dim $ItemText = ""
Dim $arColInfo [ 9 ]
Dim $SelNum = _GUICtrlListView_GetSelectedCount ( $lvTable )
Dim $ColCount = _GUICtrlListView_GetColumnCount ( $lvTable )
 for $i = 0 to $ColCount - 1
  $arColInfo = _GUICtrlListView_GetColumn ( $lvTable, $i )
  if $i = 0 Then
   $ItemText = $arColInfo [ 5 ]
  Else
   $ItemText &= @TAB & $arColInfo [ 5 ]
  EndIf
 Next
 $ItemText &= @CRLF
 if $SelNum > 0 Then
  Dim $SelRow = _GUICtrlListView_GetSelectedIndices ( $lvTable )
  if StringLen ( $SelRow ) > 0 Then
   for $Col = 0 to $ColCount - 1
    if $Col = 0 Then
     $ItemText &= _GUICtrlListView_GetItemText ( $lvTable, $SelRow * 1, $Col )
    Else
     $ItemText &= @TAB & _GUICtrlListView_GetItemText ( $lvTable, $SelRow * 1, $Col )
    EndIf
   Next
  EndIf
 EndIf
 ClipPut ( "" )
 ClipPut ( $ItemText )
EndFunc
Func CopyRowOnClick()
 Dim $SelNum = _GUICtrlListView_GetSelectedCount( $lvTable )
 if $SelNum > 0 Then
  Dim $SelRow = _GUICtrlListView_GetSelectedIndices ( $lvTable )
  if StringLen ( $SelRow ) > 0 Then
   Dim $ItemText = ""
   for $Col = 0 to _GUICtrlListView_GetColumnCount ( $lvTable ) - 1
    if $Col = 0 Then
     $ItemText &= _GUICtrlListView_GetItemText ( $lvTable, $SelRow * 1, $Col )
    Else
     $ItemText &= @TAB & _GUICtrlListView_GetItemText ( $lvTable, $SelRow * 1, $Col )
    EndIf
   Next
   ClipPut ( "" )
   ClipPut ( $ItemText )
  EndIf
 EndIf
EndFunc
Func OnExit()
 Exit
EndFunc
;-----------Helper Functions-------------
Func _ArrayTokenNum ( $SeparatedString, $Separator )
 if StringInStr ( $SeparatedString, $Separator ) = 0 Then
  if StringLen ( $SeparatedString ) > 0 Then
   Return 1
  Else
   Return 0
  EndIf
 Else
  Dim $arTokens = StringSplit ( $SeparatedString, $Separator, 1 )
  Return $arTokens [ 0 ]
 EndIf
EndFunc
Func CheckConInfo()
 if StringLen ( GUICtrlRead ( $txtCatalog ) ) > 0 And _
  StringLen ( GUICtrlRead ( $txtUser ) ) > 0 And _
  StringLen ( GUICtrlRead ( $txtPassword ) ) > 0 Then
  GUICtrlSetState ( $btnConnect,  $GUI_ENABLE )
  GUICtrlSetData ( $btnConnect, "Connect" ) ; update the button text to let user know they can try connecting
  $SelectedConnectionInformation = GUICtrlRead ( $txtServer ) & @TAB & GUICtrlRead ( $txtCatalog ) & @TAB & GUICtrlRead ( $txtUser ) & @TAB & GUICtrlRead ( $txtPassword )
 EndIf
EndFunc
Func CheckTableSelection()
 Dim $SelectedTable = GUICtrlRead ( $lbTables )
 if StringLen ( $SelectedTable ) = 0 then Return ; no selection
 if $CurrentTableName = $SelectedTable Then Return ; no new selection
 GUICtrlSetData ( $txtQuery, "select * from " & $SelectedTable )
 DisplayTableGroup ( $GUI_SHOW )
 RunUserQuery()
 $CurrentTableName = $SelectedTable ; set the current table for future verification of user table selection change
EndFunc
Func ClearConInfoGBData ()
 GUICtrlSetData ( $txtServer, "" )
 GUICtrlSetData ( $txtCatalog, "" )
 GUICtrlSetData ( $txtUser, "" )
 GUICtrlSetData ( $txtPassword, "" )
EndFunc
; Convert the client (GUI) coordinates to screen (desktop) coordinates
Func ClientToScreen($hWnd, ByRef $x, ByRef $y)
    Local $stPoint = DllStructCreate("int;int")
    DllStructSetData($stPoint, 1, $x)
    DllStructSetData($stPoint, 2, $y)
    DllCall("user32.dll", "int", "ClientToScreen", "hwnd", $hWnd, "ptr", DllStructGetPtr($stPoint))
 $x = DllStructGetData($stPoint, 1)
    $y = DllStructGetData($stPoint, 2)
    ; release Struct (not really needed as it is a local)
    $stPoint = 0
EndFunc
Func CopyData ()
 ClipPut ( $TableData )
EndFunc
Func DisplayConInfoGroupBox ( $Setting )
 GUICtrlSetState ( $gbConInfo, $Setting )
 GUICtrlSetState ( $lblServer, $Setting )
 GUICtrlSetState ( $txtServer, $Setting )
 GUICtrlSetState ( $lblCat, $Setting )
 GUICtrlSetState ( $txtCatalog, $Setting )
 GUICtrlSetState ( $lblUser, $Setting )
 GUICtrlSetState ( $txtUser, $Setting )
 GUICtrlSetState ( $lblPasword, $Setting )
 GUICtrlSetState ( $txtPassword, $Setting )
EndFunc
Func DisplayTableGroup ( $Setting )
 GUICtrlSetState ( $gbTableDisplay, $Setting )
 GUICtrlSetState ( $txtQuery, $Setting )
 GUICtrlSetState ( $lvTable, $Setting )
 GUICtrlSetState ( $lblSep, $Setting )
EndFunc
Func DisplayTableListGroup ( $Setting )
 GUICtrlSetState ( $gbTableListDisplay, $Setting )
 GUICtrlSetState ( $lbTables, $Setting )
EndFunc
Func FillConInfoWithExistingInfo ( $InfoItemNumber )
 if $InfoItemNumber > 0 Then
  Dim $arConInfoValues = StringSplit ( $SelectedConnectionInformation, @CRLF, 1 )
  GUICtrlSetData ( $txtServer, $arConInfoValues [ 1 ] )
  if $InfoItemNumber > 1 Then GUICtrlSetData ( $txtCatalog, $arConInfoValues [ 2 ] )
  if $InfoItemNumber > 2 Then GUICtrlSetData ( $txtUser, $arConInfoValues [ 3 ] )
 EndIf
EndFunc
Func IsValidQuery ( $Query )
 ; the query can't be thouroughly checked here. Just make sure once leading and trailing spaces, tabs, CR, LF are stripped, it starts with "select " or "update " or "insert "
 While StringLeft ( $Query, 1 ) = " " Or StringLeft ( $Query, 1 ) = @TAB Or StringLeft ( $Query, 1 ) = @CR Or StringLeft ( $Query, 1 ) = @LF
  $Query = StringTrimLeft ( $Query, 1 )
 WEnd
 While StringRight ( $Query, 1 ) = " " Or StringRight ( $Query, 1 ) = @TAB Or StringRight ( $Query, 1 ) = @CR Or StringRight ( $Query, 1 ) = @LF
  $Query = StringTrimRight ( $Query, 1 )
 WEnd
 if StringLeft ( StringLower ( $Query ), 7 ) = "select " And StringLen ( $Query ) > 7 Then return True
 if StringLeft ( StringLower ( $Query ), 7 ) = "update " And StringLen ( $Query ) > 7 Then return True
 if StringLeft ( StringLower ( $Query ), 7 ) = "insert " And StringLen ( $Query ) > 7 Then return True
EndFunc
Func ReadIniData()
 if FileExists(@ScriptDir & "\" & "SQLDBViewer.ini") Then
  $IniBuf = FileRead ( @ScriptDir & "\SQLDBViewer.ini" )
  if @error = 0 Then
   ; $IniBuf is of tab- and CRLCF-format. Each line contains tab separated values <server><catalog><user><password>; if it becomes necessary, will encrypt and decrypt
   $arConnectInfo = StringSplit ( $IniBuf, @CRLF )
  EndIf
 EndIf
EndFunc
Func RecSetOpenError()
 if $oMyError = 0 then Return
 Dim $HexNumber = hex ( $oMyError.number, 8 )
 Msgbox ( 0, "COM Error !", "Error number" & @LF & $HexNumber & @LF & _
    "description is: " & $oMyError.windescription )
 $g_eventerror = 1 ; something to check for when this function returns
EndFunc
;-----------------------------
; Function RunQuery
; Input:
;  SQry = string containing the SQL query
;  $bFirstRowHasColumns = boolean, set to true to return the columns as the first item in the return array
; Output:
;  an array where item 0 contains the size of the array, and each item contains a tab-separated list of values for one record
; Caveat: if the database returns an error for eg bad credentials, AutoIT cannot handle the error and crashes.
;  This happens at the line "$oRS.open ( $Qry, $sqlCon, 3, -1)".
Func RunQuery ( $Qry, $bFirstRowHasColumns = False )
Dim $sqlCon
Dim $oRS
Dim $ani1
Dim $intNbLignes
Dim $adLockOptimistic =3 ;Verrouillage optimiste, un enregistrement à la fois. Le fournisseur utilise le verrouillage optimiste et ne verrouille les enregistrements qu'à l'appel de la méthode Update.
Dim $adOpenKeyset = 1 ;Utilise un curseur à jeu de clés. Identique à un curseur dynamique mais ne permettant pas de voir les enregistrements ajoutés par d'autres utilisateurs (les enregistrements supprimés par d'autres utilisateurs ne sont pas accessibles à partir de votre Recordset). Les modifications de données effectuées par d'autres utilisateurs demeurent visibles.
Dim $Ret = ""
Dim $arConTokens = StringSplit ( $SelectedConnectionInformation, @TAB )
Dim $ServerName = $arConTokens [ 1 ]
Dim $CatalogName = $arConTokens [ 2 ]
Dim $User = $arConTokens [ 3 ]
Dim $Pwd = $arConTokens [ 4 ]
Dim $RetAr[2]
 $RetAr[0] = 0
 $sqlCon = ObjCreate("ADODB.Connection")
 if $sqlCon = 0 Then
  MsgBox(0,"","failed to create a connection object")
  Exit
 EndIf
 $oMyError = ObjEvent("AutoIt.Error","RecSetOpenError") ; Install a custom error handler
    $ani1 = GUICtrlCreateAvi ( @SystemDir & "\shell32.dll", 165, 10, 50)
 GUISetState()
 GUICtrlSetState($ani1, 1)
 if $ServerName = "." or $ServerName = "" Then
  dim $ConString = "Provider=SQLOLEDB; Data Source=.\" & $CatalogName & "; Initial Catalog=" & $CatalogName & "; User ID=" & $User & "; Password=" & $Pwd & ";"
  $sqlCon.Open($ConString)
 Else
  $sqlCon.Open("Provider=SQLOLEDB; Data Source=" & $ServerName & "\" & $CatalogName & "; Initial Catalog=" & $CatalogName & "; User ID=" & $User & "; Password=" & $Pwd & ";")
 EndIf
 GUICtrlDelete ( $ani1 )
 if $g_eventerror then Return $RetAr
 $oRS = ObjCreate ( "ADODB.Recordset" )
 if $oRS = 0 then return $RetAr
 $oRS.CursorLocation = 2 ;adUseServer
 if StringLeft ( StringLower ( $Qry ), 7 ) = "select " Then
  $oRS.open ( $Qry, $sqlCon, 3, -1) ; adOpenStatic, lock type unspecified
  $intNbLignes = $oRS.recordCount
  if $intNbLignes > 0 Then
   $oRS.MoveFirst
   if $bFirstRowHasColumns = True Then
    for $iField = 0 to $oRS.Fields.Count - 1
     if $iField = 0 Then
      $RetAr [ 1 ] = $oRS.Fields.Item(0).name
     Else
      $RetAr [ 1 ] &= @TAB & $oRS.Fields.Item($iField).name
     EndIf
    Next
   EndIf
   if $bFirstRowHasColumns Then
    ReDim $RetAr [ $intNbLignes + 2 ]
    $RetAr [ 0 ] = $intNbLignes + 1
   Else
    ReDim $RetAr [ $intNbLignes + 1]
    $RetAr [ 0 ] = $intNbLignes
   EndIf
   For $i = 1 To $intNbLignes
    if ControlCommand ( "SQL Server Tool", "", "[CLASS:msctls_progress32; INSTANCE:1]", "IsVisible" ) Then
     GUICtrlSetData ( $pb, ($i * 100) / $intNbLignes )
    EndIf
    for $j = 1 to $oRS.Fields.Count
     if $j > 1 Then
      if $bFirstRowHasColumns Then
       $RetAr [ $i + 1 ] &= @TAB & $oRS.Fields.Item($j - 1).value
      Else
       $RetAr [ $i ] &= @TAB & $oRS.Fields.Item($j - 1).value
      EndIf
     Else
      if $bFirstRowHasColumns Then
       $RetAr [ $i + 1 ] = $oRS.Fields.Item(0).value
      Else
       $RetAr [ $i ] = $oRS.Fields.Item(0).value
      EndIf
     EndIf
    Next
    $oRS.MoveNext
   Next
  EndIf
  $oRS.close
 EndIf
 Return $RetAr
EndFunc
Func RunUserQuery()
 ; check if part of the text in $txtQuery is selected
 Dim $OldClipValue = ClipGet()
 ClipPut("")
 ControlSend ( "SQL Server Tool", "", "[CLASS:Edit; INSTANCE:5]", "^c" )
 Dim $Query = ClipGet()
 ClipPut ( $OldClipValue ); restore the clipboard old value
 if StringLen ( $Query ) = 0 Then
  $Query = GUICtrlRead ( $txtQuery )
 EndIf
 Dim $arQryEditPos = ControlGetPos ( "SQL Server Tool", "", "[CLASS:Edit; INSTANCE:6]" )
 GUICtrlSetPos ( $txtQuery, 222, 33, $arQryEditPos [ 2 ], 33)
 if Not IsValidQuery ( $Query ) Then
  MsgBox(0, "SQLDBViewer", "The query" & @LF & $Query & @LF & "is not a valid query.")
  $TableData = $Query
  Return
 EndIf
 Dim $arTableData = RunQuery ( $Query, True )
 GUICtrlSetPos ( $txtQuery, 222, 16, $arQryEditPos [ 2 ], 50 )
 ; fix the listview columns
 GUICtrlDelete ( $lvTable ); it's easier to just delete and recreate the listview than to clear it and remove its columns...
 ; gather the data needed to calculate where the listview should be recreated
 Dim $arGBPos = ControlGetPos ( "SQL Server Tool", "", "[CLASS:Button; INSTANCE:6]" )
 $lvTable = GUICtrlCreateListView ( "", $arQryEditPos[0], $arQryEditPos[1] + $arQryEditPos[3] + 8, $arQryEditPos[2], $arGBPos[1] + $arGBPos[3] - $arQryEditPos[1] - $arQryEditPos[3] - 8 )
 GUICtrlSetResizing ( $lvTable, $GUI_DOCKLEFT + $GUI_DOCKTOP + $GUI_DOCKRIGHT + $GUI_DOCKBOTTOM )
 GUISetState()  ; display the GUI including the new listview
 Dim $arCols = StringSplit ( $arTableData [ 1 ], @TAB )
 for $iCol = 1 to $arCols [ 0 ] ; create and populate the missing column headers
  _GUICtrlListView_AddColumn ( $LVTable, $arCols [ $iCol ] )
 Next
 ; show the progressbar and resize the input control to make room for it
 GUICtrlSetState ( $pb, $GUI_SHOW )
 GUICtrlSetPos ( $pb, 222, 16, $arQryEditPos [ 2 ] - 4, 17 )
 GUICtrlSetPos ( $txtQuery, 222, 33, $arQryEditPos [ 2 ], 40 );33)
 for $iRow = 2 to UBound ( $arTableData ) - 1 ; row 1 is the column names, skip
  GUICtrlSetData ( $pb, $iRow * 100 / $arTableData [ 0 ] ); show progress
  GUICtrlCreateListViewItem ( StringReplace ( $arTableData [ $iRow ], @TAB, "|" ), $lvTable )
 Next
 ; hide the progressbar and restore the former size to the input control
 GUICtrlSetState ( $pb, $GUI_HIDE )
 GUICtrlSetPos ( $txtQuery, 222, 16, $arQryEditPos [ 2 ], 57 );50 )
 ;create the buffer that will store the data for copying to the clipboard
 $TableData = _ArrayToString ( $arTableData, @CRLF )
 $TableData = StringRight ( $TableData, StringLen ( $TableData ) - StringInStr ( $TableData, @CRLF ) + 2 )
 $TableData = GUICtrlRead ( $txtQuery ) & @CRLF & "--------------" & @CRLF & $TableData
EndFunc
Func SaveINI()
 if StringInStr ( @CRLF & $IniBuf & @CRLF, @CRLF & $SelectedConnectionInformation & @CRLF ) = 0 Then
  if StringLen ( $IniBuf ) < 8 Then
   $IniBuf = $SelectedConnectionInformation
  Else
   $IniBuf &= @CRLF & $SelectedConnectionInformation
  EndIf
 EndIf
 FileDelete( @ScriptDir & "\SQLDBViewer.ini" )
 FileWrite ( @ScriptDir & "\SQLDBViewer.ini", $IniBuf )
EndFunc
Func SetMouseCursor()
 Dim $arEdPos = ControlGetPos ( "SQL Server Tool", "", "[CLASS:Edit; INSTANCE:6]" )
 Dim $arLvPos = ControlGetPos ( "SQL Server Tool", "", "[CLASS:SysListView32; INSTANCE:1]" )
 Dim $arCursorLocation = GUIGetCursorInfo ( $FormHwnd )
 if $arCursorLocation [ 4 ] = $lblSep Then
  if $arCursorLocation [ 2 ] = 1 Then
   ; mouse left button is down
   Dim $arLblSepPos = ControlGetPos ( "SQL Server Tool", "", "[CLASS:Static; INSTANCE:6]" )
   if IsArray ( $arLblSepPos ) Then
    GUICtrlSetPos ( $lblSep, $arLblSepPos [ 0 ], $arCursorLocation [ 1 ] - 6, $arLblSepPos [ 2 ], 12 )
    GUICtrlSetPos ( $txtQuery, $arEdPos [ 0 ], $arEdPos [ 1 ], $arEdPos [ 2 ], $arCursorLocation [ 1 ] - 6 - $arEdPos [ 1 ] )
    GUICtrlSetPos ( $lvTable, $arLvPos [ 0 ], $arCursorLocation [ 1 ] + 6, $arLvPos [ 2 ], $arLvPos [ 3 ] + $arLvPos [ 1 ] - $arCursorLocation [ 1 ] + 6 )
    $SleepTimeMS = 1 ;accelerate reaction to UI changes while dragging
   Else
    ConsoleWrite("$arLblSepPos is  not an array")
   EndIf
  Else
   GUISetCursor ( 2, 0, $FormHwnd )
   $SleepTimeMS = 300 ;restore normal sleep time while updating UI etc
  EndIf
 ElseIf $arCursorLocation [ 1 ] >= $arLvPos [ 1 ] And $arCursorLocation [ 1 ] <= $arLvPos [ 1 ] + $arLvPos [ 3 ] And $arCursorLocation [ 3 ] = 1 Then
  ; right mouse button down on listview, check if that's over a listview item
  Local $hMenu = GUICtrlGetHandle ( $cm )
  ClientToScreen ( $FormHwnd, $arCursorLocation [ 0 ], $arCursorLocation [ 1 ] )
  TrackPopupMenu($FormHwnd, $hMenu, $arCursorLocation [ 0 ], $arCursorLocation [ 1 ] )
 Else
  GUISetCursor ( 2, 0, $FormHwnd )
  $SleepTimeMS = 300 ;restore normal sleep time while updating UI etc
 EndIf
EndFunc
Func ShowDBTables()
 Dim $arTableInfo = RunQuery ( "select TABLE_SCHEMA, TABLE_NAME from INFORMATION_SCHEMA.TABLES", False )
 if $arTableInfo [ 0 ] > 0 Then
  Dim $TableList = _ArrayToString ( $arTableInfo )
  $TableList = StringRight ( $TableList, StringLen ( $TableList ) - StringInStr ( $TableList, @CRLF ) - 2 );strip leading array size and CRLF
  if StringLeft ( $TableList, 1 ) = @TAB Then
   GUICtrlSetData ( $lbTables, StringReplace ( $TableList, @TAB, "" ) ); there is no schema name before the table, just display table names
  else
   GUICtrlSetData ( $lbTables, StringReplace ( $TableList, @TAB, "." ) ); the 1st column is the schema name, the tables should be of format schemaName.tableName
  EndIf
  DisplayTableListGroup ( $GUI_SHOW )
  $bCheckSelectedTable = True
  Return True
 Else
  MsgBox(0,"","No tables, or connection failed...")
  Return False
 EndIf
EndFunc
Func TrackPopupMenu($hWnd, $hMenu, $x, $y)
 DllCall("user32.dll", "int", "TrackPopupMenuEx", "hwnd", $hMenu, "int", 0, "int", $x, "int", $y, "hwnd", $hWnd, "ptr", 0)
EndFunc

SQLDBViewer.au3

Edited December 21, 2011 by rodent1

Reekod · December 16, 2011

So fast : Great Job

Feed Back :

1/ click connect button

2/ enter information in all fields (Server Catalog User Password)

3/ When i enter the first char of my password... the GUI do a strange loop and SHOW / HIDE state are changing for some elements.

Any idea ?

Edited December 16, 2011 by Reekod

rodent1 · December 16, 2011

fixed, I already had stored connection information, and had not gone back to make sure it would work without it. Sorry about that.

Edited December 16, 2011 by rodent1

Reekod · December 16, 2011

Thx for your post :

Actually it's better, but i got an other error :

DEVLDAPSQL_recursive.au3 (259) : ==> The requested action with this object has failed.:
$sqlCon.Open("Provider=SQLOLEDB; Data Source=" & $ServerName & "" & $CatalogName & "; Initial Catalog=" & $CatalogName & "; User ID=" & $User & "; Password=" & $Pwd & ";")
$sqlCon.Open("Provider=SQLOLEDB; Data Source=" & $ServerName & "" & $CatalogName & "; Initial Catalog=" & $CatalogName & "; User ID=" & $User & "; Password=" & $Pwd & ";")^ ERROR

Actually i'm testing it on a SMS SQL Server.

i'm not sure about the $catalog "value" i have to enter.

- SERVER01_SMS (SQL Server 9.0.3042)

-- System DATABASE

|-- Master

|-- Model

|-- Msdb

|-- Tempdb

-- SMS_001

|-- Database schem

|-- Tables

|-- Table

|-- Views

....

An idea ?

Regards.

ReekoD

Edited December 16, 2011 by Reekod

rodent1 · December 16, 2011

The server is the name of the machine SQL Server is installed on. There can be more than one catalog in one SQL Server installation, though. So a catalog is a named set of tables, views, stored procedures, users, user groups, etc, inside a db installation. In some of Microsoft's demo databases, there is only one catalog and its name is AdventureWorks, of NorthWind.

Edited December 19, 2011 by rodent1

Reekod · December 16, 2011

my question was only about the catalog

Maybe my problem was about the right of the user authentification...

i'll gonna test it on monday with a full access user.

see ya

Reekod · December 21, 2011

Is the ligne 312 seem to be correct ?

Dim $arTableInfo = RunQuery ( "select TABLE_SCHEMA, TABLE_NAME from INFORMATION_SCHEMA.TABLES" )

Maybe i can change

TABLE_SCHEMA

TABLE_NAME

INFORMATION_SCHEMA

with some others value ?

rodent1 · December 21, 2011

When I look at the table INFORMATION_SCHEMA.TABLES, the columns are

TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE

I assume you have a problem there.

In the database I work with, the data tables belong to a schema, and they have to be called with this format:

select * from <table_schema>.<table_name>

I have seen databases where the table schema is not used, and you just access your tables with a query with this format:

select * from <table_name>

In that case, I assume that the column TABLE_SCHEMA in the table INFORMATION_SCHEMA.TABLES is empty, and in the code I check for that situation, so that I can generate correct queries. I updated the code I posted with that change several days ago. Are you using the latest post?

In my current code, the line you are talking about is not 512, it's 564. I made minor changes to allow resizing the query textbox and the table data listview. As soon as I post this response, I'll go and edit the post.

Reekod · December 22, 2011

Now the last code version is used

i get another error but it is an authentification problem

i tested the main query

select TABLE_SCHEMA, TABLE_NAME from INFORMATION_SCHEMA.TABLES

With a basic code it's work,

i will now have enough information to adapt the code :

#include<sql.au3>
#include<array.au3>
$sqlCon = ObjCreate("ADODB.Connection")
$sqlCon.Open("DRIVER={SQL Server};SERVER=Server01;DATABASE=SMS_001;UID=admin;Trusted_Connection=Yes;")
$NameList=""
if @error Then
MsgBox(0, "ERROR", "Failed to connect to the database")
Exit
Else
MsgBox(0, "Success!", "Connection to database successful!")
EndIf$result = _SQLQuery($sqlCon,"select TABLE_SCHEMA, TABLE_NAME from INFORMATION_SCHEMA.TABLES")
$liste = FileOpen(@ScriptDir & 'liste_tables.csv',2)With $result
While Not .EOF
$NameList &= .Fields ("TABLE_NAME").value & @CRLF
.MoveNext
WEnd
EndWith
FileWrite($liste, $NameList)
Fileclose($liste)

Thank you for your help !

Edited December 22, 2011 by Reekod

Reekod · January 4, 2012

Hi and Happy new year 2012,

i wrote this from your help script :

i tried to

1 / Display all Table in a listview1

2 / Create a listview2 with the column of the table selected in listview1

3 / Display all values in listview2

i have a problem with the second loop my value does not appear in the ligne & column and i don't know where is my error, and i need help to fix it.

i learn to code only with myself sorry for my noob level.

sql.au3 is an include (see attached file) and my code :

#include <sql.au3>
#include <File.au3>
#include <array.au3>
#include <GuiMenu.au3>
#include <GUIListBox.au3>
#include <GuiTreeView.au3>
#include <GuiListView.au3>
#include <GuiStatusBar.au3>
#include <EditConstants.au3>
#include <GuiConstantsEx.au3>
#include <ButtonConstants.au3>
#include <StaticConstants.au3>
#include <WindowsConstants.au3>
#include <TreeViewConstants.au3>
Global $OU, $hGui, $hListView, $treeOne, $treeItem, $iIndex, $i, $main, $tableselected, $ListeTable1, $colonnes, $username, $servername, $databasename, $GO
#Region ### START Koda GUI section ### Form=
$main = GUICreate("Form1", 1024, 742, 200, 114)
$input1 = GUICtrlCreateInput(@UserName, 88, 8, 141, 21)
$input2 = GUICtrlCreateInput("servername", 88, 32, 141, 21)
$input3 = GUICtrlCreateInput("SMS_CODE", 88, 56, 141, 21)
$Input4 = GUICtrlCreateInput("", 88, 80, 121, 21)
$Input5 = GUICtrlCreateInput("", 88, 104, 121, 21)
$Input6 = GUICtrlCreateInput("", 88, 128, 121, 21)
$Label1 = GUICtrlCreateLabel("Username", 8, 16, 56, 17)
$Label2 = GUICtrlCreateLabel("SERVER", 8, 40, 59, 17)
$Label3 = GUICtrlCreateLabel("DB", 8, 64, 56, 17)
$Label4 = GUICtrlCreateLabel("Label4", 8, 88, 46, 17)
$Label5 = GUICtrlCreateLabel("Label5", 8, 112, 46, 17)
$Label6 = GUICtrlCreateLabel("Label6", 8, 136, 46, 17)
;~ $List1 = GUICtrlCreateListView("", 8, 160, 249, 565)
;~ $List2 = GUICtrlCreateListView("", 264, 160, 545, 565)
$Label7 = GUICtrlCreateLabel("-", 288, 5, 321, 21)
$Label8 = GUICtrlCreateLabel("-", 288, 20, 321, 21)
$Label9 = GUICtrlCreateLabel("-", 288, 35, 321, 21)
$Label10 = GUICtrlCreateLabel("-", 288, 50, 321, 21)
$Label11 = GUICtrlCreateLabel("-", 288, 65, 321, 21)
$Label12 = GUICtrlCreateLabel("-", 288, 80, 321, 21)
Global $ListView1 = _GUICtrlListView_Create($main, "", 8, 160, 249, 565)
Global $ListView2 = _GUICtrlListView_Create($main, "", 264, 160, 745, 565)
_GUICtrlListView_SetExtendedListViewStyle($hListView, BitOR($LVS_EX_GRIDLINES, $LVS_EX_FULLROWSELECT, $LVS_EX_SUBITEMIMAGES))
$Button1 = GUICtrlCreateButton("GO", 216, 128, 75, 25, $WS_GROUP)
;~ $Button2 = GUICtrlCreateButton("Button2", 296, 128, 75, 25, $WS_GROUP)
$fFlagCG = False
$fFlagCG_listview = False
$fFlagTL_listview = False
GUISetState(@SW_SHOW)
#EndRegion ### END Koda GUI section ###
GUIRegisterMsg($WM_NOTIFY, "_WM_NOTIFY")
While 1
$nMsg = GUIGetMsg()
Switch $nMsg
  Case $GUI_EVENT_CLOSE
   Exit
  Case $Button1
   Go()
EndSwitch
If $fFlagCG_listview Then
  $fFlagCG_listview = False
  ListeColumn($tableselected)
EndIf
If $fFlagTL_listview Then
  $fFlagTL_listview = False
;~   MsgBox(64, '', 'coucou')
;~   listtable2($tableselected)
EndIf
WEnd
Func Go()
$servername = GUICtrlRead($input2)
$databasename = GUICtrlRead($input3)
MsgBox(64,'informations', 'U will connect to : ' & $servername & ' on ' & $databasename & ' DB with this : ' &  @UserName)
listtable()
EndFunc   ;==>Go
Func listtable()
_GUICtrlListView_InsertColumn($ListView1, 0, "Tables", '350', -1, -1, False)
$sqlCon = ObjCreate("ADODB.Connection")
$sqlCon.Open("DRIVER={SQL Server};SERVER=" & $servername & ";DATABASE=" & $databasename & ";UID=" & @UserName & ";Trusted_Connection=Yes;")
$NameList = ""
If @error Then
  MsgBox(0, "ERROR", "Failed to connect to the database")
  Exit
Else
;~   MsgBox(0, "Success!", "Connection to database successful!")
EndIf
$result0 = _SQLQuery($sqlCon, "select TABLE_SCHEMA, TABLE_NAME from INFORMATION_SCHEMA.TABLES")
$totali = 0
With $result0
  While Not .EOF
   $ligne0 = .Fields("TABLE_NAME" ).value
;~  $ligneOK0 = StringMid($ligne0, 1, 2)
;~  If $ligneOK0 = 'v_' Then
;~   $ligneOK20 = StringMid($ligne0, 1, 13)
;~   If $ligneOK20 = 'v_CM_RES_COLL' Then
;~   Else
     $totali = $totali + 1
;~   EndIf
;~  Else
;~  EndIf
   .MoveNext
  WEnd
EndWith
;~  MsgBox(64, '', $totali + 1)
$result = _SQLQuery($sqlCon, "select TABLE_SCHEMA, TABLE_NAME from INFORMATION_SCHEMA.TABLES")
Dim $ListeTable1[$totali + 1]
$i = 0
With $result
  While Not .EOF
   $ligne = .Fields("TABLE_NAME" ).value
;~  $ligneOK = StringMid($ligne, 1, 2)
;~  If $ligneOK = 'v_' Then
;~   $ligneOK2 = StringMid($ligne, 1, 13)
;~   If $ligneOK2 = 'v_CM_RES_COLL' Then
;~   Else
     $i = $i + 1
     _GUICtrlListView_AddItem($ListView1, $ligne, $i)
     $ListeTable1[$i] = $ligne
;~   EndIf
;~  Else
;~  EndIf
   .MoveNext
  WEnd
EndWith
Return $ListeTable1
EndFunc   ;==>listtable
Func ListeColumn($tableselected)
_GUICtrlListView_Destroy($ListView2)
$ListView2 = _GUICtrlListView_Create($main, "", 264, 160, 745, 565)
_GUICtrlListView_DeleteAllItems($ListView2)
$sqlCon = ObjCreate("ADODB.Connection")
$sqlCon.Open("DRIVER={SQL Server};SERVER=" & $servername & ";DATABASE=" & $databasename & ";UID=" & @UserName & ";Trusted_Connection=Yes;")
$NameList = ""
$colonnes = ""
$y = 0
If @error Then
  MsgBox(0, "ERROR", "Failed to connect to the database")
  Exit
Else
;~   MsgBox(0, "Success!", "Connection to NEW database successful!")
EndIf
$result0 = _SQLQuery($sqlCon, "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='" & $tableselected & "'")
$total = 0
With $result0
  While Not .EOF
   $total = $total + 1
   .MoveNext
  WEnd
EndWith
Sleep(50)
msgbox(64,'',$total)
Dim $colonneliste[$total]
$queryfile = FileOpen(@ScriptDir & 'queryfull.txt', 2)
$result0 = _SQLQuery($sqlCon, "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='" & $tableselected & "'")
With $result0
  While Not .EOF
   $y = $y + 1
   GUICtrlSetData($Label3, $y)
   $ligne = .Fields("COLUMN_NAME" ).value
   If $y = 0 Then
    $colonneliste[$y] = $total
   Else
    If $y = ($total - 1) Then
     FileWrite($queryfile, $ligne)
     ExitLoop
    Else
     $colonneliste[$y] = $ligne
     FileWrite($queryfile, $ligne & ', ')
    EndIf
   EndIf
;~  $colonnes &= $ligne & ','
   _GUICtrlListView_InsertColumn($ListView2, $y, $ligne, '75', -1, -1, False)
   .MoveNext
  WEnd
EndWith
FileClose($queryfile)
;~  $queryfile = FileOpen(@ScriptDir & 'queryfull.txt', 0)
;~  $listecols = FileReadLine($queryfile, 1)
;~  MsgBox(64, 'listecols', $total & '             ' & $tableselected & '            ' & $listecols)
$result2 = _SQLQuery($sqlCon, 'Select * from ' & $tableselected)
$ii = 1
With $result2
  While Not .EOF
   $test = IsArray($colonneliste)
   If $test = 1 Then
;~   _ArrayDisplay($colonneliste)
    If $ii > ($total - 2) Then
     ExitLoop
    Else
;~  $valueii = StringUpper($colonneliste[$ii])
     $newligne = .Fields($colonneliste[$ii]).value
     _GUICtrlListView_AddItem($ListView2, $newligne)
     For $dropcols = 1 to ($total - 2)
      $newligne2 = .Fields($colonneliste[$dropcols]).value
;~   MsgBox(64, '$ii & $newligne2 &  $dropcols', $ii & ' ' & $newligne2 & ' ' & $dropcols)
      _GUICtrlListView_AddSubItem($ListView2, $ii, $newligne2, $dropcols)
;~   $valuedropcols = StringUpper($colonneliste[$dropcols])
;~   _GUICtrlListView_AddItem($hListView, "Row 1: Col 1", 0)
;~   _GUICtrlListView_AddSubItem($hListView, 0, "Row 1: Col 2", 1)
;~   _GUICtrlListView_AddSubItem($hListView, 0, "Row 1: Col 3", 2)
     Next
    EndIf
   Else
    MsgBox(64, '$test', 'is not an array')
   EndIf
   $ii = $ii + 1
   .MoveNext
  WEnd
EndWith
$fFlagTL_listview = True
Return $colonnes
EndFunc   ;==>ListeColumn
Func listtable2($tableselected)
;~  $sqlCon = ObjCreate("ADODB.Connection")
;~ $sqlCon.Open("DRIVER={SQL Server};SERVER=" & $servername & ";DATABASE=" & $databasename & ";UID=" & @UserName & ";Trusted_Connection=Yes;")
;~  $NameList = ""
;~  If @error Then
;~   MsgBox(0, "ERROR", "Failed to connect to the database")
;~   Exit
;~  Else
;~   MsgBox(0, "Success!", "Connection to database successful!")
;~  EndIf
;~  Return $ListeTable1
EndFunc   ;==>listtable2
Func _WM_NOTIFY($hWnd, $iMsg, $wParam, $lParam)
#forceref $hWnd, $iMsg, $wParam
Local $tNMHDR, $hWndFrom, $iCode
$tNMHDR = DllStructCreate($tagNMHDR, $lParam)
$hWndFrom = HWnd(DllStructGetData($tNMHDR, "hWndFrom"))
$iCode = DllStructGetData($tNMHDR, "Code")
Local $IDFrom = DllStructGetData($tNMHDR, "IDFrom")
Local $tStruct = DllStructCreate("hwnd;uint_ptr;int_ptr;int;int", $lParam) ; Increase the struct size to get the column <<<<<<<<<<<<<<<<
If @error Then Return
Switch DllStructGetData($tStruct, 3)
;~   Menu listview des users = Struct4 (0x00741A46)
;~   Menu Treeview OU = Struct4 (0x0058040E)
  Case $NM_RCLICK ; determine if right click on an item
  Case $NM_DBLCLK ; Look for the double click <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
  Case $NM_CLICK ; determine if Left click on an item
   GUICtrlSetData($Label7, 'DllStructGetData($tStruct, 0) = ' & DllStructGetData($tStruct, 0))
   GUICtrlSetData($Label8, 'DllStructGetData($tStruct, 1) = ' & DllStructGetData($tStruct, 1))
   GUICtrlSetData($Label9, 'DllStructGetData($tStruct, 2) = ' & DllStructGetData($tStruct, 2))
   GUICtrlSetData($Label10, 'DllStructGetData($tStruct, 3) = ' & DllStructGetData($tStruct, 3))
   GUICtrlSetData($Label11, 'DllStructGetData($tStruct, 4) = ' & DllStructGetData($tStruct, 4))
   GUICtrlSetData($Label12, 'DllStructGetData($tStruct, 5) = ' & DllStructGetData($tStruct, 5))
   If DllStructGetData($tStruct, 2) = 10001 Then
    $fFlagCG = True
   EndIf
   If DllStructGetData($tStruct, 2) = 10000 Then
    $fFlagCG_listview = True
    $tableselected = $ListeTable1[DllStructGetData($tStruct, 4) + 1]
     msgbox(64,'$tableselected = ',$tableselected)
    Return $tableselected
   EndIf
   Return 0
EndSwitch
Return $GUI_RUNDEFMSG
EndFunc   ;==>_WM_NOTIFY

include SQL.AU3

#include-once
;===============================================================================
;
; Function Name:    _SQLConnect
; Description:    Initiate a connection to a SQL database
; Syntax:          $oConn = _SQLConnect($sServer, $sDatabase, $fAuthMode = 0, $sUsername = "", $sPassword = "", _
;                      $sDriver = "{SQL Server}")
; Parameter(s):  $sServer - The server your database is on
;                  $sDatabase - Database to connect to
;                  $fAuthMode - Authorization mode (0 = Windows Logon, 1 = SQL) (default = 0)
;                  $sUsername - The username to connect to the database with (default = "")
;                  $sPassword - The password to connect to the database with (default = "")
;                  $sDriver (optional) the ODBC driver to use (default = "{SQL Server}")
; Requirement(s):   Autoit 3 with COM support
; Return Value(s):  On success - returns the connection object for subsequent SQL calls
;                  On failure - returns 0 and sets @error:
;                      @error=1 - Error opening database connection
;                      @error=2 - ODBC driver not installed
;                      @error=3 - ODBC connection failed
; Author(s):        SEO and unknown
; Note(s):        None
;
;===============================================================================
Func _SQLConnect($sServer, $sDatabase, $fAuthMode = 0, $sUsername = "", $sPassword = "", $sDriver = "{SQL Server}")
    Local $sTemp = StringMid($sDriver, 2, StringLen($sDriver) - 2)
    Local $sKey = "HKEY_LOCAL_MACHINESOFTWAREODBCODBCINST.INIODBC Drivers", $sVal = RegRead($sKey, $sTemp)
    If @error or $sVal = "" Then Return SetError(2, 0, 0)
    $oConn = ObjCreate("ADODB.Connection")
    If NOT IsObj($oConn) Then Return SetError(3, 0, 0)
    If $fAuthMode Then $oConn.Open ("DRIVER=" & $sDriver & ";SERVER=" & $sServer & ";DATABASE=" & $sDatabase & ";UID=" & $sUsername & ";PWD=" & $sPassword & ";")
    If NOT $fAuthMode Then $oConn.Open("DRIVER=" & $sDriver & ";SERVER=" & $sServer & ";DATABASE=" & $sDatabase)
    If @error Then Return SetError(1, 0, 0)
    Return $oConn
EndFunc   ;==>_SQLConnect
;===============================================================================
;
; Function Name:    _SQLConnect
; Description:    Send a query to a SQL database and return the results as an object
; Syntax:          $oQuery = _SQLQuery($oConn, $sQuery)
; Parameter(s):  $oConn - A database connection object created by a previous call to _SQLConnect
;                  $sQuery - The SQL query string to be executed by the SQL server
; Requirement(s):   Autoit 3 with COM support
; Return Value(s):  On success - returns the query result as an object
;                  On failure - returns 0 and sets @error:
;                      @error=1 - Unable to process the query
; Author(s):        SEO and unknown
; Note(s):        None
;
;===============================================================================
Func _SQLQuery($oConn, $sQuery)
    If IsObj($oConn) Then Return $oConn.Execute($sQuery)
    Return SetError(1, 0, 0)
EndFunc ;==>_SQLQuery
;===============================================================================
;
; Function Name:    _SQLDisconnect
; Description:    Disconnect and close an existing connection to a SQL database
; Syntax:          _SQLDisconnect($oConn)
; Parameter(s):  $oConn - A database connection object created by a previous call to _SQLConnect
; Requirement(s):   Autoit 3 with COM support
; Return Value(s):  On success - returns 1 and closes the ODBC connection
;                  On failure - returns 0 and sets @error:
;                      @error=1 - Database connection object doesn't exist
; Author(s):        SEO and unknown
; Note(s):        None
;
;===============================================================================
Func _SQLDisconnect($oConn)
    If NOT IsObj($oConn) Then Return SetError(1, 0, 0)
    $oConn.Close
    Return 1
EndFunc   ;==>_SQLDisconnect

Edited January 4, 2012 by Reekod

SQL Server DB Viewer

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Create an account or sign in to comment

Create an account

Sign in

Recently Browsing 0 members