Recent

Author Topic: Anyone got a database viewer to share?  (Read 5772 times)

tjpren

  • Jr. Member
  • **
  • Posts: 67
Anyone got a database viewer to share?
« on: April 08, 2012, 05:03:29 am »
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

  • Hero Member
  • *****
  • Posts: 8310
  • Programming + Glam Metal + Tae Kwon Do = Me
Re: Anyone got a database viewer to share?
« Reply #1 on: April 08, 2012, 08:31:41 am »
If you open $(LazarusDir)/tools/lazdatadesktop, you've got one there. It could connect to any SQLDB supported database engines.

tjpren

  • Jr. Member
  • **
  • Posts: 67
Re: Anyone got a database viewer to share?
« Reply #2 on: April 08, 2012, 01:47:58 pm »
Thanks Leledumbo,

that's a good start - much appreciated.

Regards

tjpren

  • Jr. Member
  • **
  • Posts: 67
Re: Anyone got a database viewer to share?
« Reply #3 on: April 09, 2012, 10:50:05 am »
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

  • Hero Member
  • *****
  • Posts: 1173
Re: Anyone got a database viewer to share?
« Reply #4 on: April 09, 2012, 11:35:57 am »
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

  • Jr. Member
  • **
  • Posts: 67
Re: Anyone got a database viewer to share?
« Reply #5 on: April 09, 2012, 12:34:02 pm »
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

  • Hero Member
  • *****
  • Posts: 1173
Re: Anyone got a database viewer to share?
« Reply #6 on: April 09, 2012, 12:48:27 pm »
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

  • Jr. Member
  • **
  • Posts: 67
Re: Anyone got a database viewer to share?
« Reply #7 on: April 09, 2012, 01:38:34 pm »
Awesome.  That works.  Thanks heaps.

tjpren

  • Jr. Member
  • **
  • Posts: 67
Re: Anyone got a database viewer to share?
« Reply #8 on: April 10, 2012, 12:47:17 pm »
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

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: Anyone got a database viewer to share?
« Reply #9 on: April 10, 2012, 02:45:31 pm »
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....
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

tjpren

  • Jr. Member
  • **
  • Posts: 67
Re: Anyone got a database viewer to share?
« Reply #10 on: April 10, 2012, 11:21:21 pm »
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

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: Anyone got a database viewer to share?
« Reply #11 on: April 11, 2012, 10:34:12 am »
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
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

 

TinyPortal © 2005-2018