Anyone got a database viewer to share?
lazarus / 16-Databases

----------------------------------------------------------------------
tjpren · 2012-04-08 03:03

Hello,

I was wondering if anyone might have a good database viewer they might
share - particularly one to connect to mssql by ODBC.

I've written some, but they are pretty basic, and lack some
refinements - sorting, searching, insert, delete etc.

I've tried some of the full featured examples from other websites that
have lazarus compatability - eg from KGrid- but they seem to require a
lot extra packages, or I couldn't get them to work.  I'm trying to
just use the basic lazarus IDE (0.9.30, and FPC 2.4.2).

If anyone has a good viewer, that I can mod; it would be appreciated.

Regards

----------------------------------------------------------------------
Leledumbo · 2012-04-08 06:31

If you open $(LazarusDir)/tools/lazdatadesktop, you've got one there.
It could connect to any SQLDB supported database engines.

----------------------------------------------------------------------
tjpren · 2012-04-08 11:47

Thanks Leledumbo,

that's a good start - much appreciated.

Regards

----------------------------------------------------------------------
tjpren · 2012-04-09 08:50

Hello All,

I'm using a few examples to make my "universal MSSQL viewer", and it
is coming on OK.

I've got a problem trying to list the tables of a database.  My select
query is fine if I know what the table is, but I'd like to list them,
in a list box, for selection by the user.

 I'm using a Zeos connector with  TZQuery, TZConnection, and a
TDatasource.

I've seen a Gettablenames, but couldn't find how to use it (most info
is for Delphi).

Any help??

Regards

----------------------------------------------------------------------
ludob · 2012-04-09 09:35

To list all the tables in the current database in Memo1 do
Code:
[Select]ZConnection1.GetTableNames(ZConnection1.Database,Memo1.Lines);

The first parameter is the database name.

To select tables with a name starting with 'user' in database 'mysql'
use 
Code: [Select]ZConnection1.GetTableNames('user%','mysql',Memo2.Lines);

The first parameter is a table name pattern and the second the
database name.
Note the sql wildchar '%' used in the table name pattern.

----------------------------------------------------------------------
tjpren · 2012-04-09 10:34

Hi Ludob,

I'm probably doing something wrong, but any how my code is as follows:

Code: [Select]procedure TForm1.Button1Click(Sender: TObject);
begin
    sa.Database := edit2.text;
    sa.HostName := edit3.text;
    sa.Name := edit4.text;
    sa.Password := edit5.text;
    sa.Connected :=True;
     
    sa.GetTableNames(sa.Database,Memo1.Lines);

end; 
The sa connection is the name for the TZConnection, so I think is
close to your example.

When I run, i get no errors, but the memo box is empty.

----------------------------------------------------------------------
ludob · 2012-04-09 10:48

On MSSQL you have to specify the schema name as first parameter. The
example I gave was for MySQL that doesn't have schemes.

To show all tables the user can access, use :Code:
[Select]sa.GetTableNames('',Memo1.Lines);Unfortunately this doesn't
return the schema names, only the table names.

ZeosLib 7.0.0 dev from svn:
https://zeoslib.svn.sourceforge.net/svnroot/zeoslib/trunk

----------------------------------------------------------------------
tjpren · 2012-04-09 11:38

Awesome.  That works.  Thanks heaps.

----------------------------------------------------------------------
tjpren · 2012-04-10 10:47

Hi again,

I'd like to copy the contents of selected rows from the DBGrid to the
clipboard.

From what I've seen, I know I have to declare Uses Clipbrd.

I thought some basic code like:

Code: [Select]procedure TForm1.DBGrid1DblClick(Sender: TObject);
 var
  SelectedItems :TStringList;
begin
  SelectedItems := TStringList.Create();
  try
    Clipboard.AsText := SelectedItems.Text;
  finally
    SelectedItems.Free();
  end;
end;    
This doesn't work of course - no items get selected. 

Also, when I double click, all my multiple selections disappear.

Any thought?

Thanks again

----------------------------------------------------------------------
BigChimp · 2012-04-10 12:45

Why don't you open another thread/topic because the subject has
changed. This makes it more likely for people who know the answer to
actually look at the thread based on the title.
Also, future searches on topics will be easier....

----------------------------------------------------------------------
tjpren · 2012-04-10 21:21

Hello,

Yes , you are right.  (I've got my data base viewer to share, just
wanted to try and fix a few glitches before I uploaded it.)

With thanks to those who have helped, here is my contribution to a
MSSQL viewer.

It is similar to the lazarus wiki for MySQL
(http://wiki.freepascal.org/Lazarus_Database_Tutorial), but
specifically geared to MSSQL.

It is quite simple, in that it:
1. connects to a datasource
2. Lists the tables
3. Displays the requested table in a grid
4. Allows the user to build an SQL query
5. Allows the user to do a basic field sort
6. Allows the field sort in ascending or descending order by a
checkbox

It uses the ZEOS library for MSSQL connectivity.

I tried to program something similar to the MSQRY32.exe that comes
with MS Office, which I find very useful.  It obviously falls short,
but is a step toward
the goal.

Feel free to use and modify,

When I add more functionality, I'll upload a newer version.

Regards

----------------------------------------------------------------------
BigChimp · 2012-04-11 08:34

Thanks for the code tjpren.

Have you looked at LazDataDesktop? IIRC, it can do the things your
tool can as well and is included with Lazarus (it uses sqldb instead
of zeos).
On older versions of Lazarus, MS SQL Server connectivity is limited to
ODBC.
If you are running Lazarus SVN and FPC 2.7, you could even add native
MS SQL Server support (using FreeTDS dblib.dll), so you don't need to
go via ODBC anymore...

Laz developers will be happy with patches to improve Lazdatadesktop
(at least one of them told me)...

Regards,
BigChimp