subject:"\[Lazarus\] Cross Platform SQL Client\/Editor"




On Sun, 3 Jan 2016, Michael Van Canneyt wrote:




On Sun, 3 Jan 2016, Anthony Walter wrote:


Michael,

Regarding select into, it is needed because many times you select into in
order to create a table without needing to define it.


As I said, please create a bug report for this particular case.

As for script execution and executing only the selection, I am working on the 
solution already.




I have committed a solution for execution of selection and execution of
multiple statements (a script).

It's not yet perfect, but works. It uses the SQL script tool.

The execute button now has a dropdown which can be used to enable 1 of 4 modes:
- Execute contents as single statement.
- Execute selection as single statement.
- Execute contents as SQL script statement.
- Execute selection as SQL script statement.

Selection Execution is enabled if the selection contains more than 1 word.

If the execute button is pressed, then a heuristic is used to decide what to
do:
- If there is a valid selection, then selection execution is selected

- If multiple statements are detected (it counts the number of ;
  characters), script execution is selected

No doubt this can be refined.

After each statement, the results are printed in the bottom memo.

If during script execution an error happens, a message is presented with 4
choices:
- Repeat statement
- Continue, stop on next error
- Continue, do not stop on errors
- Abort.

Notably missing: explicit transaction support.

Please test, and suggestions for improvements/changes are welcome.

Michael.

--
___
Lazarus mailing list
Lazarus@lists.lazarus.freepascal.org
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus

Re: [Lazarus] Cross Platform SQL Client/Editor

2016-01-03 Thread Anthony Walter

Thanks, I'll test it out.
--
___
Lazarus mailing list
Lazarus@lists.lazarus.freepascal.org
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus

Re: [Lazarus] Cross Platform SQL Client/Editor




On Sat, 2 Jan 2016, Anthony Walter wrote:


Michael,

I checked out the lasted code from
svn/fpc/trunk/packages/fcl-db/src/datadict and
svn/lazarus/trunk/tools/lazdatadesktop.

Everything builds fine and it looks/works a lot better. Although I can now
connect to a remote mssql server, I found some problems with the "Run
query".

Here is a short issue list of the problems I nottice off-hand:

1) It doesn't handle typos or incorrect SQL statement very well. The app
presents an uncaught exception error dialog and asks you for permission to
terminate lazdatadesktop.


This is the standard exception dialog of lazarus.



2) It doesn't allow a selected block of text to be run, rather it tries to
execute everything in the query window (see also issue #7).


This is by design.

I personally consider this use case to be fundamentally flawed.
How am I to choose the difference between the 2 cases ?

But I am willing to reconsider this, as I know this is a personal preference.
So how about adding a second button to just execute the selection ?



3) It doesn't allow for multiple datasets to be returned.


This is by design. SQLDB does not allow this either (if it does for an
engine, it is purely by accident). You can execute 1 statement at a time.

Allowing more statements would require advanced parsing of SQL to detect the
various statements and submit them separately to the engine.

I have plans for a script window, however. (see below about this all)


4) Running "select columns into dest_table from source_table" crashes
lazdatadesktop as described in issue #1


Probably because SQLDB detects this as a select statement, not as an insert
statement. This syntax is specific to MS-SQL server. Normally, you do a

"insert into desttable (fields) select fields from sourcetable"

Please report a bug about this.


5) Running "create database new_database" crashes lazdatadesktop as
described in issue #1.


What is the exact error ?


6) It seems to want to return a live editable dataset and you must have to
press a stop button before the run query button is enabled again. There
should be some option to allow for a query to be run either without being
live, or change run to allow it to close then open a new query.


I am not sure what you mean here ?



7) I'm not sure how useful the next/previous buttons are. I think most
people tend to want to maintain a big script they keep on disk containing
"if exists () drop table/procedure/function" followed by a "create
table/procedure/function" statement. They don't want a bunch of small SQL
clips kept in memory which they use forward and back arrows to navigate.


I have never seen anyone work like what you describe. 
I do use scripts of course.


The "big script" use case - as you put it - is not meant for this window.

The window is there to test and execute single statements. Keeping that in
mind, the history mechanism is useful. It exists in all other tools I use or
have used.

But as I said:

I have plans for a script window, which is what you are looking for.

You should keep in mind that the ability to execute multiple statements at once 
is not
supported by all SQL engines. I am not aware of what MS-SQL server does, but
offhand, the only engine I know that does it is Postgres. MySQL and Firebird
certainly do not.

SQLDB is not designed for it, so support for this depends on a separate
scripting tool which detects the statements (see the sqlscript unit), and
using the sqlscript tool, I will build a scripting window.



8) There should be a database selector to allow the user to change
databases on connections which support that functionality.


That approach is simply not possible using SQLDB. 
in SQLDB, 1 connection = 1 database.


But see below.


To fix:

