Some testing the water questions about the PostgreSQL support in Lazarus

[mdlueck]

Greetings,

I find that PostgreSQL appears to be natively supported with Lazarus, here:

https://wiki.freepascal.org/postgres

That is absolutely fantastic!

Background first: I have learned a number of SQL RDBMS's so far. First was IBM DB2. Then added MySQL around 1999 when web database applications were taking off. In 2011, I added MS SQL Server by specific request of a client. Then I have added Oracle, for another client's transition from DB2 to Oracle.

My first favorite was DB2. I much prefer that for ETL operations. Its proprietary export / import format, for me, worked flawlessly. (Compared with SQL Server, the same operations one had to use CSV, and there was a fight with Microsoft tools that some tools put quotes around the values in CSV and other Microsoft tools do not. What a non-standard mess!)

My favorite SQL RDBMS for developing SQL Stored Procedures was SQL Server 2008 with its SSMS tool.

Questions about that support:

1) With Lazarus + PostgreSQL, is it solidly supported to develop prepared SQL? That is where placeholders are put into the SQL code to indicate where host variables will appear, then separately provide all the variables, then the SQL engine executes the request. This is a good way to harden again SQL Injection Attacks.

My favorite so far has been front-end coding in MS Access VBA with ADO.Command and ADO.Parameter objects.

Essentially what I am asking is does Lazarus + PostgreSQL support something similar to VBA ADO.Command and ADO.Parameter objects.


2) Does Lazarus + PostgreSQL support SQL Stored Procedures? That is, publishing to PostgreSQL the SQL, then having the Lazarus simply call the Stored Procedure by name, supplying the needed Parameters.

I never got my mind around how DB2 supported Stored Procedures.... seemed like (back on DB2 version 2) that the Stored Procedures needed to be written in a compiled language such as C, and then that C code would interact with the DB2 database engine. I greatly enjoyed developing SQL Stored Procedures in SQL Server 2008 vis SSMS. THAT made perfect sense to me. SQL is to talk to the back-end, front-end is what ever tool is being used (Lazarus), but then to require a THIRD language since you dare to say "Stored Procedure"? That just seemed non-intuitive back on DB2. I do not want to need a third language.

3) About the warning on the above mentioned page about PostgreSQL in Lazarus:

"Note: The libpq C client contains some memory leaks (at least up till version 9.3 of Postgres) when a library is repeatedly loaded/unloaded. SQLDB loads the library when the first connection is made, and unloads it when the last connection closes."

Does that mean that if a program loads the PostgreSQL driver as mandatory at start of the Lazarus program and leaves it loaded the entire time it is running, only cleans up the connection at the very end then problems would be avoided?

My use of MS SQL Server, now that I think of it, was in fact to build a brand new ADO.Connection object for each individual SQL call the front-end needed to make to the back-end. That design was robust through the front-end being put into sleep mode, woken back up, and the expectation being that continued use should work flawlessly. At first, I thought to obtain the ADO.Connection object and keep it alive within a VBA class. That worked perfectly while on Windows XP and Office 2007. But when client machines were upgraded to Windows 7 and Office 2010, THEN suddenly that combination seemed to power save / sleep even deeper, and the ADO.Connection object would not survive. So then I had to update my VBA class to create and destroy the ADO.Connection object each time a request was made to receive the ADO.Connection object, not return the same ADO.Connection object that has existed since the application came up. So sounds like I will encounter this libpq C client memory leaks issue, correct?

I am thankful,

[Handoko]

2) Does Lazarus + PostgreSQL support SQL Stored Procedures?

Perhaps this can answer your question:
https://forum.lazarus.freepascal.org/index.php/topic,17307.msg95166.html#msg95166

Lazarus has many libraries to support wide range of databases, maybe you're interested to know:
https://wiki.lazarus.freepascal.org/Database_libraries

[Thaddy]

Yes FPC supports stored procedures for PostGreSQL.. It basically supports everything, give or take a few exotic options that you won't find in the wild very often (and require recompiling PostGreSQL yourself, which you are not very likely to go and do).

[mdlueck]

Yes FPC supports stored procedures for PostGreSQL.. It basically supports everything, give or take a few exotic options that you won't find in the wild very often (and require recompiling PostGreSQL yourself, which you are not very likely to go and do).

Good to hear that Lazarus calling PostgreSQL Stored Procedures will not be a problem, Thaddy.

Any feedback on my other sub topic questions?

I am thankful,

[mdlueck]

2) Does Lazarus + PostgreSQL support SQL Stored Procedures?

Perhaps this can answer your question:
https://forum.lazarus.freepascal.org/index.php/topic,17307.msg95166.html#msg95166

Noted. Thank you for that direct link, Handoko.

I am thankful,

[Thaddy]

What is not clear enough is that you can also write the stored procedures in Object Pascal, just as for the other mainstream Db back-ends.
There are currently no concerns about any memory leaks that I know of.

