Sign in to follow this  
Followers 0
Reekod

How to display SQL BD table

4 posts in this topic

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

Share this post


Link to post
Share on other sites



#2 ·  Posted (edited)

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:

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

Share this post


Link to post
Share on other sites

Thx a lot

i'll test it asap

Share this post


Link to post
Share on other sites

#4 ·  Posted (edited)

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

Share this post


Link to post
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
Sign in to follow this  
Followers 0