The output of the PostgreSQL server.... lazarus / 16-Databases ---------------------------------------------------------------------- Elmug · 2012-06-19 20:17 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 · 2012-06-19 23:21 http://wiki.lazarus.freepascal.org/Lazarus_Database_Tutorial http://wiki.lazarus.freepascal.org/SQLdb_Tutorial1 ---------------------------------------------------------------------- Elmug · 2012-06-20 09:58 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 · 2012-06-20 10:21 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 · 2012-06-20 12:10 Quotethe 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 possibleAh... I get it now. For errors, it should be handled using exception handler. I have no idea for warnings though. ---------------------------------------------------------------------- Elmug · 2012-06-20 17:22 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 · 2012-06-20 18:54 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. QuoteI want to see if I can have the response also show in a Memo boxWe 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 · 2012-06-21 02:16 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 · 2012-06-21 05:12 QuoteI 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. QuoteIf 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 · 2012-06-21 05:52 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 · 2012-06-21 11:25 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 · 2012-06-21 11:52 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 · 2012-06-21 14:06 Quote from: BigChimp on June 21, 2012, 01:52:56 pmOk. 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 · 2012-06-21 14:42 Quote from: Elmug on June 21, 2012, 04:06:40 pmThe 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/1 e0f2eb47242/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 · 2012-06-22 04:41 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. ---------------------------------------------------------------------- BigChimp · 2012-06-22 05:28 Elmug, I'm sure you don't have unreasonable questions ... it's just that it's difficult for me to understand what you mean because you give out information that confuses me. Very glad about your last post, this clears things up. As I said, you could then try running the string to a query parser (or, as you said, just scan for DROP etc). ---------------------------------------------------------------------- snorkel · 2012-06-28 18:53 I believe PostgreSQL sends the query hints/warnings etc using the postgreSQL notification system and the default Lazarus components do not have the capability built in to get the info needed. I use the postgres components from Devart.com which work on Lazarus and fully support getting the proper error messages from the PG notification system. Libpq does have the ability to get this info, so it's possible for the default Lazarus components to implement this. Hope that helps out a bit. ---------------------------------------------------------------------- Elmug · 2012-06-28 20:34 Quote from: snorkel on June 28, 2012, 08:53:06 pmI believe PostgreSQL sends the query hints/warnings etc using the postgreSQL notification system and the default Lazarus components do not have the capability built in to get the info needed. I use the postgres components from Devart.com which work on Lazarus and fully support getting the proper error messages from the PG notification system. Libpq does have the ability to get this info, so it's possible for the default Lazarus components to implement this. Hope that helps out a bit. Hi and thank you very much for this valuable explanation. It helps a lot. I just saw that it is cross-platform, and has extra features. They even have an option to include the source-code! Thank you also for mentioning your finding it adequate. I am making it a point to explore what you mention. Perhaps eventually the capability might be added to the Lazarus components, too, as you suggest! ---------------------------------------------------------------------- BigChimp · 2012-06-29 09:41 Quote from: snorkel on June 28, 2012, 08:53:06 pmI believe PostgreSQL sends the query hints/warnings etc using the postgreSQL notification system and the default Lazarus components do not have the capability built in to get the info needed. I use the postgres components from Devart.com which work on Lazarus and fully support getting the proper error messages from the PG notification system. Libpq does have the ability to get this info, so it's possible for the default Lazarus components to implement this. Testing indicates SQLDB catches and processes PostgreSQL errors: If I run the attached test program and enter some invalid SQL like Quotethis is not sql;...  SQLDB properly picks up the error from PostgreSQL. The program tells me this: QuoteDatabase error: EDatabaseError/Execution of query failed (PostgreSQL: ERROR:  syntax error at or near "this" LINE 1: this is not sql;         ^ ) SQL statement was: this is not sql; I do agree that it would be handy to have an error code property as well (such as implemented in the SQLDB Firebird, MS SQL Server, Sybase and Oracle drivers)... ---------------------------------------------------------------------- ludob · 2012-06-29 13:24 QuoteI do agree that it would be handy to have an error code property as well (such as implemented in the SQLDB Firebird, MS SQL Server, Sybase and Oracle drivers)...A patch for extended error information can be found at http://bugs.freepascal.org/view.php?id=22336. The error info is returned in the exception message and as a convenient EPQDatabaseError exception class with the following definition: Code: [Select]  EPQDatabaseError = class(EDatabaseError)     public       SEVERITY:string;       SQLSTATE: string;       MESSAGE_PRIMARY:string;       MESSAGE_DETAIL:string;       MESSAGE_HINT:string;       STATEMENT_POSITION:string;   end;