egnew

Remy: That is what I needed. It works perfectly. Thanks procedure TForm1.GetPropertiesByClassType (p_Class: TClass); var v_Context: TRttiContext; v_Type: TRttiType; v_Property: TRttiProperty; begin Memo1.Lines.Add('----- Properties -----'); v_Type := v_Context.GetType(p_Class); for v_Property in v_Type.GetProperties do Memo1.Lines.Add(v_Property.ToString); end;

I added a function FIndClassByName and was able to get it to work. function TForm1.FindClassByName (const p_ClassName: string): TClass; var v_Context: TRttiContext; v_Type: TRttiType; v_TypeList: TArray<TRttiType>; begin Result := nil; v_Context := TRttiContext.Create; try v_TypeList := v_Context.GetTypes; for v_Type in v_TypeList do begin if v_Type.Name = p_ClassName then begin Result := v_Type.AsInstance.MetaClassType; BREAK; end; end; finally v_Context.Free; end; end; procedure TForm1.GetPropertiesByClassName (p_ClassName: String); var v_Class: TClass; v_Context: TRttiContext; v_Type: TRttiType; v_Property: TRttiProperty; begin Memo1.Lines.Add('----- Properties -----'); v_Class := FindClassByName(p_ClassName); if v_Class <> nil then begin v_Type := v_Context.GetType(v_Class); for v_Property in v_Type.GetProperties do Memo1.Lines.Add(v_Property.ToString); end; end; The FindClassByName function processes a lot of types to get to the one that is needed. Please let me know if there is a better or more efficient way to do this.

I have a procedure that loads the properties of a class into a memo: procedure TForm1.GetProperties (p_Class: TClass); var v_Context: TRttiContext; v_Type: TRttiType; v_Property: TRttiProperty; begin v_Type := v_Context.GetType(p_Class); for v_Property in v_Type.GetProperties do Memo1.Lines.Add(v_Property.ToString); end; Sample Usage GetProperties(TFDPhysPGConnectionDefParams); I would like a procedure to get the properties by class name instead of a class. procedure TForm1.GetPropertiesByClassName (p_ClassName: String); // Changed var v_Class: TClass; // Added v_Context: TRttiContext; v_Type: TRttiType; v_Property: TRttiProperty; begin v_Class := GetClass(p_ClassName); // Access Violation Exception v_Type := v_Context.GetType(v_Class); for v_Property in v_Type.GetProperties do Memo1.Lines.Add(v_Property.ToString); end; Sample Usage: v_FDConnection := TFDConnection.Create(nil); try v_FDConnection.DriverName := 'PG'; GetPropertiesByClassName(v_FDConnection.Params.ClassName); finally v_FDConnection.Free; end; My attempt as shown above results in a runtime exception for Access Violation. How can I get this to work?

Thanks very much Brian. You have been very helpful. Everything I need is working. This issue is resolved. Thanks for the assistance.

Thanks for your explanation. This works as expected: update mytable set testno = testno+:increment where id = 1; However, this does not: begin; update mytable set testno = testno+:increment where id = 1; commit; This results in "ERROR: cannot insert multiple commands into a prepared statement"

Removing the $$ from the query fixed the "Index out of bounds" issue in the FireDAC Query Editor. The parameter properties can now be set and the execute button no longer produces that error. However, that does not provide a solution. I normally program for Oracle, SQL-Server, and other databases. None of these have every had an issue with FDQuery and parameters. I have never heard of $$ begin a thing in Delphi. It is one thing for it to be a feature in PostgreSQL, but FIreDAC should not be bound by that in terms of doing the parameter substitution. Just my opinion. I am new to PostgreSQL but this is what happens with FDQuery3 when I Execute it in the Query Editor. With the original SQL text and after entering the Query Editor, I get the "Index out of bounds" error. Without the $$, I get "Syntax error at or near begin". Without the DO $$, I get "Syntax error at or near update". With just begin/end, I get "cannot insert multiple commands into a prepared statement". But, putting everything back to the original SQL test. 1) I no longer get the original "index out of bounds" to the error 2) Instead, I get the same message I get at runtime which is "Syntax error at or near ":". 3) Automatic recognition of the parameter no longer occurs and the parameter is no longer recognized. If I then exit Query Editor and restart it with the original SQL 1) I again get the "index out of bounds" error 2) The parameter has been automatically recognized Finally: 1) I have a workaround where I just substitute the desired values into the SQL text. That works without error. 2) FireDAC gives different errors for the same SQL text depending on the sequence changes are made. That is a bug. 3) I need to know if there is \another way to specify a query in PostgreSQL without the $$. Is there another way to specify the query without the $$? Thanks

