Some testing the water questions about the PostgreSQL support in Lazarus lazarus / 16-Databases ---------------------------------------------------------------------- mdlueck · 2025-11-15 14:19 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 · 2025-11-15 16:44 Quote from: mdlueck on November 15, 2025, 03:19:39 pm2) Does Lazarus + PostgreSQL support SQL Stored Procedures? Perhaps this can answer your question: https://forum.lazarus.freepascal.org/index.php/topic,17307.msg95166.ht ml#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 · 2025-11-15 16:46 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 · 2025-11-15 16:55 Quote from: Thaddy on November 15, 2025, 05:46:37 pmYes 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 · 2025-11-15 16:56 Quote from: Handoko on November 15, 2025, 05:44:29 pmQuote from: mdlueck on November 15, 2025, 03:19:39 pm2) Does Lazarus + PostgreSQL support SQL Stored Procedures? Perhaps this can answer your question: https://forum.lazarus.freepascal.org/index.php/topic,17307.msg95166.ht ml#msg95166 Noted. Thank you for that direct link, Handoko. I am thankful, ---------------------------------------------------------------------- Thaddy · 2025-11-15 17:34 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 · 2025-11-15 21:30 Quote from: Thaddy on November 15, 2025, 06:34:36 pmWhat 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.  Quote from: Thaddy on November 15, 2025, 06:34:36 pmThere are currently no concerns about any memory leaks that I know of. Excellent! Quote from: Thaddy on November 15, 2025, 06:34:36 pmIf 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 · 2025-11-15 21:48 Quote from: mdlueck on November 15, 2025, 10:30:29 pmI 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 · 2025-11-15 23:25 Quote from: marcov on November 15, 2025, 10:48:15 pmQuote from: mdlueck on November 15, 2025, 10:30:29 pmI 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 · 2025-11-15 23:31 Quote from: mdlueck on November 15, 2025, 03:19:39 pmMy 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i Public Function Update() As BooleanOn Error GoTo Err_Update   Dim adoCMD As Object  Dim adoRS As Object  Dim strSQL As String  Dim lRecordsAffected As Long   'Define a query to Update a new record into the FE temp table  strSQL = "UPDATE [" & Me.FETempTableName & "]" & vbCrLf & _           "SET [partnumber] = p1," & vbCrLf & _           "[title] = p2," & vbCrLf & _           "[qtyper] = p3," & vbCrLf & _           "[oldqtyper] = p4," & vbCrLf & _           "[addpartrecordflg] = p5," & vbCrLf & _           "[doneflg] = p6" & vbCrLf & _           "WHERE [aid] = p7;"   'Define attachment to database table specifics  Set adoCMD = CreateObject("ADODB.Command")  With adoCMD    .ActiveConnection = CurrentProject.Connection    .CommandType = adCmdText    .Parameters.Append .CreateParameter("p1", adVarChar, adParamInput, 25, Me.partnumber)    .Parameters.Append .CreateParameter("p2", adVarChar, adParamInput, 50, Me.title)    .Parameters.Append .CreateParameter("p3", adSmallInt, adParamInput, 2, Me.qtyper)    .Parameters.Append .CreateParameter("p4", adSmallInt, adParamInput, 2, Me.oldqtyper)    .Parameters.Append .CreateParameter("p5", adBoolean, adParamInput, 2, Me.addpartrecordflg)    .Parameters.Append .CreateParameter("p6", adBoolean, adParamInput, 2, Me.doneflg)    .Parameters.Append .CreateParameter("p7", adInteger, adParamInput, 4, Me.aid)    .CommandText = strSQL    Set adoRS = .Execute(lRecordsAffected)  End With   If lRecordsAffected = 0 Then    Update = False  Else    'Return a good return code    Update = True  End If Exit_Update:  'Clean up the connection to the database  Set adoCMD = Nothing  Set adoRS = Nothing   Exit Function Err_Update:  Call errorhandler_MsgBox("Class: " & TypeName(Me) & ", Function: Update()")  Update = False  Resume Exit_Update End Function Is there such similar objects to leverage within Lazarus / Free Pascal? I am thankful, ---------------------------------------------------------------------- Handoko · 2025-11-16 03:21 Quote from: mdlueck on November 16, 2025, 12:31:16 amIs 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i { TFirebird } function TFirebird.SelectQryWithPage(const TblName: string; Page,  ItemsPerPage: Integer): string;var  Skip: string;begin  Skip := '';  if Page > 1 then Skip := ' SKIP ' + ((Page-1)*ItemsPerPage).ToString;  if Page > 0 then Skip := 'FIRST ' + ItemsPerPage.ToString + Skip + ' ';  Result := 'SELECT ' + Skip + TblName + ';';end; { TSQLite } function TSQLite.SelectQryWithPage(const TblName: string; Page, ItemsPerPage: Integer  ): string;var  Limit: string;begin  Limit := '';  if Page > 0 then    Limit := ' LIMIT ' + ItemsPerPage.ToString +             ' OFFSET ' + ((Page-1)*ItemsPerPage).ToString;  Result := 'SELECT ' + TblName + Limit + ';';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.h tml#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 · 2025-11-16 06:07 Quote from: mdlueckIs there such similar objects to leverage within Lazarus / Free Pascal? something like this: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i function TForm1.Update(FETempTableName, partnumber, title: String; qtyper, oldqtyper: Smallint; addpartrecordflg, doneflg: Boolean; aid: Integer): Boolean;var  sqlCMD: TSQLQuery;  strSQL: String;  i, lRecordsAffected: Integer;begin  Result := False;  //Define a query to Update a new record into the FE temp table  strSQL := 'UPDATE [' + FETempTableName + ']' + lineEnding +            'SET [partnumber] = :p1,' + lineEnding +            '[title] = :p2,' + lineEnding +          '[qtyper] = :p3,' + lineEnding +            '[oldqtyper] = :p4,' + lineEnding +            '[addpartrecordflg] = :p5,' + lineEnding +          '[doneflg] = :p6' + lineEnding +            'WHERE [aid] = :p7;';   //Define attachment to database table specifics  sqlCMD := TSQLQuery.Create(nil);  with sqlCMD do  begin    SQLConnection := PQConnection1;    Transaction := SQLTransaction1;    SQL.Text := strSQL;    for i := 0 to Params.Count - 1 do      Params[i].ParamType := ptInput;    ParamByName('p1').AsString := partnumber;    ParamByName('p2').AsString := title;    ParamByName('p3').AsSmallInt := qtyper;    ParamByName('p4').AsSmallInt := oldqtyper;    ParamByName('p5').AsBoolean := addpartrecordflg;    ParamByName('p6').AsBoolean := doneflg;    ParamByName('p7').AsInteger := aid;    try      ExecSQL;      lRecordsAffected := RowsAffected;    SQLTransaction1.Commit;      Result := True;    except      SQLTransaction1.Rollback;      ShowMessage('Update error for table: ' + FETempTableName);    end;  end;  sqlCMD.Free;end; ---------------------------------------------------------------------- mdlueck · 2025-11-16 13:17 Quote from: paweld on November 16, 2025, 07:07:33 amQuote from: mdlueckIs 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,