If you plan to do so, use C style types for that (PChar/PWideChar) and NOT object pascal managed types like pascal strings etc.

[mdlueck]

What is not clear enough is that you can also write the stored procedures in Object Pascal, just as for the other mainstream Db back-ends.

What the?!?!?! Now that is frying my brain circuits. 

I am hoping that PostgreSQL supports SQL Stored Procedures much like SQL Server does. Define those on the DB, simply call the published Stored Procedure from the front-end application. That my brain excels with. 

There are currently no concerns about any memory leaks that I know of.

Excellent!

If you plan to do so, use C style types for that (PChar/PWideChar) and NOT object pascal managed types like pascal strings etc.

I am a developer who excels in Interpretive Script programming languages. I like storing numbers and strings in SQL RDBMS's, with the CLOB / BLOB and XML objects thrown in for good measure.

I am thinking the "P" prefix means Pointer... nope, not my kind of data to be storing in a SQL RDBMS.

I am thankful,

[marcov]

I am hoping that PostgreSQL supports SQL Stored Procedures much like SQL Server does. Define those on the DB, simply call the published Stored Procedure from the front-end application. That my brain excels with. 

Afaik yes, but the syntax is more Oracle like.

[mdlueck]

I am hoping that PostgreSQL supports SQL Stored Procedures much like SQL Server does. Define those on the DB, simply call the published Stored Procedure from the front-end application. That my brain excels with. 

Afaik yes, but the syntax is more Oracle like.

Thank you, marcov. Noted the warning.... MS SQL Server does have its unique T-SQL. And Oracle has its PL-SQL.

I am thankful,

[mdlueck]

My favorite so far has been front-end coding in MS Access VBA with ADO.Command and ADO.Parameter objects.

Essentially what I am asking is does Lazarus + PostgreSQL support something similar to VBA ADO.Command and ADO.Parameter objects.

Picture hopefully worth 1000 words... a source code example of SQL UPDATE method in VBA using ADO.Command and ADO.Property type objects to safely encapsulate the data and integrate it into the SQL to be executed:

Code: Text  [Select][+][-]

1. Public Function Update() As Boolean
2. On Error GoTo Err_Update
3.  
4.   Dim adoCMD As Object
5.   Dim adoRS As Object
6.   Dim strSQL As String
7.   Dim lRecordsAffected As Long
8.  
9.   'Define a query to Update a new record into the FE temp table
10.   strSQL = "UPDATE [" & Me.FETempTableName & "]" & vbCrLf & _
11.            "SET [partnumber] = p1," & vbCrLf & _
12.            "[title] = p2," & vbCrLf & _
13.            "[qtyper] = p3," & vbCrLf & _
14.            "[oldqtyper] = p4," & vbCrLf & _
15.            "[addpartrecordflg] = p5," & vbCrLf & _
16.            "[doneflg] = p6" & vbCrLf & _
17.            "WHERE [aid] = p7;"
18.  
19.   'Define attachment to database table specifics
20.   Set adoCMD = CreateObject("ADODB.Command")
21.   With adoCMD
22.     .ActiveConnection = CurrentProject.Connection
23.     .CommandType = adCmdText
24.     .Parameters.Append .CreateParameter("p1", adVarChar, adParamInput, 25, Me.partnumber)
25.     .Parameters.Append .CreateParameter("p2", adVarChar, adParamInput, 50, Me.title)
26.     .Parameters.Append .CreateParameter("p3", adSmallInt, adParamInput, 2, Me.qtyper)
27.     .Parameters.Append .CreateParameter("p4", adSmallInt, adParamInput, 2, Me.oldqtyper)
28.     .Parameters.Append .CreateParameter("p5", adBoolean, adParamInput, 2, Me.addpartrecordflg)
29.     .Parameters.Append .CreateParameter("p6", adBoolean, adParamInput, 2, Me.doneflg)
30.     .Parameters.Append .CreateParameter("p7", adInteger, adParamInput, 4, Me.aid)
31.     .CommandText = strSQL
32.     Set adoRS = .Execute(lRecordsAffected)
33.   End With
34.  
35.   If lRecordsAffected = 0 Then
36.     Update = False
37.   Else
38.     'Return a good return code
39.     Update = True
40.   End If
41.  
42. Exit_Update:
43.   'Clean up the connection to the database
44.   Set adoCMD = Nothing
45.   Set adoRS = Nothing
46.  
47.   Exit Function
48.  
49. Err_Update:
50.   Call errorhandler_MsgBox("Class: " & TypeName(Me) & ", Function: Update()")
51.   Update = False
52.   Resume Exit_Update
53.  
54. End Function

Is there such similar objects to leverage within Lazarus / Free Pascal?

I am thankful,

[Handoko]

Is there such similar objects to leverage within Lazarus / Free Pascal?

It doesn't differ much doing what you said above in Lazarus. For database programming in Lazarus basically you need 3 objects: a connection, a transaction and a query. If you want to 'link' the database to a visual component, you then also need a datasource.

