Jump to content

SQL Server DB/Table Viewer

Reekod

By Reekod,
in AutoIt General Help and Support

 Share

Recommended Posts

Reekod Wayfarer

Reekod

Posted
Posted

Hi all and Happy new year 2012,

i wrote a script to browse any MS-SQL DB table and them value in

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,

if someone can help me and 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

sql.au3

Link to comment
Share on other sites
  • Moderators
Melba23 Universalist

Melba23

Posted
  • Moderators
Posted

Reekod,

Please do not bump your posts within 24 hours. :)

Remember this is not a 24/7 support forum - those who answer are only here because they like helping others and have some time to spare. You just have to wait until someone who knows something about your particular problem, and is willing to help, comes online. Be patient and someone will answer eventually. ;)

M23

Public_Domain.png.2d871819fcb9957cf44f4514551a2935.png Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind

Open spoiler to see my UDFs:

Spoiler

ArrayMultiColSort ---- Sort arrays on multiple columns
ChooseFileFolder ---- Single and multiple selections from specified path treeview listing
Date_Time_Convert -- Easily convert date/time formats, including the language used
ExtMsgBox --------- A highly customisable replacement for MsgBox
GUIExtender -------- Extend and retract multiple sections within a GUI
GUIFrame ---------- Subdivide GUIs into many adjustable frames
GUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView items
GUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeView
Marquee ----------- Scrolling tickertape GUIs
NoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxes
Notify ------------- Small notifications on the edge of the display
Scrollbars ----------Automatically sized scrollbars with a single command
StringSize ---------- Automatically size controls to fit text
Toast -------------- Small GUIs which pop out of the notification area

 

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share
Go to topic listing

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...