Sign in to follow this  
Followers 0
rodent1

SQL CE database viewer

2 posts in this topic

#1 ·  Posted (edited)

Friends,

This may be useful to someone.

This is a GUI tool that allows me to quickly access data from SQL Compact (SQL CE) (*.sdf) database files. My testing automation generates many such database files, and it's been handy.

SQL CE is a very lightweight database system that was originally written for phones etc.

To register the tool to open .sdf files, from the command line, type SQLCETool /i

to unregister it, from the command line, type SQLCETool /u

Once registered, there are several ways to run the tool.

if the tool is up, you can drag a .sdf file from explorer to the "SDF File" edit box, click on the open button. The tables in the database will then be listed. Select a table, and its contents will be displayed.

Or paste the fully qualified name of the database and click open, etc.

If the focus is on another application, the tool rolls up.

If the mouse is hovering on the rolled up tool, it will return to its normal size.

Enjoy!

And for this to properly read databases, you will need to have SQL CE installed. This is hard coded to use version 4.0, though just changing the database connection string to use another version should work fine.

1/12/2012 (2nd link below): added commandline input of queries so that the tool can be called and directly display the query; fixed a bug that caused flickering; changed handling of Escape or F5 key press so that it's only used if the tool has the focus; updated handling of database errors.

SQLCETool.au3

SQLCETool.au3

Edited by rodent1

Share this post


Link to post
Share on other sites



#2 ·  Posted (edited)

This version for SQL you send me.

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 Reekod

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