All error messages related to SQL execution issues (table already exists,
invalid statements, ect) should be routed to a message area below the
script window. In this way a user can examine the message while trying to
fix their SQL, rather than having to dismiss a window and trying to
remember the details of the error message while trying to fix their SQL. In
all cases, users should never be presented with error dialog asking for
permission to terminate the app because their SQL is invalid.


Agreed 100%, now the window just uses the default exception mechanism.
I will work on this at once.



Create database and database switching need to work. If I have a SQL
client/editor which cannot run a  "create database" statement, then that
editor is broken IMO.


Agreed about the create.

Switching : see below.


Every SQL client/editor where the connection supports multiple databases
(mssql, firebird, mysql, postgre) should give users the ability to switch
databases without the need to define new connections.


If you write this, I think you have never used firebird :)


 Users should be able
to see the databases for a given connection and have an easy way to switch
the connection to a different database, possible

Re: [Lazarus] Cross Platform SQL Client/Editor

2016-01-03 Thread Anthony Walter

Michael,

Regarding select into, it is needed because many times you select into in
order to create a table without needing to define it. This is especially
useful and perhaps almost necessary when creating temporary tables to
cache/persists a result for a session. When I do work processing huge
volumes of medial database records looking for pill mill facilities I need
to use these kinds of intermediate queries to filter billions of records
down to a manageable size.

Example:

select
  p.pracitioner_id,
  h.drug_code,
  i.icd_code,
  min(h.visit_date) [min_visit_date],
  count(*) [visit_count]
into
  #prescriptions
from
  hcfa h
  join icd10 i on i.icd_code = h.icd_code
  join pracititioner p on p.pracitioner_id = h.pracitioner_id
  join pracititioner_credentials pc on pc.pracititioner_id = p.pracititioner
where
  h.visit_date between '2015-01-01' and '2015-12-31 11:59:59 AM'
  and pc.certification = 'MD'
group by
  p.pracitioner_id,
  h.drug_code,
  i.icd_code
having
  count(*) > 3;

-- #prescriptions columns might actually be quite a bit more in
number/variety
-- reuse #prescriptions to check for other similar activity in the paste
-- when done #prescriptions is dropped

Now regarding an execute script window which allows for multiple blocks of
SQL to be execute and multiple result sets, this is what I and probably
everyone else I have ever worked with would need. When I create a temp
table like #prescriptions (which is one of the things everyone in
healthcare coverage IT development needs to do 10x daily) I do it in steps.
First I might start be creating a temp table out of the claims tables (hcfa
is outpatient claims). Then I check a portion of the temp table to make
sure the data looks right. Next I use the temp table to pull information
from the archives tables, and compare the results usually with sections of
multiple dataset return.

This is all standard stuff and it's not entirely limited to healthcare
database work. Healthcare IT developers are certainly the busiest when it
comes to checking records for practitioners or clients looking for new ways
to abuse the system.
--
___
Lazarus mailing list
Lazarus@lists.lazarus.freepascal.org
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus

Re: [Lazarus] Cross Platform SQL Client/Editor




On Sun, 3 Jan 2016, patspiper wrote:


On 02/01/16 19:06, Michael Van Canneyt wrote:



On Sat, 2 Jan 2016, Anthony Walter wrote:


Michael,

Just FYI currently there is no "fpddmssql.pp" (the dd is for data
dictionary I suppose) so no, it's not as easy as just adding a unit name 
to

the project source. New units must be created and a register function must
be create for mssql to appear in the connections list.


I have just committed initial support for this.


FPC trunk for WinCE fails to build now:
Compiling ./fcl-db/src/datadict/fpddmssql.pp
fpddmssql.pp(52,6) Fatal: Can't find unit mssqlconn used by fpddmssql
Fatal: Compilation aborted

Shall I submit a bug report?


Please do.

Michael.

--
___
Lazarus mailing list
Lazarus@lists.lazarus.freepascal.org
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus

Re: [Lazarus] Cross Platform SQL Client/Editor




On Sun, 3 Jan 2016, Anthony Walter wrote:


Michael,

Regarding select into, it is needed because many times you select into in
order to create a table without needing to define it.


As I said, please create a bug report for this particular case.

As for script execution and executing only the selection, I am working on the 
solution already.

