The output of the PostgreSQL server....

[Elmug]

Hi everyone,

In Windows 7.

I wrote a little application that can can connect to any Postgres database, and from there create more databases, tables, updates, inserts and so on. It detects when the query is not accepted for the usual reasons.

I run it, and issue the arbitrary but valid commands, and I can check the effect of the commands by having also an instance of the terminal-based psql.exe, and verify with it that the commads took place.

HOWEVER, I can not yet figure how to catch the OUTPUT of the Posgres engine to display them in the said Lazarus application, meaning the REPLIES to a query that one sees when running the query from pgsql.exe shell-type application.

So, I was hoping that someone could and would advice me on the fundamentals of how that could be done. I use the Lazarus TPQConnection, and related VCL objects in the said application. The queries I type in a Memo box. The output of the PostgreSQL engine I would like to show in a second Memo box.

Thanks a lot!

[Leledumbo]

http://wiki.lazarus.freepascal.org/Lazarus_Database_Tutorial
http://wiki.lazarus.freepascal.org/SQLdb_Tutorial1

[Elmug]

Hi Leledumbo,

I studied the links you gave (again for I had seen them before) and I still can not figure out if it is possible to somehow catch what the database server sends back (as text or lines) everytime it gets a query regardless of what its response is, even if it is error or warnings (the text output we see if we were using a pgsql shell, that's what I was asking if we can get it and put it into a memo box, if possible).

Thanks.

[BigChimp]

Elmug,

You'd have to show some code I think.
Query results are different from errors. You deal with errors by catching database exceptions. It's up to your exception handler to display stuff the same way as pgsql would, if you want to.

[Leledumbo]

the text output we see if we were using a pgsql shell, that's what I was asking if we can get it and put it into a memo box, if possible

Ah... I get it now. For errors, it should be handled using exception handler. I have no idea for warnings though.

[Elmug]

Good people,

I have no code to show, but would gladly go for it if I knew only what the output channel of the PostgreSQL server is when the connection is via a TPPQ connection.

If I type "asdf" at the psql shell, PostgreSQL replies

ERROR:  syntax error at or near "asdfdf"
LINE 1: asdfdf;

Of course, when a select statement is the input, the output is text data.

Whatever it be, I would like to show the Postgres reply in a Memo box.

I am trying to ad a "shell" to my application. I already have the INPUT part where the valid queries are applied, and I can check it is so by having simultaneously open a pgsql shell. I want to see if I can have the response also show in a Memo box, as said, except the input is in one Memo box, and the output of Postres would be on a second Memo box.

Thanks for any suggestions!
       

[BigChimp]

Either redirect input/output to/from the pgsql or write code that runs queries.

If you are still having problems, it's perhaps best to go through some examples on the wiki to help you understand how database components work.

I want to see if I can have the response also show in a Memo box

We already told you: use TSQLQuery. You can adapt this http://wiki.lazarus.freepascal.org/SqlDBHowto#How_to_read_data_from_a_table.3F to output the data to a memo. Perhaps there are more suitable examples on the wiki... only had a quick look, sorry.

[Elmug]

Hi, and thanks BigChimp.

I don't think my question has to do how database components work, put perhaps more on how PostgreSQL engine works, specifically as to how it handles its output.

I found this info on the PostgreSQL documentation, and if based on that you could guide me as to how to achieve what I am after, I would be  grateful. It has to do with environment settings of PostgreSQL:


\pset parameter [ value ]

    This command sets options affecting the output of query result tables. parameter describes which option is to be set. The semantics of value depend thereon.
   
 pager

 Controls use of a pager for query and psql help output. If the environment variable PAGER is set, the output is piped to the specified program. Otherwise a platform-dependent default (such as more) is used.

    When the pager is off, the pager is not used. When the pager is on, the pager is used only when appropriate, i.e. the output is to a terminal and will not fit on the screen. (psql does not do a perfect job of estimating when to use the pager.) \pset pager turns the pager on and off. Pager can also be set to always, which causes the pager to be always used.


So, I am looking into this, since I have not found any example that deals with my issue. If you happen to find one, would also appreciate if you advise me on it.

Thanks again!

[ludob]

I don't think my question has to do how database components work, put perhaps more on how PostgreSQL engine works, specifically as to how it handles its output.

The output from PostgreSQL is a binary stream. It is up to the database client to transform it into readable information. Psql is such a database client. \pset pager is a metacommand for psql and has therefor nothing to do with the PostgreSQL engine.
The PostgreSQL database components in lazarus/fpc are also a database client but with the main mission to transform the binary stream into datasets. It is up to the programmer to render these datasets to a screen if he whishes so. DBControls are there to help you with visualising datasets.

If I type "asdf" at the psql shell, PostgreSQL replies

ERROR:  syntax error at or near "asdfdf"
LINE 1: asdfdf;

Have you already tried to execute this "query" with one of the database components? If you had, you would have noticed the exception message pop-up with that message. So to "lead" that message to a  tmemo, you only have to catch that exception to get rid of the pop-up and add the associated exception message string to the memo. 

[BigChimp]

After Ludo's explanation, I hope you understand now why we keep on about dealing with the output of queries/exceptions in SQLDB and use that output to emulate whatever you want.

Looking at another post http://lazarus.freepascal.org/index.php/topic,17309.0/topicseen.html it seems as if you manage to get database output in a grid.

It seems as if you think that you need to emulate the PostgreSQL client setings and think the FPC database functionality should provide this. As we explained, these are different mechanisms.

• If you still think our suggestions (use query output and output error messages based on exceptions) are not the best way to do this, can you tell us what direction/solution you're thinking of?
• Also, could you explain what the purpose of your program on a higher level is? The thing is, you might be trying to do something in a way that might be possible in Lazarus but there might be a much easier/nicer way to achieve the program's goals.
  

I'd really appreciate if you could answer these 2 questions... which will hopefully stop all us from repeating the same messages over and over again in the hope the other party understands
Also, I'm intrigued by your requirements, and I'd like you to have a good program.

Thanks,
BigChimp

[Elmug]

Hi again, and thanks for replying.

Ludo,
I will solve the issue by directing the output of PostgreSQL engine into a file.
Then I will just read that file into the memo box. I've known this is possible since the beggining, but I was trying to see if it is possible to avoid writing the fie. If anyone knows how to, I'd  appreciate that.

Thanks for all you say.

BigChimp,
What I want to do, I have already explained:
I already have a memo box to input a query.
All I need is to put whatever response from the PostgreSQL engine into a second memo box. The practical use of that is to allow SQL input from a user, but RESTRICT the input by checking it, rejecting certainin commands and syntax.

Thanks for the help, advice, and concerns.

[BigChimp]

Ok.
So I suppose your answer to my 1st question is redirect pgsql input/output. Note: have a look at the executing external processes wiki page: you could redirect the output directly to your program instead of dumping to file.

However: you say you put the response from the server in a second memo box. Ok. That will be used to restrict SQL input from a user based on server response. But you already sent the SQL commands to the server???

Never mind, I'm giving up - seems we just don't understand each other... no problem.

[Elmug]

Ok.
So I suppose your answer to my 1st question is redirect pgsql input/output. Note: have a look at the executing external processes wiki page: you could redirect the output directly to your program instead of dumping to file.

However: you say you put the response from the server in a second memo box. Ok. That will be used to restrict SQL input from a user based on server response. But you already sent the SQL commands to the server???

Never mind, I'm giving up - seems we just don't understand each other... no problem.

BigChimp,

In one memo box, the query is to be placed. Then rejected if not allowed; sent if allowed.

In the second memo box of the application, I will show the response of PostgreSQL, whatever it be: data, error message, no matter.

The main thing is to examine the query, by program control, BEFORE sending it.

[BigChimp]

The main thing is to examine the query, by program control, BEFORE sending it.

Ok, and how do you intend to do that? By sending some command through pgsql?

Note: you could also run the command the user entered through an SQL parser - I think FPC has some which will catch some errors at least.

If your program's higher level goal is to let users run queries on postgresql and e.g. save a history of all succesful queries, you can just execute the queries without checking and see if you get data back, or if you get exceptions.
If you get exceptions (or no data, or both, depending on your program's goal), remove the SQL command from the history list or whatever list you are keeping, and/or remove the command from the relevant memo.
If you get data back, do whatever you want with it: display it in a grid, ignore it, put it in a memo by looping through the result set and adding each record to the memo... etc.

See e.g. https://bitbucket.org/reiniero/fpc_laz_patch_playground/src/1e0f2eb47242/SQL/pasql.lpr line 897 (function RunSQL) for a command line example that runs user-specified SQL, deals with errors and detects if you get results back or not (e.g. some Firebird stored procedures only execute without returning data; I understand PostgreSQL SPs always need to be called with SELECT so they return data... even if that is an empty set)

As I said, it helps if you tell us a bit what your program needs to do on a higher level. Thanks for already telling a bit but we could go further if you want. E.g. why do you need to filter out invalid SQL commands - the server will report a proper error message anyway which the database components will detect anyway? Do you want to protect your users from e.g. deleting entire tables or something? Will they be frightened by threatening error messages?
What does this program do? Does it work as some kind of query editor? Is it meant for data analysis? Data entry?

[Elmug]

No, BigChimp.

I will check the query input by the user first, by means of string functions.

In the systems I developed for corporations 30 years ago, still in DOS, that was exactly one of the features of the development.

That allowed some extra power to advanced users, not for data-entry clerks, while not raising database damage possibilities.

For instance, if the query contains words like "drop" or "create", the query is just rejected.

There's a little more than that, but that I hope this briefly explains the intent.


Thanks for helping on this. I am sure I'll have some more questions, and be assured they are not meant to be unreasonable.