SQL

[BoDGie]

How do i go about making a variable become equal to a executed SQL statement

like say i want to get the name of a dvd title from a barcode with the sql statement SELECT title FROM dvd WHERE barcode = 34534534

 

[svanels]

Make an editbox (this wil capture the barcode characters), put some other DBaware controls, DBGrid or DBedit or DBText, put in a query, datasource etc.

Set the SQL property of the Query to:

'Select title from DVD where barcode = :IBarcode'

In the OnChange event of the EditBox type in:

with query1 do
begin
Close;
ParamByName('IBarcode').AsString := Tedit1.text;
Open;
end;

Regards


Steven van Els
SAvanEls@cq-link.sr

 

[earlrainer]

or the opposite

Query1.Active:=false;
Query1.sql.clear;
Query1.sql.add('SELECT title FROM dvd WHERE barcode = 34534534');
Query1.Active:=true;


edit1.text:=query1['Title'];

 

[towerbase]

If you want to set a variable (instead of a DB aware control) to the value you need then do something like:

function GetDVDtitle ( barcode: integer ): string;
var
  Query: TQuery;
begin
  Query := TQuery.Create ( nil );
  try
    Query.DatabaseName := 'name';
    Query.SQL.Add ( 'SELECT title AS t ' );
    Query.SQL.Add ( 'FROM dvd ' );
    Query.SQL.Add ( 'WHERE barcode=' + IntToStr(barcode) );
    Query.Open;
    result := Query.FieldByName('t').AsString; 
  finally
    Query.Free;
  end;

The key to this is the
SELECT title AS t
bit of the SQL. I arbritarily picked 't' to be the destination variable. The AS clause is not very well documented in books like Cantu. It took me a long time to find it.

Hope this helps.

Andrew

 

[towerbase]

I should have added that the AS clause is used when getting something like the count of records from a TQuery. For example:

...
Query.SQL.Add ( 'SELECT count(*) AS count FROM dvd' );
...
result := Query.FieldByName('count').AsInteger;
...

You do not need to use the AS clause for a column that already exists in your table. Sorry about that.

Andrew

 

[lespaul]

I would also suggest using the variable as Andrew does rather than the ParamByName method. I have currently been working on a project that I have to run 20 queries or so before opening the result form. Using paramByname for all the queries required 1 - 1 1/2 minutes of processing where using variables in each query cut the time down to 10 seconds or less.

Make sure you use the SQL.Clear though or subsequent running of the query appends the new SQL to the lastSQL.

HTH


Leslie
landrews@metrocourt.state.nm.us

There are 10 types of people in the world -
those who understand binary
and
those who don't!

 

[svanels]

The statement in my code assumes that the barcode reader is used to do the input, instead of the keyboard.

Steven van Els
SAvanEls@cq-link.sr