The exception handling is already done, I hope you will like the solution.
(I haven't committed this yet, but it is functional)

I am also reworking the various panels to frames, so they can be designed 
visually,
this should lower the treshold for people wishing to contribute.
Frames didn't work at all when the database desktop was initially designed...

We'll get there in the end.

Michael.

--
___
Lazarus mailing list
Lazarus@lists.lazarus.freepascal.org
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus

Re: [Lazarus] Cross Platform SQL Client/Editor

2016-01-03 Thread patspiper


On 03/01/16 17:44, Michael Van Canneyt wrote:



On Sun, 3 Jan 2016, patspiper wrote:


On 02/01/16 19:06, Michael Van Canneyt wrote:



On Sat, 2 Jan 2016, Anthony Walter wrote:


Michael,

Just FYI currently there is no "fpddmssql.pp" (the dd is for data
dictionary I suppose) so no, it's not as easy as just adding a unit 
name to
the project source. New units must be created and a register 
function must

be create for mssql to appear in the connections list.


I have just committed initial support for this.


FPC trunk for WinCE fails to build now:
Compiling ./fcl-db/src/datadict/fpddmssql.pp
fpddmssql.pp(52,6) Fatal: Can't find unit mssqlconn used by fpddmssql
Fatal: Compilation aborted

Shall I submit a bug report?


Please do.


http://bugs.freepascal.org/view.php?id=29319

Stephano

--
___
Lazarus mailing list
Lazarus@lists.lazarus.freepascal.org
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus

Re: [Lazarus] Cross Platform SQL Client/Editor

2016-01-03 Thread patspiper


On 02/01/16 19:06, Michael Van Canneyt wrote:



On Sat, 2 Jan 2016, Anthony Walter wrote:


Michael,

Just FYI currently there is no "fpddmssql.pp" (the dd is for data
dictionary I suppose) so no, it's not as easy as just adding a unit 
name to
the project source. New units must be created and a register function 
must

be create for mssql to appear in the connections list.


I have just committed initial support for this.


FPC trunk for WinCE fails to build now:
Compiling ./fcl-db/src/datadict/fpddmssql.pp
fpddmssql.pp(52,6) Fatal: Can't find unit mssqlconn used by fpddmssql
Fatal: Compilation aborted

Shall I submit a bug report?

Stephano

--
___
Lazarus mailing list
Lazarus@lists.lazarus.freepascal.org
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus

Re: [Lazarus] Cross Platform SQL Client/Editor




On Sun, 3 Jan 2016, patspiper wrote:


On 03/01/16 17:44, Michael Van Canneyt wrote:



On Sun, 3 Jan 2016, patspiper wrote:


On 02/01/16 19:06, Michael Van Canneyt wrote:



On Sat, 2 Jan 2016, Anthony Walter wrote:


Michael,

Just FYI currently there is no "fpddmssql.pp" (the dd is for data
dictionary I suppose) so no, it's not as easy as just adding a unit name 
to
the project source. New units must be created and a register function 
must

be create for mssql to appear in the connections list.


I have just committed initial support for this.


FPC trunk for WinCE fails to build now:
Compiling ./fcl-db/src/datadict/fpddmssql.pp
fpddmssql.pp(52,6) Fatal: Can't find unit mssqlconn used by fpddmssql
Fatal: Compilation aborted

Shall I submit a bug report?


Please do.


http://bugs.freepascal.org/view.php?id=29319


You must have a nonstandard system somehow ?

T:=P.Targets.AddUnit('fpddmssql.pp', DatadictOSes*MSSQLOSes);
with T.Dependencies do
  begin
  AddUnit('sqldb');
  AddUnit('fpdatadict');
  AddUnit('fpddsqldb');
  AddUnit('mssqlconn');
end;

Unless there is a compiler error, DatadictOSes*MSSQLOSes should not contain 
WinCE, since wince is not in
MSSQLOSes, hence fpddmssql.pp must not be compiled at all ?

Michael.

--
___
Lazarus mailing list
Lazarus@lists.lazarus.freepascal.org
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus

Re: [Lazarus] Cross Platform SQL Client/Editor




On Fri, 1 Jan 2016, Anthony Walter wrote:


Michael,

I don't want a "visual" table builder and I'm unsure how many others do.


I don't want a visual table builder either, where did you see that
functionality ?

If you are referring to the data dictionary: 
this is not the only part of the desktop.



All I want is something to connect me to a database engine where I can then
execute bits of SQL (it executes what you highlight), which is what my
program does.


This is what the database desktop also does.



Lazarus database desktop seems geared towards everything I don't want. I
don't see MSSQL support.


That is a matter of including the mssql unit. 
It didn't exist at the time the database desktop was written.



I don't want to setup ODBC DSNs, I just want a straight MSSQL connection.


You will have it. You don't need to set up ODBC at all ?


I don't see an simple create/test connection screen.


Eh ? What do you see under the 'Connections' menu ?


Right clicking in a blank list view to get a menu and then
selecting an option is just bad design.


See above.


Straight off I don't see a SQL
editor either.


Of course not, you need a connection first. 
Then you'll jump straight to the sql editor.


The sql editor is no good without a connection.



So I think you'll understand why I don't want to use Lazarus database


No, I honestly don't. I do understand from what you write that you didn't 
investigate very deeply.



desktop, and no I don't want to improve it. I'd rather have a stand alone
program for anyone to use on any platform which can connect to MSSQL (or
MySQL) and let you edit and execute SQL. And finally I don't see the point
of using the Lazarus moniker for such a tool.


Sure, suit yourself.

That is the despair and frustration of Open source: everybody suits himself.

The Database tool is committed to the lazarus SVN in the hope that people
will improve on it. Its design may be improved. I am aware the design is
not intuitive, but there is simply a historical reason for the current
design. It is not set in stone, far from it.

We cannot do everything outselves:
- Create basic functionality in rtl/fcl/lcl
- Fix bugs
- Create documentation
- Create useful tools
- Provide help on mailing lists

If everyone shoots off at yet another tangent instead of improving what
exists, no progress is made.

Please do not take this as a token that I do not appreciate your efforts,
but it is very frustrating to see the wheel being reinvented 100 times over.

Michael.

--
___
Lazarus mailing list
Lazarus@lists.lazarus.freepascal.org
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus

Re: [Lazarus] Cross Platform SQL Client/Editor

2016-01-02 Thread Mark Morgan Lloyd


Graeme Geldenhuys wrote:

On 2016-01-02 00:11, Mark Morgan Lloyd wrote:
I've yet to see a GUI frontend which is remotely usable when 
attached to a table with a few billion rows.


Then I guess you have never seen the setting in most GUI frontends that
limit the return result to say 500 records max. All the GUI SQL Editors
I've used supports this. In fact, they normally use the feature that
already exists in most SQL database servers to accomplish this.


Actually yes, I have. And almost always found it broken once one starts 
trying to give it complex selection criteria.


--
Mark Morgan Lloyd
markMLl .AT. telemetry.co .DOT. uk

[Opinions above are the author's, not those of his employers or colleagues]

--
___
Lazarus mailing list
Lazarus@lists.lazarus.freepascal.org
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus

Re: [Lazarus] Cross Platform SQL Client/Editor




On Sat, 2 Jan 2016, Graeme Geldenhuys wrote:


On 2016-01-02 00:10, Anthony Walter wrote:

Straight off I don't see a SQL
editor either.


It of course has that - the Laz Data Desktop just doesn't have a very
intuitive interface. It also has some bugs or oddities. But saying that,
it also has some useful features like extendible code generation etc.


Patches or suggestions for changes are highly appreciated.

Michael.

--
___
Lazarus mailing list
Lazarus@lists.lazarus.freepascal.org
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus

Re: [Lazarus] Cross Platform SQL Client/Editor




On Sat, 2 Jan 2016, Graeme Geldenhuys wrote:


On 2016-01-02 09:38, Michael Van Canneyt wrote:

but it is very frustrating to see the wheel being reinvented 100 times over.


+1

The perils of open source in general. Not everything needs to be
reinvented over and over - sometimes fixes and tweaks are all that
should be required to move forward.


In this spirit:

See my other mail about the lazarus desktop...

Michael.

--
___
Lazarus mailing list
Lazarus@lists.lazarus.freepascal.org
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus

Re: [Lazarus] Cross Platform SQL Client/Editor

2016-01-02 Thread Graeme Geldenhuys

On 2016-01-02 09:38, Michael Van Canneyt wrote:
> but it is very frustrating to see the wheel being reinvented 100 times over.

+1

The perils of open source in general. Not everything needs to be
reinvented over and over - sometimes fixes and tweaks are all that
should be required to move forward.


Regards,
  - Graeme -



--
___
Lazarus mailing list
Lazarus@lists.lazarus.freepascal.org
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus

Re: [Lazarus] Cross Platform SQL Client/Editor

Michael,

Thanks for the changes. I am checking them out and will create a new build
to test the changes. If it works I may contribute a few changes to improve
usability such as a connection wizard or some other dialog to guide first
time users in creating/configuring a database connection. I know from my
experience that I had to tweak the mssql connection to allow for statements
such as "create database". I will test this. Also there should be a
database selector, which when dealing with mssql allows the same connection
to address a different database on the same server instance. Again I will
check for this.
--
___
Lazarus mailing list
Lazarus@lists.lazarus.freepascal.org
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus

Re: [Lazarus] Cross Platform SQL Client/Editor

On Sat, 2 Jan 2016, Anthony Walter wrote:

Michael,

Just FYI currently there is no "fpddmssql.pp" (the dd is for data
dictionary I suppose) so no, it's not as easy as just adding a unit name to
the project source. New units must be created and a register function must
be create for mssql to appear in the connections list.

I have just committed initial support for this.

And as far as your surprise at my lack of research goes, you need to
rethink about discovery. The first thing your app does is show a blank list
view of data dictionaries rather than help users create a database
connection first. Next after switching to the connections tab you have to
know to right click in the blank list view to get a context menu which
gives you the option to create a connection. Forcing users to find your
hidden options by using right click context menus is terrible design. Next
we get to the point where these is no option for mssql. You say you simply
include a unit, but in reality no mssql connection register unit currently
exists, so it's not that simple.

I was writing in the belief this unit existed.
I was wrong, but this is now remedied.

When you despair if people "didn't investigate very deeply", how far do you
want users to go? You need to understand that most people are not going to
go through a deep investigation before trying to resolve problems deciding
if your tool suits them or even works for their needs.

Before a _seasoned developer_ (I would say you qualify?) starts writing his own tool,
I do expect him to investigate, yes.

Let me explain:

- It should be clear what the database desktop is for (more or less).
It's clearly not a browser or image viewer.

- That being the case, if it still doesn't do something you want,
then that is a reason for at least investigating.

- If the code is not clear: ask on the mailing list, this IS open source.

I cannot imagine that this is more work than reimplementing it from scratch.

To improve your tool I'd suggest:

If there is no stored connection, you should have a database connection
setup screen as the first thing users see when launching your app.

Well, I have added a popup saying what to do. I don't believe starting the
dialog as once as you suggest is the right thing to do, so I offer people
the choice. Clicking one of the 2 'Ok' buttons take them to the appropriate
dialog, as you suggest.

If there are any hidden right click menus, you should supplement them with
items in a menu menu or a toolbar with appropriate icons and tooltips.

As I said in a previous mail: The item in the menu was there, since day one.
This, and this alone, prompted my remark about depth of investigation.

That it is not there in the toolbar was indeed a lack which has now been
remedied.

Provide in the sources code at least a comment about how to add additional
database providers.

Done.

Also calling database providers (what the rest of the world calls them)
"dictionaries" (RegisterDictionaryEngine) isn't helping the situation any.

The name is there simply because of the architecture of the tool:
the intent was (and still is, though secondary) to promote the use of the dictionary in DB apps.
They make life easier.
You cannot add a connection type without a corresponding data dictionary engine.

I have made the connections more prominent by putting them first.

I hope that recent commits convince you that we do want people to work with
these tools, and that we're open for suggestions.

I repeat: comments, suggestions, patches are welcome.

Certainly where UI design is concerned, which is - admittedly - not my forte.

Michael.

--
___
Lazarus mailing list
Lazarus@lists.lazarus.freepascal.org
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus

Re: [Lazarus] Cross Platform SQL Client/Editor

2016-01-02 Thread Sven Barth

Am 02.01.2016 15:55 schrieb "Anthony Walter" :
>
> Sven,
>
> >> I don't see MSSQL support. I don't want to setup ODBC DSNs,
>
> > It *does* support MSSQL. It supports all databases that FPC supports.
>
> On my system LazDataDesktop does not show MSSQL as an option:
>
> http://cache.getlazarus.org/images/where-is-mssql.png

Hmm... Then the unit was not added. Simply file a bug report then as FPC
supports it definitely and thus there's no reason why the LazDataDesktop
shouldn't...

Regards,
Sven
--
___
Lazarus mailing list
Lazarus@lists.lazarus.freepascal.org
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus

Re: [Lazarus] Cross Platform SQL Client/Editor




On Sat, 2 Jan 2016, Anthony Walter wrote:


Michael,

Thanks for the changes. I am checking them out and will create a new build
to test the changes. If it works I may contribute a few changes to improve
usability such as a connection wizard or some other dialog to guide first
time users in creating/configuring a database connection. I know from my
experience that I had to tweak the mssql connection to allow for statements
such as "create database". I will test this. Also there should be a
database selector, which when dealing with mssql allows the same connection
to address a different database on the same server instance.


You can register a connection string dialog per category/type of database,
the current dialog is a generic one which is simply registered for any kind
of database.

But it is possible to register a specialized one for SQL server.


Again I will check for this.


Please do.

Note that I haven't got MS-SQL server myself, so I am unable to perfom any 
testing myself for this kind of server.


But Laco (Lacak in Mantis) is active in DB development and has MS SQL server
experience, I have asked him already to test (and possibly improve) the ms sql 
datadict component.


But any other area: feel free to mail and suggest/ask for improvements.

Michael.

--
___
Lazarus mailing list
Lazarus@lists.lazarus.freepascal.org
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus

Re: [Lazarus] Cross Platform SQL Client/Editor





On Sat, 2 Jan 2016, Sven Barth wrote:


Am 02.01.2016 15:55 schrieb "Anthony Walter" :


Sven,


I don't see MSSQL support. I don't want to setup ODBC DSNs,



It *does* support MSSQL. It supports all databases that FPC supports.


On my system LazDataDesktop does not show MSSQL as an option:

http://cache.getlazarus.org/images/where-is-mssql.png


Hmm... Then the unit was not added. Simply file a bug report then as FPC
supports it definitely and thus there's no reason why the LazDataDesktop
shouldn't...


The (historical) reason is explained in my mail.

I am working on it right now, together with the 'test connection' button in
the new connection dialog.

Michael.

--
___
Lazarus mailing list
Lazarus@lists.lazarus.freepascal.org
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus

Re: [Lazarus] Cross Platform SQL Client/Editor

Sven,

>> I don't see MSSQL support. I don't want to setup ODBC DSNs,

> It *does* support MSSQL. It supports all databases that FPC supports.

On my system LazDataDesktop does not show MSSQL as an option:

http://cache.getlazarus.org/images/where-is-mssql.png
--
___
Lazarus mailing list
Lazarus@lists.lazarus.freepascal.org
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus

Re: [Lazarus] Cross Platform SQL Client/Editor

Michael,

Just FYI currently there is no "fpddmssql.pp" (the dd is for data
dictionary I suppose) so no, it's not as easy as just adding a unit name to
the project source. New units must be created and a register function must
be create for mssql to appear in the connections list.

And as far as your surprise at my lack of research goes, you need to
rethink about discovery. The first thing your app does is show a blank list
view of data dictionaries rather than help users create a database
connection first. Next after switching to the connections tab you have to
know to right click in the blank list view to get a context menu which
gives you the option to create a connection. Forcing users to find your
hidden options by using right click context menus is terrible design. Next
we get to the point where these is no option for mssql. You say you simply
include a unit, but in reality no mssql connection register unit currently
exists, so it's not that simple.

When you despair if people "didn't investigate very deeply", how far do you
want users to go? You need to understand that most people are not going to
go through a deep investigation before trying to resolve problems deciding
if your tool suits them or even works for their needs.

To improve your tool I'd suggest:

If there is no stored connection, you should have a database connection
setup screen as the first thing users see when launching your app.
If there are any hidden right click menus, you should supplement them with
items in a menu menu or a toolbar with appropriate icons and tooltips.
Provide in the sources code at least a comment about how to add additional
database providers.
Also calling database providers (what the rest of the world calls them)
"dictionaries" (RegisterDictionaryEngine) isn't helping the situation any.
--
___
Lazarus mailing list
Lazarus@lists.lazarus.freepascal.org
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus

Re: [Lazarus] Cross Platform SQL Client/Editor

Michael,

I checked out the lasted code from
svn/fpc/trunk/packages/fcl-db/src/datadict and
svn/lazarus/trunk/tools/lazdatadesktop.

Everything builds fine and it looks/works a lot better. Although I can now
connect to a remote mssql server, I found some problems with the "Run
query".

Here is a short issue list of the problems I nottice off-hand:

1) It doesn't handle typos or incorrect SQL statement very well. The app
presents an uncaught exception error dialog and asks you for permission to
terminate lazdatadesktop.

2) It doesn't allow a selected block of text to be run, rather it tries to
execute everything in the query window (see also issue #7).

3) It doesn't allow for multiple datasets to be returned.