Thanks, Roger. Here you go. I have been using Borland, Inprise, etc. since Turbo Pascal in 1983 and was a beta tester for Rad Studio. You can pretty much just tell me what you want and I will know how to do it. Please know that the values for Datatype and Value could not be set in the FireDAC Query Editor. I edited the form and text to add the settings. I confirmed that I could have just set them in the object inspector. The issue with "List issue out of bounds" only occurs in the Query Editor. object FDQuery3_Copy_Params: TFDQuery Connection = FDConnection1 SQL.Strings = ( 'do $$' 'begin' ' update mytable set testno = testno+:increment where id = 1;' ' commit;' 'end' '$$') Left = 296 Top = 288 ParamData = < item Name = 'INCREMENT' DataType = ftInteger ParamType = ptInput Value = 1 end> end

I did read your comment but it does not appear to have anything to do with my issue. Delphi is not having a problem executing queries with the "DO" in either the IDE or at runtime. The problem is that Delphi is not handling TFDQuery.Params correctly when the database is PostgreSQL. Please see my previous detailed post. At runtime the error is: [FireDAC][Phys][PG][libpq] ERROR: syntax error at or near ":" At design time the error is: List index out of bounds (0). TList<System.Classes.TCollectionItem> is empty. Clearly, FireDAC is not setting the parameter value as instructed at runtime as I get the error at or near ":". But, it also will not allow it to be set when executing from the IDE. Thanks

1) This appears to be a FireDAC bug related to PostgreSQL. Unless someone convinces me otherwise, I will post a bug report at https://qp.embarcadero.com/. At runtime the error is: [FireDAC][Phys][PG][libpq] ERROR: syntax error at or near ":" At design time the error is: List index out of bounds (0). TList<System.Classes.TCollectionItem> is empty. The design time error prevents the INCREMENT param value from being defined properly in the FIREDAC Query Editor. I was able to set the desired values by switching to text mode, adding the correct settings, and then switching back to form mode. It did not make a difference. 2) Environment RAD Studio 12 Version 29.0.51961.7529 Delphi 12 and VC++Builder 12 Update 1 RAD Studio 12.1 Patch 1 Windows 11 (Version 23H2, OS Build 22631.4460, 64-bit Edition) I have installed PostgreSQL drivers for both 32 and 64 bit executables. I have modified the User System Override Path in Rad Studio to allow PostgreSQL queries in the FireDAC Query Editor. c:\postgresql\odbc32\bin;c:\postgresql\odbc64\bin;$(PUBLIC)\Documents\Embarcadero\InterBase\redist\InterBase2020\IDE_spoof;$(PATH) 3) All Application Code is in this event: procedure TForm1.FormShow(Sender: TObject); var v_Step,v_SQL: String; v_pointer: Pointer; begin if SizeOf(v_Pointer) = 4 then FDPhysPGDriverLink1.VendorLib := 'C:\PostgreSQL\odbc32\bin\libpq.dll' else FDPhysPGDriverLink1.VendorLib := 'C:\PostgreSQL\odbc64\bin\libpq.dll'; Memo1.Text := ''; FDConnection1.Connected := True; try FDQuery1.ExecSQL; Memo1.Lines.Add('Query1: Success'); except on E: Exception do Memo1.Lines.Add('Query1: '+E.Message); end; FDQuery1.Close; try FDQuery2.Open; Memo1.Lines.Add('Query2: Success'); except on E: Exception do Memo1.Lines.Add('Query2: '+E.Message); end; FDQuery2.CLose; try v_Step := 'SetParameter'; FDQuery3.ParamByName('increment').AsInteger := 100; v_Step := 'ExecSQL'; FDQuery3.ExecSQL; Memo1.Lines.Add('Query3: Success'); except on E: Exception do begin Memo1.Lines.Add('Query3: Step '+v_Step+' '+E.Message); Memo1.Lines.Add(FDQuery3.SQL.Text); Memo1.Lines.Add('End Query3'); end; end; FDQuery3.CLose; try v_SQL := FDQuery3.SQL.Text; FDQuery3.SQL.Text := StringReplace(v_SQL,':increment','100',[rfReplaceAll,rfIgnoreCase]); FDQuery3.ExecSQL; FDQuery3.SQL.Text := v_SQL; Memo1.Lines.Add('Query3.WorkAround: Success'); except on E: Exception do Memo1.Lines.Add('Query3: '+E.Message); end; FDQuery3.Close; FDConnection1.Connected := False; end; 4) Table mytable in your PostgreSQL database with a single record with testno set to zero. id serial; testno numeric; 5) Components Needed FDConnection1 - TFDConnection to your PostgreSQL database FDPhysPGDriverLink1 - TFDPhysPGDriverLink FDQuery1, FDQuery2, FDQuery3 - TFDQuery as described below. QUERY CONTENT FDQuery1.SQL do $$ begin update mytable set testno = testno+1 where id = 1; commit; end $$ FDQuery2.SQL select t.* from mytable t FDQuery3.SQL - Same as FDQuery1 except "+:increment" replaces "+1". do $$ begin update mytable set testno = testno+:increment where id = 1; commit; end $$ RESULTS Results for FDQuery1 and FDQuery2 in the IDE and at runtime FDQuery1 Executes successfully FDQuery2 Executes successfully Result for FDQuery3 at Runtime Runtime exception [FireDAC][Phys][PG][libpq] ERROR: syntax error at or near ":" Result for FDQuery3 in FireDAC Query Editor An attempt to modify the INCREMENT parameter DataType or Value or to execute the query results in: List index out of bounds (0). TList<System.Classes.TCollectionItem> is empty. Result for FDQuery3 Workaround - runtime only FDQuery3 Executes successfully with ReplaceString instead of ParamAsName.

