Query with parameters

This works.
{code}
query.SQL.text := 'select PK_VALUE from Admin.CR_KEY_GEN where PK_FIELD = ''CRFEE''';
query.Open;
{code}

This doesn't.
{code}
query.SQL.text := 'select PK_VALUE from Admin.CR_KEY_GEN where PK_FIELD = :TransType';
query.ParamByName('TransType').AsString := 'CRFEE';
query.Open;
{code}

The first case, I get as expected 1 row. The second case, I get nothing. Am I
0
Lawrence
6/4/2013 7:01:19 PM
embarcadero.delphi.firedac 822 articles. 2 followers. Follow

16 Replies
10311 Views

Similar Articles

[PageSpeed] 8

Lawrence Tse wrote:

> query.ParamByName('TransType').AsString := 'CRFEE';

Try this:


query.ParamByName('TransType').AsString := QuotedStr('CRFEE');

[]s


Cesar Romero
0
Cesar
6/5/2013 1:30:40 PM
Depending on your Database connection type (DBX, DBgo, ..) you can try:

query.Params.ParamByName('TransType').AsString := 'CRFEE';

note the extra "Params".
0
Robert
6/5/2013 2:18:13 PM
> {quote:title=Cesar Romero wrote:}{quote}
> Try this:
> 
> query.ParamByName('TransType').AsString := QuotedStr('CRFEE');
> 

Tried this and got nothing back.
0
Lawrence
6/5/2013 5:18:50 PM
> {quote:title=Robert Triest wrote:}{quote}
> Depending on your Database connection type (DBX, DBgo, ..) you can try:
> 
> query.Params.ParamByName('TransType').AsString := 'CRFEE';
> 
> note the extra "Params".

I'm using FireDAC to access Oracle.
Params.ParamByName didn't work either
0
Lawrence
6/5/2013 5:31:30 PM
Hello Lawrence

Please produce a trace file and post it to attachments newsgroup:
http://docs.embarcadero.com/products/rad_studio/firedac/Tracing_and_Monitoring.html

Put into your test only these your two queries.

-- 
With best regards,
Dmitry Arefiev / FireDAC Architect
FireDAC - Firebird, SQLite, MySQL, SQL Server, Oracle, PostgreSQL,
DB2, SQL Anywhere, Access, Informix, ODBC high-speed data access lib
0
Dmitry
6/5/2013 6:22:38 PM
> {quote:title=Dmitry Arefiev wrote:}{quote}
> Hello Lawrence
> 
> Please produce a trace file and post it to attachments newsgroup:
> http://docs.embarcadero.com/products/rad_studio/firedac/Tracing_and_Monitoring.html
> 
> Put into your test only these your two queries.

First query is the parameterized query that fails.
0
Lawrence
6/5/2013 7:29:20 PM
PK_FIELD is defined as CHAR(20) if that makes a difference.
0
Lawrence
6/5/2013 11:45:18 PM
The best will be to get a trace file.

Also, you can try to set:
FormatOptions.StrsTrim to False
FormatOptions.StrsEmpty2Null to False

-- 
With best regards,
Dmitry Arefiev / FireDAC Architect
FireDAC - Firebird, SQLite, MySQL, SQL Server, Oracle, PostgreSQL, 
DB2, SQL Anywhere, Access, Informix, ODBC high-speed data access lib
0
Dmitry
6/6/2013 2:38:01 AM
> {quote:title=Dmitry Arefiev wrote:}{quote}
> The best will be to get a trace file.
> 
> Also, you can try to set:
> FormatOptions.StrsTrim to False
> FormatOptions.StrsEmpty2Null to False
> 
> -- 
> With best regards,
> Dmitry Arefiev / FireDAC Architect
> FireDAC - Firebird, SQLite, MySQL, SQL Server, Oracle, PostgreSQL, 
> DB2, SQL Anywhere, Access, Informix, ODBC high-speed data access lib

The trace file is in the attachments area with the same subject title
0
Lawrence
6/6/2013 2:41:57 AM
> PK_FIELD is defined as CHAR(20) if that makes a difference.
Did you try to access an integer field?

query.ParamByName('MyFieldID').AsInteger := 10;
0
Robert
6/6/2013 7:25:00 AM
> {quote:title=Robert Triest wrote:}{quote}
> > PK_FIELD is defined as CHAR(20) if that makes a difference.
> Did you try to access an integer field?
> 
> query.ParamByName('MyFieldID').AsInteger := 10;