4) Running "select columns into dest_table from source_table" crashes
lazdatadesktop as described in issue #1

5) Running "create database new_database" crashes lazdatadesktop as
described in issue #1.

6) It seems to want to return a live editable dataset and you must have to
press a stop button before the run query button is enabled again. There
should be some option to allow for a query to be run either without being
live, or change run to allow it to close then open a new query.

7) I'm not sure how useful the next/previous buttons are. I think most
people tend to want to maintain a big script they keep on disk containing
"if exists () drop table/procedure/function" followed by a "create
table/procedure/function" statement. They don't want a bunch of small SQL
clips kept in memory which they use forward and back arrows to navigate.

8) There should be a database selector to allow the user to change
databases on connections which support that functionality.

To fix:

All error messages related to SQL execution issues (table already exists,
invalid statements, ect) should be routed to a message area below the
script window. In this way a user can examine the message while trying to
fix their SQL, rather than having to dismiss a window and trying to
remember the details of the error message while trying to fix their SQL. In
all cases, users should never be presented with error dialog asking for
permission to terminate the app because their SQL is invalid.

Create database and database switching need to work. If I have a SQL
client/editor which cannot run a  "create database" statement, then that
editor is broken IMO.

Every SQL client/editor where the connection supports multiple databases
(mssql, firebird, mysql, postgre) should give users the ability to switch
databases without the need to define new connections. Users should be able
to see the databases for a given connection and have an easy way to switch
the connection to a different database, possible integrated with the "use"
SQL command.