I posted a solution for my non-IDE issue with PostgreSQL drivers at: https://en.delphipraxis.net/topic/12623-firedac-cannot-load-postgresql-vendor-library/ The solution to enable PostgreSQL in the IDE is to do the following: Go to Tools>Options>Environment Variables>. Under User System Overrides double click PATH and add the path to your 32-bit and 64-bit PostgreSQL drivers. The updated user system override path on my system: c:\postgresql\odbc32\bin;c:\postgresql\odbc64\bin;$(PUBLIC)\Documents\Embarcadero\InterBase\redist\InterBase2020\IDE_spoof;$(PATH) I downloaded the 32-bit and 64-bit drivers using Application Stack Builder installed with PostgreSQL database. I will no longer follow this topic as I have provided a simple and effective solution.

I would like to thank everyone for their ideas but I posted a solution less than two hours after my original post. The solution is to set the VendorLib property of a TFDPhysPGDriverLink. var v_pointer: Pointer; begin if SizeOf(v_Pointer) = 4 then FDPhysPGDriverLink1.VendorLib := 'C:\PostgreSQL\odbc32\bin\libpq.dll' else FDPhysPGDriverLink1.VendorLib := 'C:\Program Files\PostgreSQL\17\bin\libpq.dll'; I initially tried putting the appropriate DLLs in the application debug and release folders without success. I expected that to work as it does with other 3rd-party DLLs. But It does not work with FireDAC on my machine. I have encountered a number of issues with FireDAC and it is not surprising that I needed to use a DriverLink to resolve the issue. I will no longer follow this topic as I have provided a simple and effective solution.

A TFDQuery with PostgreSQL fails to execute when I have parameters in the query. The error message is: [FireDAC][Phys][PG][libpq] ERROR: syntax error at or near ":" ParamByName executes but PostgreSQL sees the ":" and the exception occurs in ExecSQL. This seems to indicate there is an issue with FireDAC setting the parameter. Although I could manually work around the issue by substituting the parameters in the SQL at runtime, I would prefer this to work as it does in other databases. Code try v_Step := 'SetParameter'; FDQuery3.ParamByName('increment').AsInteger := 100; v_Step := 'ExecSQL'; FDQuery3.ExecSQL; except on E: Exception do begin Memo1.Lines.Add(v_Step+': '+E.Message); Memo1.Lines.Add(FDQuery3.SQL.Text); end; end; Memo1.Text ExecSQL: [FireDAC][Phys][PG][libpq] ERROR: syntax error at or near ":" do $$ begin update mytable set testno = testno+:increment where id = 1; commit; end $$

My system already has all Visual C++ Runtimes as this is my development computer.

Thanks. Placing the DLL in the executable directories is the first thing I tried. It works for every other DLL I use. It did not work at all with libpq.dll. When I release the application, libpq.dll will be in the executable directory. I am using Embarcadero® RAD Studio 12 Version 29.0.51961.7529. Delphi 12 Update 1 with Rad Studio 12.1 Patch 1. Windows 11 version 23H2 OS Build 22631.4460, 64-bit edition.

I would like to get the Firedac Query Editor in the IDE to work with Postgresql. When I click the execute button in the Firedac Query Editor, I get this message: [FireDAC][Phys][PG]-314. Cannot load vendor library [C:\Prograqm Files\PostgreSQL\17\bin\libpq.dll. Library has unsupported architecture [x64]. Required [x86] %1 is not a valid Win32 application. Hint: check it is in the PATH or application EXE directories, and has x86 bitness. The library mentioned is the directory containing the installation for PostgreSQL. I want to override that and have it use the 32-bit library which is in c:\postgresql\odbc32\bin. My system path begins with c:\postgresql\odbc32\bin; and does not mention the path the IDE is using. The IDE override path is set to: $(PUBLIC)\Documents\Embarcadero\InterBase\redist\InterBase2020\IDE_spoof;$(PATH) How do I get the IDE to use the 32-bit library? Thanks