How to Get a Value Using SQL in Delphi and Setting the Value to a Variable

How to get a value using SQL in Delphi and setting the value to a variable?

You're not using the query properly. qryPrices.SQL is the SQL statement itself. It's just text. You need to do something to actually run the statement. (See below.)

You've also embedded the variable inside the quotes, which means it's not being evaluated, and neither is the function call to the (misspelled) QuotedStr. There is no function quaotedStr(). If you insist on the poor idea of concatenating SQL, you need to do it properly. If you're going to clear and then add, you can just assign to SQL.Text instead to do it in one step:

dmHospital.qryPrices.SQL.Text := 'SELECT Price(R) FROM MedicationPrices WHERE Medication = ' + Quotedstr(sMedication);

Also, the query won't do anything until you actually execute it. You need to use qryPrices.Open to run a SELECT statement, or qryPrices.ExecSQL to run an INSERT, UPDATE or DELETE statement.

You should get out of the thought of concatenating SQL immediately (before you get the habit) and learn to use parameterized queries. It allows the database driver to handle the formatting and conversion and quoting for you, and it also prevents SQL injection that can give others access to your data. Here's a corrected version that should get you started.

procedure TForm1.BuyButtonClick(Sender: TObject);
var
  sMedication : string;
  sRate : string;
begin
  iAmount := 0;
  sMedication := BuyCombobox.Items[BuyCombobox.ItemIndex];
  dmHospital.qryPrices.SQL.Text := 'SELECT Price(R) FROM MedicationPrices WHERE Medication = :Medication';
  dmHospital.qryPrices.Parameters.ParamByName('Medication').Value := sMedication;
  dmHospital.qryPrices.Open;
  sRate := dmHospital.qryPrices.FieldByName('Price(R)').AsString;
  dmHospital.qryPrices.Close;
  ShowMessage(sRate);
end;

Get the result from the Query

Try this code:

procedure TForm1.ExecuteQuery;
var
  SQL : String;
  Q : TFDQuery;
begin
  SQL := 'select ''Sami'' as NOM';  //  Tested with MS Sql Server backend
  try
    Q := TFDQuery.Create(Self);
    Q.Connection := FDConnection1;
    Q.Params.CreateParam(ftString, 'Nom', ptOutput);// Try also ptResult
    Q.SQL.Text := SQL;
    Q.Open;
    ShowMessage( IntToStr(Q.ParamCount));
    Caption := Q.FieldByName('Nom').AsString;
  finally
    Q.Free;  // otherwise you have a memory leak
  end;
end;

You'll see that the created parameter no longer exists once the FDQuery is opened, because FireDAC "knows" that there is nothing it can do with it.

Then, replace Q.Open by Q.ExecSQL. When that executes you get an exception
with the message

Cannot execute command returning result set.
Hint: Use Open method for SELECT-like commands.

And that's your problem. If you use a SELECT statement, you get a result set whether
you like it or not, and the way to access its contents is to do something like

Nom := Q.FieldByName('Nom').AsString

You asked in a comment what is the point of ptOutput parameters. Suppose your database has a stored procedure defined like this

Create Procedure spReturnValue(@Value varchar(80) out)
as
select @Value = 'something'

Then, in your code you could do

  SQL := 'exec spReturnValue :Value';  //  note the absence of the `out` qualifier in the invocation of the SP

  try
    Q := TFDQuery.Create(Self);
    Q.Connection := FDConnection1;
    Q.Params.CreateParam(ftString, 'Value', ptOutput);// Try also ptResult
    Q.SQL.Text := SQL;
    Q.ExecSQL;
    ShowMessage( IntToStr(Q.ParamCount));
    Caption := Q.ParamByName('Value').AsString;
  finally
    Q.Free;  // otherwise you have a memory leak
  end;

which retrieves the output parameter of the Stored Proc into Q's Value parameter.

In Delphi, how can I multiply a sql field and a value?

Assuming:

