Simplest example to perform INSERT into Postgres table?

[timg11]

A few months ago I was able to install and test Zeoslib in Delphi 7, and was able to connect to the database.

Now I'm ready to integrate it into the Delphi program.
All I need to do is perform an INSERT.

Even with the ZSimple example, there seems to be a lot of functionality I don't need.

As I study the "ZeosDocumentationCollection-2017-03-20.pdf" there is no component for INSERT.

Searching on INSERT, I find:

If you want to execute an SQL statement which has no result set (e. g.:
INSERT or UPDATE) you have to use ExecSQL (see also: TZStoredProc).

But ExecSQL is not documented, and TZStoredProc doesn't seem like the right functionality.

What is the minimum set of components needed to perform an INSERT into Postgres?

Is there any example I can use to guide?

[Fr0sT]

Use TZQuery, Luke

[timg11]

Thanks! I have no experience with Delphi database or components.
I see an name like TZQuery, and I think the word Query means "asking a database to give me some data". I'll keep looking for something to put data into the database....

From reading the ZeosDocumentationCollection-2017-03-20.pdf, I find "Multiple statements in TZQuery and TZUpdateSQL"

Code: Select all

With Query do Begin
Sql.Clear;
Sql.Add('DELETE FROM table1;');
Sql.Add('INSERT INTO table1 VALUES (:Val1, :Val2);');
Sql.Add('INSERT INTO table2 VALUES (:Val3, :Val2);');
Sql.Add('UPDATE table3 SET field1 = :Val4;');
Params.ParamByName('Val1').AsInteger := 123;
:
ExecSql;
End;
:

So I understand the Query can do more than a query - it can provide some SQL statement in the SQL property. Those statements can include INSERT.

I'm now trying to even further simplify the ZSimple.dpr example. What can I get rid of if all I want to do is run the statements above?

What is ZSequence? No mention in the ZeosDocumentationCollection.

As I try to strip down ZSimpleMain.pas I get "
Access violation at address 015A21E6 in module dcc70.dll, Read of address 00000003." And then after that "Fatal Error Internal Error LA30".

[aehimself]

This will not work. You'll have to put one statement in a block and execute it...

SQLQuery.SQL.Text := 'DELETE FROM Table';
SQLQuery.ExecSQL;

SQLQuery.SQL.Text := 'INSERT INTO ...';
SQLQuery.ExecSQL;

Or you can use a ZSQLProcessor which is automatically breaking your script up based on a divider:

SQLProcessor.Script.Text := 'DELETE FROM Table; INSERT INTO ...';
SQLProcessor.Execute;

With SQLProcessor you can use the events to give parameter values / give feedback on rows affected after each statement / react to errors.

[Fr0sT]

In DB terms, query means every communication with a server, even if no noticeable data is returned from server.

ZSequence is a component for handling auto-generated ID fields, you're unlikely need it for now

[timg11]

I wanted to share how I solved my problem of getting data out of my old D7 application into Postgres.

I realized that there is a steep learning curve with Zeos, built around the whole Delphi database approach that I've never used.
I could eventually probably figure it out, but I found another way.

In my program I already had the data available in a string that was close to CSV.
Every 10 minutes or so when there is data to export, I write it as a file "temp.sql" like this:

Code: Select all

INSERT INTO sometable VALUES ('2020-06-09 15:50:00+0',77.8,90.8,69.2,74.1,70.4,3.0,72.5);

Then I use Delphi ShellExecute to call a .CMD file that runs "PSQL.EXE -f temp.sql"

This has been working perfectly for a month or so. Thank you all for your help. I hope next time I work with Zeoslib, I will be able to work with a more recent version of Delphi, like D2010.

[aehimself]

I see this as a huge overkill, plus now your application totally depends on PLSQL - which external dependencies I personally don't like.
How critical this information is? ShellExecute returns no exit code, so your data can be lost if something weird happens.

[CedsCocaa]

Have you considered using the TZQuery component in Delphi for your Postgres INSERT operation, and have you encountered any challenges while implementing it?