When I do a Select * with no constraints, 
{code}
query.FieldByName('PK_FIELD').DataType = ftString returns True
{code}
0
Lawrence
6/6/2013 5:05:15 PM
Is the problem that you need to select the field that you use in your where statement?

>query.SQL.text := 'select PK_VALUE, PK_FIELD  from Admin.CR_KEY_GEN where PK_FIELD = :TransType';
>query.ParamByName('TransType').AsString := 'CRFEE';
>query.Open;
0
Robert
6/7/2013 7:05:20 AM
Ok, clear. CHAR(N) requires special handling, when you are
working with parameters, because values in the DB are padded
by spaces on the right. You have two options:

1) Pad by spaces the ftString parameter value:

query.ParamByName('TransType').AsString := 'CRFEE' + StringOfChar(' ', 15);
query.Open;

2) Use ftFixedChar data type and specify the size.
There are two steps. First setup your query and parameter:

query.FormatOptions.StrsTrim := False;
with query.ParamByName('TransType') do begin
  DataType := ftFixedChar;
  Size := 20;
end;

then call open:

query.ParamByName('TransType').AsString := 'CRFEE';
query.Open;

PS: To simplify things, you can change PK_FIELD data type
from CHAR(N) to VARCHAR2(N).

-- 
With best regards,
Dmitry Arefiev / FireDAC Architect
FireDAC - Firebird, SQLite, MySQL, SQL Server, Oracle, PostgreSQL, 
DB2, SQL Anywhere, Access, Informix, ODBC high-speed data access lib
0
Dmitry
6/7/2013 8:36:31 AM
I tried to set the values for the parameter in the parameters tab in the query editor, but it didn't seem to be working. Doing it in code does work and I'll just go with that for now. Thanks!
0
Lawrence
6/7/2013 6:29:50 PM
>I tried to set the values for the parameter in the parameters tab 
> in the query editor, but it didn't seem to be working.

I tried both options in Query Editor and they are working.
Probably you have missed some of the settings.

-- 
With best regards,
Dmitry Arefiev / FireDAC Architect
FireDAC - Firebird, SQLite, MySQL, SQL Server, Oracle, PostgreSQL, 
DB2, SQL Anywhere, Access, Informix, ODBC high-speed data access lib
0
Dmitry
6/9/2013 8:29:03 AM
I have a similar problem. My application accesses to different databases. In some databases the same filed is CHAR(N), in others it is VARCHAR(N). I'd like my code works fine (with StringTrim enabled) with both field type without perform the trick you have presented. Is it possible? With BDE same code worked fine with both field type. Thank you.
How can i accomplish that?
> {quote:title=Dmitry Arefiev wrote:}{quote}
> Ok, clear. CHAR(N) requires special handling, when you are
> working with parameters, because values in the DB are padded
> by spaces on the right. You have two options:
> 
> 1) Pad by spaces the ftString parameter value:
> 
> query.ParamByName('TransType').AsString := 'CRFEE' + StringOfChar(' ', 15);
> query.Open;
> 
> 2) Use ftFixedChar data type and specify the size.
> There are two steps. First setup your query and parameter:
> 
> query.FormatOptions.StrsTrim := False;
> with query.ParamByName('TransType') do begin
>   DataType := ftFixedChar;
>   Size := 20;
> end;
> 
> then call open:
> 
> query.ParamByName('TransType').AsString := 'CRFEE';
> query.Open;
> 
> PS: To simplify things, you can change PK_FIELD data type
> from CHAR(N) to VARCHAR2(N).
> 
> -- 
> With best regards,
> Dmitry Arefiev / FireDAC Architect
> FireDAC - Firebird, SQLite, MySQL, SQL Server, Oracle, PostgreSQL, 
> DB2, SQL Anywhere, Access, Informix, ODBC high-speed data access lib
0
Federico
3/14/2014 3:56:50 PM
Reply:

Similar Artilces:

