How to display SQL BD table

[Reekod]

Hi all,

In this code i display all computername of my SCCM SQL Server. I need to do more complex query for some reports but i don't know how to do some basic query.

Can you help me to :

1st/ Display in an array each table of the data base

2nd/ Display * from a specific table

(ex: display all value rows/cols from v_R_system in an array)

#include<sql.au3>
#include<array.au3>
$sqlCon = ObjCreate("ADODB.Connection")
$driver = 'DRIVER={SQL Server}'
$server = 'SERVER=servename'
$DB1 = 'DATABASE=DBNAME'
$user = 'UID=USERNAME'
$TC = 'Trusted_Connection=Yes'
$sqlCon.Open($driver & ';' & $server & ';' & $DB1 & ';' & $user & ';' & $TC & ';')
$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 name0 from v_R_System")
With $result
While Not .EOF
$NameList &= .Fields ("name0").value & @CRLF
.MoveNext
WEnd
EndWith

Thx for reading.

Regards.

ReekoD

[rodent1]

Hi,

There is a post that does something like what your're asking for, but for SQL Compact. It asks for the list of tables, posts the list in a listbox, then when a table is selected, it displays its contents.

It is located here:

To get a list of tables, this query should work:

SELECT table_name FROM INFORMATION_SCHEMA.TABLES

Here is a function I wrote for another script that retrieves data from a SQL Server database:

expandcollapse popup

Func RunQuery ( $Qry, $TestFile = "" )
Dim $sqlCon
Dim $oRS
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 = ""
 $sqlCon = ObjCreate("ADODB.Connection")
 if $sqlCon = 0 Then
  MsgBox(0,"","failed to create a connection object")
  Exit
 EndIf
 if $ServerName = "." or $ServerName = "" Then
  $sqlCon.Open("Provider=SQLOLEDB; Data Source=MyDatabase; Initial Catalog=MyData; User ID=user; Password=password;")
 Else
  if StringLen($sServerIP) Then
   $sqlCon.Open("Provider=SQLOLEDB; Data Source=" & $sServerIP & "MyData; User ID=user; Password=password;")
  Else
   $sqlCon.Open("Provider=SQLOLEDB; Data Source=" & $ServerName & "MyDatabase; Initial Catalog=MyData; User ID=user; Password=password;")
  EndIf
 EndIf
 $oRS = ObjCreate ( "ADODB.Recordset" )
 if $oRS = 0 then
  AddToLog ( "Failed to connect to the SQL database on server '" & $Servername & "'. Exiting...", "", 2 )
  Exit(1)
 EndIf
 $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
   Dim $RetAr = StringSplit ( "", " " )
   $oRS.MoveFirst
   For $i = 1 To $intNbLignes
    if $i > 1 Then ReDimStr1Dim ( $RetAr, $i )
    $RetAr [ $i ] = $oRS.Fields.Item(0).value
    $oRS.MoveNext
   Next
   $Ret = $RetAr [ 1 ]
  EndIf
  $oRS.close
 EndIf
 Return $Ret
EndFunc

Edited December 14, 2011 by rodent1

[Reekod]

Thx a lot

i'll test it asap

[rodent1]

I needed something similar to what you were asking for, so I wrote something fast that you may want to have a look at and just posted it here.

Edited December 15, 2011 by rodent1