Hi list,

I've created a small SQL*Plus/isql/osql/sqlcmd clone, pasql.

It lets you connect to a database and run select queries as well as queries that don't return a dataset.
It also shows how to use logging.

Perhaps it's a nice candidate for inclusion as an FPC example, but it's only been tested/known to work on Firebird 2.5 server, MS SQL Server 2008 and Sybase ASE15.

I'd be interested in suggestions for improving the code (e.g. the ODBC code probably needs work), bug reports with other databases (PostgreSQL, Oracle,...)

Thanks,
Reinier

From the readme:

Pasql
=====
Pasql is a sample tool that lets you connect to an SQLDB supported database and run SQL commands.

It logs your commands and can print them at the end of the session.

To run this, you need to have the relevant database client drivers/dlls installed (e.g. in Windows, you can put them in the directory where pasql.exe is)

Run pasql -h for help.

Concepts
========
This program demonstrates:
- handling command line parameters
- use of a general class (TSQLConnection) for one of several specialized classes (TIBConnection, TMSSQLConnection,...) and use of the as keyword to call specialized code.
- a trick to find out whether a TSQLQuery returns records or not

Improvement ideas
=================
Support for more databases

Rewrite program so it is a bit more object oriented and variables are limited/scoped (the program originated as a procedural program)

For embedded/file based databases: if hostname and database are both empty (or database does not exist), create a database if user agrees

Better format of returned records; scrolling support

Pasql command mode next to the existing SQL mode: e.g. by typing ' at the beginning of a line.
This could include setting up parametrized queries: 'PARAM SELECT NAME,SURNAME,SALARY FROM EMPLOYEES WHERE NAME=:FINDNAME; 'PARAM FINDNAME=Van Canneyt
'GO
==>Database returns e.g.: Michael Van Canneyt 58000 'PARAM
==>reads file name.txt into FINDNAME parameter
'GO
==>Database returns e.g.: Florian Klaempfl 60000
'
(single quote to exit command mode and forget parametrized query) Command mode could also have output formatting commands like 'SET HEADER OFF
'SET HEADER ON
and metadata commands like:
'SHOW TABLES
'SHOW TABLE X =>can show e.g. columns, indexes, constraints 'SHOW PROCEDURES
... look into e.g. SQLQuery.SetSchemaInfo for that

Some way to insert blobs - e.g. using command mode above with the @file notation

Intercept keypresses (e.g. Alt-1, Alt-2) and show help/enter command mode

Reinier Olislagers, April 2012

Interesting example. You forgot the readme in the zip ?

I'm willing to include it as an example in fcl-db, but then you'd need to make some improvements first:
- Choose a different default connection type on non-windows OSes. MS-SQL does not make sense. I'd go for firebird, but I suggest you make this selectable through a define.
- Clean up the code so it is separated out in a unit, not in the program file ?
- Why not use the TSQLConnector component ? You repeat what it does already ?

Any of the improvements you suggest are fine too, of course.

Michael.

On 25-5-2012 10:36, Michael Van Canneyt wrote:

Thanks for the quick and helpful response, Michael.

Interesting example. You forgot the readme in the zip ?

I added it to the bottom of the post instead ;)

Firebird sounds good, define... why not (though it seems like a bit of overkill)

- Clean up the code so it is separated out in a unit, not in the program file ?

Ok, though I don't see that much advantage to it unless you want to separate the connection/console functionality out into a class... sorry object that can be instantiated. Which does make sense.

- Why not use the TSQLConnector component ? You repeat what it does already ?

Wasn't aware of its existence... or forgot about it... ;) Looks like pasql could do with a rewrite then...

Thanks,
Reinier