Queries with parameters fail in Delphi XE3
Hello Folks! I have been attempting to move a Delphi XE2 DataSnap server & client project to Delphi XE3. I had to do some minor tweaks like adding the unit IPPeerServer.pas to the uses clause of the server container class but what has really caused me headaches are the parameterised queries. If I try to open a ClientDataSet on the client-side I run into a "Catastrophic failure". Here is the call stack on the client-side at the time: System._DynArrayAddRef(???) System.Classes.MoveData((0, 0, 0, ... System.Classes.TMemoryStream.Write((0, 0, 0, ... System.Classes.TStream.Wr...

Parameter query
Hi: I am trying to build maste/detail page.  1.  I have on my page drop down control based on sqldata sources with the concataned fields as follows:   SELECT DISTINCT LastName + ' ' + FirstName AS Expr1 FROM dbo.Table1 It displays First Name space and Last name in the box and works fine. 2. I placed on my form a grIdview In data source I am trying to use parameter filter in SQL select query and the same query as for drop down as follows: SELECT     LastName + ' ' + SiteFirstName AS Expr1, field1, field2, etc.........

Problem with Delphi XE2 using parameters in SQL query [Edit]
Hello, I have problem with using SQL with parameters. This part of code works fine on Delphi 2007, but have Debugger Exception Notification in Delphi XE2 with ADOTmp do *//works fine on Delphi2007 and Delphi XE2, date value is inserted* begin SQL.Clear; *SQL.Add('UPDATE Table SET Date = ''2012-04-03 13:13:13'' ');* ExecSQL; end; with ADOTmp do *//works fine on Delphi2007* begin SQL.Clear; *SQL.Add('UPDATE Table SET Date = :D1 ');* Parameters.ParamByName('D1').DataType := ftDateTime; Parame...

Getting parameter not found error at Delphi-SQLite query [Edit]
I am getting error like 'parameter not found' with following SQL query at Delphi In table emp usergroup_id :- integer and label, description are string data type. My code is as follows {code} gid: Integer; sdescription,ldescription: String; SQLQuery31: TSQLQuery; SQLQuery31 := TSQLQuery.Create(nil); sdescription := RzEdit1.Text; ldescription := RzMemo1.Text; gid := Integer(RzComboBox1.Items.Objects[RzComboBox1.Items.IndexOf(gname)]); try SQLConnection1.Connected := true;...

C# Ado.net Sql query parameter query
Hi All, I'm trying to pass in a parameter value from an array in a loop that is used in a sql query and the results are populated to an xml file. The trouble is that I'm only getting the colums values in the outputted xml file. So I feel that the paramter is not being read. So can anyone help as I'm really stuck on this one. The code is as follows : public void DisplayUserInfo() { ArrayList UserIdArrayList = IdentifyUserID(); foreach(string ShowUserIDString in UserIdArrayList) { try { SqlConnection SqlConn = new SqlConnection(DBConnString)...

FireDAC and Delphi Pro XE4... firedac tab not visible?
I am moving from a very old Borland product (2006) to Delphi XE4 Professional with FireDAC addon. I purchased both from Embarcadero, and have downloaded both. The documentation says the FireDAC components should automagically appear in the tabset for Delphi. They do not. I don't want to fool around too much as my environment is strictly controlled by IT, and they are very slow to do anything. Is there something that I should have done, but did not do? I installed XE4 first, registered it, then used the link in my email from Embarcadero to download the FireDAC components. Then I ra...

Query a Query
I have a result set in a query. Is it possible to query this query? (no specific SQL dialect, can it be done generically?) Petros -- PETROS GAVRIELIDES wrote: > I have a result set in a query. > Is it possible to query this query? > > (no specific SQL dialect, can it be done generically?) There is a generic answer to your question: Yes, there is a construct called a sub-query that allows you, in 1 SQL statement, to query from a query. But, generically speaking, not all DB engines support this construct. For instance, Firebird only added it just over a y...

Query with Parameter
I am trying to use values from query as keys for another query. I have one main form with tabforms on it. On the main form I have a main query, query_1. On the tabform I have a query, query_2. Each record in query_1 will have multiple corresponding records in query_2. Each time I move the cursor in query_1 I want query_2 to re-execute and produce a new recordset. I will do this by passing values from query_1 to query_2 as parameters. I can get the values from query_1 but I am having trouble getting query_2 to execute with the parameters. I am using SetParameter( ) but to no avail. Co...

Query about Queries
Hello all, As a relatively new user to Sybase I am hoping someone can point me in the right direction. We are getting ready to go live with our database and I am wondering what types of queries I should be running for system performance purposes. If anyone has any suggestions they would be more than appreciated. Thanks in advance, Jerry ...

Query parameters
Hi, I have found a problem with the customizable queries. In the function SaveQuery in the file AccessDataAccessLayer.cs, there is a foreach loop to go through each query clause and add them in the database. However, if for example I create a query with two clauses, the boolean operator for the second clause will be the same of the one for the first clause. It seems that the new value is not set properly in the parameter. Also, the length of the fieldname of the first clause is applied to the fieldname of the second clause and if it is longer, the fieldname is cut, causing an er...

Parameters in query
Hello, first of all, if I'm posting in the wrong section fell free to be rude. I apologize.I'm pretty new to mdx and Reporting services, and already got stuck on a pretty stupid problem..I need a table that shows a measure in 2 different times, on columns. Like this:-----|-----|  Q1   | Q2   |bla | bla|  200 |  300  |bla | bla|  21   |  32   |bla | bla|  43   |  77   |This is the query i'm using:withmember [Measures].[Q1] as ([Measures].[Number of Machines], [TimeID].[Mon Name].[June 2008])member...

query parameter
I have to create a stored procedure where the criteria is: "All", specific value and, all except one value so far this is what I have:@Status varchar (50)  -- as parameter from a dropdown boxDECLARE @NewStatus varchar(50) SET @NewStatus = CASE WHEN @Status ='All' AND @Status <> 'All but closed' THEN '%' WHEN @Status <>'All' AND @Status <> 'All but closed' THEN @Status END and in the stored procedure .......... WHERE Status like @NewStatus  I am a little confused as to how could I return all values except those that have  the ...

How to query a query...
I'm working on converting an Access DB that I created into an ASP.NET application (and attempting to learn ASP at the same time...). One of my queries was very complex and required running a different query first, and then working off of that one. Any suggestions on the best way to do this in ASP? The following is a sample of code I'm using so you can see the type of data-binding I'm using. Thanks for the help! myConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; " +"Data Source=" + Server.MapPath("/EquipmentCheckout.mdb")) strS...

Query Parameter
I have the following code:             Dim ConnectString As String             Dim MyDataSet As New DataSet                                                 Dim Connect As OleDbConnection = New OleDbConnection       &nbs...

How to Query A Query
I have been trying to build a crosstab report that reports everydate within a selected range across the top and specific row information. 7/1 7/2 7/3 . . . Name Detail1 Detail2 Detail3 Name Detail1 What I get as output is only the dates that contain information in the rows. I can get the information I need by using a query within a query (nested). Can a nested query be done in Informaker 5? What about version 6 or 7? Thanks for your help Convert your SQL to syntax, and you can write anything you want. So if your DB support...

Web resources about - Query with parameters - embarcadero.delphi.firedac

Parameter - Wikipedia, the free encyclopedia
A parameter (from the Ancient Greek παρά , "para", meaning "beside, subsidiary" and μέτρον , "metron", meaning "measure"), in its common meaning, ...

fb_source Parameter - Facebook-Entwickler
This document lists the complete list of values for the fb_source parameter that is returned with th...

Google Webmaster Tools Parameter Handling - Flickr - Photo Sharing!
You aren't signed in Sign In Help Home The Tour Sign Up Explore Explore Home Last 7 Days Interesting Popular Tags Calendar Most Recent Uploads ...

Cat 8: TIA performance standards committee chairman previews parameters of forthcoming twisted-pair cable ...
Category 8, the next-generation twisted-pair cabling specification, is still in development, but the outlook looks quite positive that it will ...

Philippine president asks for 'parameters' of Mayon Volcano tours
Philippine president asks for 'parameters' of Mayon Volcano tours People's Daily Online ... 26, 27, 28, 29, 30, 31. Jan, Feb, Mar, Apr, May, ...

Alternatives to boolean parameters
... ) { PrintLine ( item . Name ); if ( printDetails ) { PrintLine ( item . Description ); } } } Aha! Now can see that the boolean parameter ...

Exporting Error (Error in Parameter...: Apple Support Communities
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability ...

Apple tech monitors device aging, adjusts operating parameters to ensure optimal performance
... describing a method of monitoring the aging of a device's electronics, comparing the data with a global average and modifying operating parameters ...

Is it poor programming practice to pass parameters as objects?
... a free, community-powered network of 100+ Q&A sites . Risser asked: So, we've got a guy who likes to write methods that take Objects as parameters, ...

Parameter Short Film Competition sponsored by Grolsch
Grolsch presents Parameter The A.V. Club Short Film Competition Please Enter Your Date of Birth Ineligible entry Enter For people over the age ...

Resources last updated: 12/16/2015 6:11:53 AM