Multiple dataset return should be supported. My program (at the top of this
thread) handles it, along with proper error message handling. I'd say the
live/editable dataset thing should be an extra option and not the default,
but that's me. Maybe we need a poll on this but I'd be curious to know how
many people who are typing end executing SQL prefer to just get results,
error messages or a live datagrid?

Execution of selected text should be supported.
--
___
Lazarus mailing list
Lazarus@lists.lazarus.freepascal.org
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus

Re: [Lazarus] Cross Platform SQL Client/Editor

2016-01-01 Thread Sven Barth

Am 02.01.2016 01:13 schrieb "Anthony Walter" :
>
> Michael,
>
> I don't want a "visual" table builder and I'm unsure how many others do.
All I want is something to connect me to a database engine where I can then
execute bits of SQL (it executes what you highlight), which is what my
program does.

LazDataDesktop allows for that as well. I don't have it right in front of
me, but if I remember correctly there is a tab - after you're connected -
in which you can simply execute statements.

> Lazarus database desktop seems geared towards everything I don't want. I
don't see MSSQL support. I don't want to setup ODBC DSNs, I just want a
straight MSSQL connection. I don't see an simple create/test connection
screen. Right clicking in a blank list view to get a menu and then
selecting an option is just bad design. Straight off I don't see a SQL
editor either.