1. Your application is FMX based then Label1.Text is okay, else you need Label1.Caption
2. SpinBox comes from FMX library, the Value type is Double, not an Integer

The code is:

Label1.Text := (MSQuery1.Fields[3].AsInteger * SpinBox1.Value).ToString;
// Or using older versions of Delphi
Label1.Text := FloatToStr(MSQuery1.Fields[3].AsInteger * SpinBox1.Value);

Why, if adding a value to a database through a variable, then “ADOQuery1” is added, and if directly, then a correct value?

Your use of with is causing the reference to Name to resolve to AdoQuery1.Name instead of your local variable Name.

with FramePOstitionsAdd1.AdoQuery1 do
begin
  // Name used here, because of the with, refers to the object in the
  // with statement and not the local variable declared outside the
  // with block.
end;

Either change the name of the variable to something else, or (better) stop using with because of the side effects it can have that cause issues like this.

procedure TForm1.FramePositionsAdd1ButtonAddClick(Sender: TObject);
var
  Title: String;
begin
  Title := FramePositionsAdd1.EditName.Text;

  with FramePositionsAdd1.ADOQuery1 do
  begin
    SQL.Clear;
    SQL.Text := 'INSERT INTO Должности ' +
      '(Наименование) ' +
      'VALUES ' +
      '(:title)';
    Parameters.ParamByName('title').Value := Title;
    ExecSQL;
  end;
end;

Better solution that prevents future problems of the same nature due to edits:

procedure TForm1.FramePositionsAdd1ButtonAddClick(Sender: TObject);
var
  Name: String
  Qry: TAdoQuery;
begin
  Name := FramePositionsAdd1.EditName.Text;
  // Get a local reference to your query
  Qry := FramePositionsAdd1.ADOQuery1;

  // Use that local reference
  Qry.SQL.Clear;
  Qry.SQL.Text := 'INSERT INTO Должности ' +
      '(Наименование) ' +
      'VALUES ' +
      '(:title)';
  Qry.Parameters.ParamByName('title').Value := Name;
  Qry.ExecSQL;
  // DO NOT free or nil Qry here. It is just a pointer to the original,
  // not a new object, and freeing it will free the original query instead.
end;

Getting the value of a query into a parameter

Short answer: Keep doing it the way you do now. There is no reason to use parameters to fetch query results.

Longer answer:

You can use an ADOCommand (not sure what the ODBCExpess equivalent is) and execute a SELECT INTO query:

SELECT
  YourField
INTO
  :YourParam
FROM
  YourTable
WHERE
  ID = 1

In a similar way you can execute stored procedures.

However, using parameters is mainly done for making sure the input is valid and is treated as a certain type. It also allows databases to use better caching, because they cache the query once and see the parameters as a variable piece.

For the output (returned fields), this is useless. The query is already cached even with normal columns. So just execute the query and read its fields as you do now. You can use parameters, but they only add more complexity and limits, without any benefits. These kinds of parameters are specifically useful for reading output of stored procedures and program blocks.

In your specific case, you want to retrieve a lot of fields (maybe all?) In that case it may be better to execute a single query and loop through the results, than to execute 1000 little queries like you do now. Each query has a little overhead for initialization and such, and you save that overhead a 1000 times if you manage to query all the data you need at once.
Also, if you do that (fetching multiple records of data), there is no way to fetch those results into parameters, so that's one of the reasons why parameters would limit you and thus another reason to use parameters only for input if you can.

Your results:

The reason you're getting 0 is because you never set the parameter value. So its value remains NULL. Because you request the value using AsInteger, this value is converted to an integer, therefore returning 0.

The value returned by the query (the one field that is returned) is probably 0 as well. You actually query an expression X = Y where X is the value of the parameter and Y is the value of the field. Since the parameter's value is NULL, the expression always evaluates to false (using the normal comparison operators on NULL values always returns false, no matter which operator you use or what the other value is). Since true and false are usually represented as 0/1 tiny int fields, the field value would probably return 0 as well. But you don't use this value in your posted code.

---

Related Topics