Below is a video tutorial showing how to connect and update a SQLite database:
https://www.youtube.com/watch?v=mPvTyTbX2Ko

I have no experience using PostgreSQL, but because fcl-db library, which is used in the video tutorial also supports PostgreSQL, with a few changes it can set to connect a PostgreSQL database.

I ever done in the past, writing a module that could be used to connect and update both SQLite and Firebird databases. In the code, I just needed to supply different connection strings for different databases, and almost all the query strings could be used for both databases. The different query strings were only for certain features of the database that do not follow the common SQL format. For example in Firebird, it uses SKIP and FIRST instead of OFFSET and LIMIT. Here is my code for solving the issue:

Code: Pascal  [Select][+][-]

1. { TFirebird }
2.  
3. function TFirebird.SelectQryWithPage(const TblName: string; Page,
4.   ItemsPerPage: Integer): string;
5. var
6.   Skip: string;
7. begin
8.   Skip := '';
9.   if Page > 1 then Skip := ' SKIP ' + ((Page-1)*ItemsPerPage).ToString;
10.   if Page > 0 then Skip := 'FIRST ' + ItemsPerPage.ToString + Skip + ' ';
11.   Result := 'SELECT ' + Skip + TblName + ';';
12. end;
13.  
14. { TSQLite }
15.  
16. function TSQLite.SelectQryWithPage(const TblName: string; Page, ItemsPerPage: Integer
17.   ): string;
18. var
19.   Limit: string;
20. begin
21.   Limit := '';
22.   if Page > 0 then
23.     Limit := ' LIMIT ' + ItemsPerPage.ToString +
24.              ' OFFSET ' + ((Page-1)*ItemsPerPage).ToString;
25.   Result := 'SELECT ' + TblName + Limit + ';';
26. end;

If you want some code for testing, here has some SQLite demos you can download:
https://forum.lazarus.freepascal.org/index.php/topic,68015.msg524797.html#msg524797

I personally prefer doing all the database things using code, as shown in the demos above. For beginners, it is easier using database components, which can be dragged-and-dropped into a form.

I saw you passing values in the database query as parameters, and here is the documentation for it:
https://www.freepascal.org/docs-html/fcl/sqldb/usingparams.html

[paweld]

Is there such similar objects to leverage within Lazarus / Free Pascal?

something like this:

Code: Pascal  [Select][+][-]

1. function TForm1.Update(FETempTableName, partnumber, title: String; qtyper, oldqtyper: Smallint; addpartrecordflg, doneflg: Boolean; aid: Integer): Boolean;
2. var
3.   sqlCMD: TSQLQuery;
4.   strSQL: String;
5.   i, lRecordsAffected: Integer;
6. begin
7.   Result := False;
8.   //Define a query to Update a new record into the FE temp table
9.   strSQL := 'UPDATE [' + FETempTableName + ']' + lineEnding +
10.             'SET [partnumber] = :p1,' + lineEnding +
11.             '[title] = :p2,' + lineEnding +
12.             '[qtyper] = :p3,' + lineEnding +
13.             '[oldqtyper] = :p4,' + lineEnding +
14.             '[addpartrecordflg] = :p5,' + lineEnding +
15.             '[doneflg] = :p6' + lineEnding +
16.             'WHERE [aid] = :p7;';
17.  
18.   //Define attachment to database table specifics
19.   sqlCMD := TSQLQuery.Create(nil);
20.   with sqlCMD do
21.   begin
22.     SQLConnection := PQConnection1;
23.     Transaction := SQLTransaction1;
24.     SQL.Text := strSQL;
25.     for i := 0 to Params.Count - 1 do
26.       Params[i].ParamType := ptInput;
27.     ParamByName('p1').AsString := partnumber;
28.     ParamByName('p2').AsString := title;
29.     ParamByName('p3').AsSmallInt := qtyper;
30.     ParamByName('p4').AsSmallInt := oldqtyper;
31.     ParamByName('p5').AsBoolean := addpartrecordflg;
32.     ParamByName('p6').AsBoolean := doneflg;
33.     ParamByName('p7').AsInteger := aid;
34.     try
35.       ExecSQL;
36.       lRecordsAffected := RowsAffected;
37.       SQLTransaction1.Commit;
38.       Result := True;
39.     except
40.       SQLTransaction1.Rollback;
41.       ShowMessage('Update error for table: ' + FETempTableName);
42.     end;
43.   end;
44.   sqlCMD.Free;
45. end;

[mdlueck]

Is there such similar objects to leverage within Lazarus / Free Pascal?

something like this:

Wow paweld! That post is golden! So there IS a way to do Prepared SQL with Lazarus!

Bookmarking that reply of yours for sure. That is a very helpful and kind translation you performed... I can clearly see how my VBA standard I developed over time would be replicated in Object Pascal. This is the sort of code I seek to build, so it gives me confidence that Lazarus is a viable tool to work with.

I am very thankful,