It *does* support MSSQL. It supports all databases that FPC supports.

Regards,
Sven
--
___
Lazarus mailing list
Lazarus@lists.lazarus.freepascal.org
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus

[Lazarus] Cross Platform SQL Client/Editor

A few months ago I was trying to find a workable Microsoft SQL Server
client/editor on Linux and couldn't find anything decent in the FOSS zone.
I ended up writing my own simple SQL client/editor:

http://cache.getlazarus.org/images/sqlclient.png

The basic functionality is/was:

Connect to a server running MSSQL from Linux.
Allow the user to list/change databases.
Allow the user to execute SQL, either select statements, or other
statements including create/drop database.
Show the query results in a result window at the bottom using tabular
formatted text.

Question 1:

Is there any project (which doesn't require a whole lot of extras like a
Java environment) that does what I've described in a cross platform manner
and is easy to use and is trustworthy?

Question 2:

If the pickings are slim in answer to Question 1, are they many people here
who want a simple cross platform SQL client/editor, possibly with both
MySQL and MSSQL support?
--
___
Lazarus mailing list
Lazarus@lists.lazarus.freepascal.org
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus

Re: [Lazarus] Cross Platform SQL Client/Editor

K.P.,

Being able to connect to MSSL from Linux is kinda of the point. Installing
Wine + setup or configuration of ODBC DSN configuration files and or
drivers just to run a SQL client+editor kinda files into the "require a
whole lot of extras" category.
--
___
Lazarus mailing list
Lazarus@lists.lazarus.freepascal.org
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus

Re: [Lazarus] Cross Platform SQL Client/Editor

2016-01-01 Thread Graeme Geldenhuys

On 2016-01-02 00:10, Anthony Walter wrote:
> Straight off I don't see a SQL
> editor either.

It of course has that - the Laz Data Desktop just doesn't have a very
intuitive interface. It also has some bugs or oddities. But saying that,
it also has some useful features like extendible code generation etc.

Regards,
  - Graeme -

-- 
fpGUI Toolkit - a cross-platform GUI toolkit using Free Pascal
http://fpgui.sourceforge.net/

My public PGP key:  http://tinyurl.com/graeme-pgp

--
___
Lazarus mailing list
Lazarus@lists.lazarus.freepascal.org
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus

Re: [Lazarus] Cross Platform SQL Client/Editor

Michael,

I don't want a "visual" table builder and I'm unsure how many others do.
All I want is something to connect me to a database engine where I can then
execute bits of SQL (it executes what you highlight), which is what my
program does.

Lazarus database desktop seems geared towards everything I don't want. I
don't see MSSQL support. I don't want to setup ODBC DSNs, I just want a
straight MSSQL connection. I don't see an simple create/test connection
screen. Right clicking in a blank list view to get a menu and then
selecting an option is just bad design. Straight off I don't see a SQL
editor either.

So I think you'll understand why I don't want to use Lazarus database
desktop, and no I don't want to improve it. I'd rather have a stand alone
program for anyone to use on any platform which can connect to MSSQL (or
MySQL) and let you edit and execute SQL. And finally I don't see the point
of using the Lazarus moniker for such a tool.
--
___
Lazarus mailing list
Lazarus@lists.lazarus.freepascal.org
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus

Re: [Lazarus] Cross Platform SQL Client/Editor

2016-01-01 Thread K. P.

I use HeidiSQL for such purposes - but it is not natively cross-platform :(
Other than that, it is very usable and very stable


> Date: Fri, 1 Jan 2016 21:04:00 -0200
> From: m...@delfire.net
> To: lazarus@lists.lazarus.freepascal.org
> Subject: Re: [Lazarus] Cross Platform SQL Client/Editor
> 
> On Fri, Jan 1, 2016 at 8:39 PM, Anthony Walter <sys...@gmail.com> wrote:
> > If the pickings are slim in answer to Question 1, are they many people here
> > who want a simple cross platform SQL client/editor, possibly with both MySQL
> > and MSSQL support?
> 
> I don't use Linux for development (yet) but I work with MSSQL every day.
> Would be great if we had such tool. Thanks.
> 
> Marcos Douglas
> 
> --
> ___
> Lazarus mailing list
> Lazarus@lists.lazarus.freepascal.org
> http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
  --
___
Lazarus mailing list
Lazarus@lists.lazarus.freepascal.org
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus

Re: [Lazarus] Cross Platform SQL Client/Editor

2016-01-01 Thread Graeme Geldenhuys

On 2016-01-02 00:11, Mark Morgan Lloyd wrote:
> I've yet to see a GUI frontend which is remotely usable when 
> attached to a table with a few billion rows.

Then I guess you have never seen the setting in most GUI frontends that
limit the return result to say 500 records max. All the GUI SQL Editors
I've used supports this. In fact, they normally use the feature that
already exists in most SQL database servers to accomplish this.

Regards,
  - Graeme -

-- 
fpGUI Toolkit - a cross-platform GUI toolkit using Free Pascal
http://fpgui.sourceforge.net/

My public PGP key:  http://tinyurl.com/graeme-pgp

--
___
Lazarus mailing list
Lazarus@lists.lazarus.freepascal.org
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus

Re: [Lazarus] Cross Platform SQL Client/Editor

2016-01-01 Thread Marcos Douglas

On Fri, Jan 1, 2016 at 8:39 PM, Anthony Walter  wrote:
> If the pickings are slim in answer to Question 1, are they many people here
> who want a simple cross platform SQL client/editor, possibly with both MySQL
> and MSSQL support?

I don't use Linux for development (yet) but I work with MSSQL every day.
Would be great if we had such tool. Thanks.

Marcos Douglas

--
___
Lazarus mailing list
Lazarus@lists.lazarus.freepascal.org
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus

Re: [Lazarus] Cross Platform SQL Client/Editor

2016-01-01 Thread Mark Morgan Lloyd


Marcos Douglas wrote:

On Fri, Jan 1, 2016 at 8:39 PM, Anthony Walter  wrote:

If the pickings are slim in answer to Question 1, are they many people here
who want a simple cross platform SQL client/editor, possibly with both MySQL
and MSSQL support?


I don't use Linux for development (yet) but I work with MSSQL every day.
Would be great if we had such tool. Thanks.


Things like this usually stall when the app for some reason needs to 
either inject backend-specific SQL, or (thinks it) needs to validate an 
SQL query or command before it can submit it. Without wanting to sound 
negative, I've yet to see a GUI frontend which is remotely usable when 
attached to a table with a few billion rows.


Having said that and stepping back a little from the problem, it appears 
that PostgreSQL uses YACC-style tools (i.e. Bison etc.) to parse SQL 
syntax before executing it. A good starting point might be to make sure 
that pyacc has the cojones to parse SQL, and then before attempting to 
support any particular backend writing a fairly complete set of parsing 
rules.


--
Mark Morgan Lloyd
markMLl .AT. telemetry.co .DOT. uk

[Opinions above are the author's, not those of his employers or colleagues]

--
___
Lazarus mailing list
Lazarus@lists.lazarus.freepascal.org
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus

Re: [Lazarus] Cross Platform SQL Client/Editor

2016-01-01 Thread Graeme Geldenhuys

On 2016-01-01 23:10, Michael Van Canneyt wrote:
> now there will be again 2 half tools...

Plus one more "whole tool" from tiOPF (tiSQLEditor). ;-) Been using it
for years and it connects to any SQL database tiOPF supports (personally
I've used SQL-Server, Firebird, Oracle, Postgress and [ashamed] MySQL).

It also supports execute selections only, run scripts, run upgrade
scripts (directory based), generates tiOPF code from SQL results,
rudimentary SQL code completion, clipboard features (copy sql as quoted
pascal string and vice-versa etc).

Regards,
  - Graeme -

-- 
fpGUI Toolkit - a cross-platform GUI toolkit using Free Pascal
http://fpgui.sourceforge.net/

My public PGP key:  http://tinyurl.com/graeme-pgp

--
___
Lazarus mailing list
Lazarus@lists.lazarus.freepascal.org
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus

Re: [Lazarus] Cross Platform SQL Client/Editor

2016-01-01 Thread Michael Van Canneyt



Very strange and somewhat disappointing to read.

why did you not use/improve the lazarus database desktop ?

that is what it is for?

Why not improve existing tools ? by fragmenting more, now there will be again 2 
half tools...

Michael.

On Fri, 1 Jan 2016, Anthony Walter wrote:


A few months ago I was trying to find a workable Microsoft SQL Server
client/editor on Linux and couldn't find anything decent in the FOSS zone.
I ended up writing my own simple SQL client/editor:

http://cache.getlazarus.org/images/sqlclient.png

The basic functionality is/was:

Connect to a server running MSSQL from Linux.
Allow the user to list/change databases.
Allow the user to execute SQL, either select statements, or other
statements including create/drop database.
Show the query results in a result window at the bottom using tabular
formatted text.

Question 1:

Is there any project (which doesn't require a whole lot of extras like a
Java environment) that does what I've described in a cross platform manner
and is easy to use and is trustworthy?

Question 2:

If the pickings are slim in answer to Question 1, are they many people here
who want a simple cross platform SQL client/editor, possibly with both
MySQL and MSSQL support?



--
___
Lazarus mailing list
Lazarus@lists.lazarus.freepascal.org
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus

Re: [Lazarus] Cross Platform SQL Client/Editor