Getting a query's memo field as a string

Question

How can I get a query's memo field as a string?

Answer

Place a query object on your form (Query1)
Place a Edit object on your form (Edit1)
Place a Button object on your form (Button1)
Double-Click on the query and add the memo field.
 (Biolife.db using notes field)
Set Query1's SQL property to: Select * from Biolife
Set Query1's Active property to: True
Add the following code to Button1's OnClick event:

procedure TForm1.Button1Click(Sender: TObject);
var
  bs : TBlobStream;
  p  : array [0..50] of char;
begin
  FillChar(p, SizeOf(p), #0);
  bs:= TBlobStream.Create(Query1Notes, bmRead);
  try
    bs.Read(p,50);
  finally
    bs.Free;
  end;
  Edit1.Text:=StrPas(p);
end;

Creating and using parameterized queries

Question

How do I pass a variable to a query?

Answer

First, you must write a query that uses a variable.

Select Test."FName", Test."Salary Of Employee"
From Test
Where Test."Salary of Employee" > :val

Note:  If you just write the field name as 
"Salary of Employee" you will get a Capability Not
Supported error.  It must be Test."Salary of Employee".

In this can the variable name is "val", but it can be whatever 
you want (of course).  Then, you go to the TQuery's params 
property and set the "val" parameter to whatever the 
appropriate type is. In our example here we will call it an 
integer.

Next, you write the code that sets the parameter's value.  
We will be setting the value from a TEdit box.

procedure TForm1.Button1Click(Sender: TObject);
begin
  with Query1 do
  begin
    Close;
    ParamByName('val').AsInteger := StrToInt(Edit1.Text);
    Open;
  end;
end;


Note:  you may want to place this code in a try..except 
block as a safety precaution.

If you want to use a LIKE in your query, you can do 
it this way:

Note:  This next section uses the customer table from 
the \delphi\demos\data directory.  It can also be 
referenced by using the DBDEMOS alias.

SQL code within the TQuery.SQL property:

  SELECT * FROM CUSTOMER
  WHERE Company LIKE :CompanyName

Delphi code:

procedure TForm1.Button1Click(Sender: TObject);
begin
  with Query1 do
  begin
    Close;
    ParamByName('CompanyName').AsString := Edit1.Text + '%';
    Open;
  end;
end;

An alternate way of referencing a parameter 
(other then ParamByName) is params[TheParameterNumber].  

The way that this line:

    ParamByName('CompanyName').AsString := Edit1.Text + '%';

can be alternately written is:

    Params[0].AsString := Edit1.Text + '%';


The trick to the wildcard is in the concatenating of the 
percentage sign at the end of the parameter.  


DISCLAIMER: You have the right to use this technical information
subject to the terms of the No-Nonsense License Statement that
you received with the Borland product to which this information
pertains.

SQL: embedded spaces in field/column names

Question

Answer

Implementing SQL with spaces or special characters in field/column names

Implementing SQL statements in Delphi's TQuery component (or the
SQL query facilities of Database Desktop, Visual dBASE or Paradox
for Windows) requires special syntax for any columns that contain
spaces or special characters.

Using the Biolife.DB table of from Delphi's demo data to
illustrate, and without the use of any special syntax
requirements, a SQL Select statement might be formed as follows,

SELECT
 Species No,
 Category,
 Common_Name,
 Species Name,
 Length (cm),
 Length_In,
 Notes,
 Graphic
FROM
 BIOLIFE

While appearing normal, the space in the species number and name
columns and the column expressing length in centimeters - as well
as the parentheses present - cause syntax errors.

Two changes must be taken to correct the syntax of the above SQL
statement.  First, any columns containing spaces or special
characters must be surrounded by single (apostrophe) or double
quotes.  Secondly, a table reference and a period must precede
the quoted column name.  This second requirement is particularly
important since a quoted string alone is interpreted as a string
expression to be yielded as a column value.  A properly formatted
statement follows:

SELECT
 BIOLIFE."Species No",
 BIOLIFE."Category",
 BIOLIFE."Common_Name",
 BIOLIFE."Species Name",
 BIOLIFE."Length (cm)",
 BIOLIFE."Length_In",
 BIOLIFE."Notes",
 BIOLIFE."Graphic"
FROM
 "BIOLIFE.DB" BIOLIFE

The above example uses the table alias BIOLIFE as the table
reference that precedes the column name.  This reference may take
the form of an alias name, the actual table name, or a quoted
file name when using dBASE or Paradox tables.  The following
SQL statements would serve equally well.

Note: This SQL statement may be used provided that the necessary
alias is already opened.  In the case of the TQuery this means the
alias is specified in the DatabaseName property.

SELECT
 BIOLIFE."Species No",
 BIOLIFE.Category,
 BIOLIFE.Common_Name,
 BIOLIFE."Species Name",
 BIOLIFE."Length (cm)",
 BIOLIFE.Length_In,
 BIOLIFE.Notes,
 BIOLIFE.Graphic
FROM
 BIOLIFE

If an alias is not available then the entire path to the table
can be specified as in this example:

SELECT
 "C:\DELPHI\DEMOS\DATA\BIOLIFE.DB"."Species No",
 "C:\DELPHI\DEMOS\DATA\BIOLIFE.DB"."Category",
 "C:\DELPHI\DEMOS\DATA\BIOLIFE.DB"."Common_Name",
 "C:\DELPHI\DEMOS\DATA\BIOLIFE.DB"."Species Name",
 "C:\DELPHI\DEMOS\DATA\BIOLIFE.DB"."Length (cm)",
 "C:\DELPHI\DEMOS\DATA\BIOLIFE.DB"."Length_In",
 "C:\DELPHI\DEMOS\DATA\BIOLIFE.DB"."Notes",
 "C:\DELPHI\DEMOS\DATA\BIOLIFE.DB"."Graphic"
FROM
 "C:\DELPHI\DEMOS\DATA\BIOLIFE.DB"

Finally, two facilities that automatically handle this special
formatting exist.  The first is the Visual Query Builder that is
a part of the Client/Server version of Delphi.  The Visual Query 
Builder performs this formatting automatically as the query is built.
The other facility is Database Desktop's Show SQL feature, available
when creating or modifying a QBE-type query.  After selecting
Query|Show SQL from the main menu, the displayed SQL text may be
cut and pasted where needed.


DISCLAIMER: You have the right to use this technical information
subject to the terms of the No-Nonsense License Statement that
you received with the Borland product to which this information
pertains.

SQL: Sorting on a calculated column

Question

How to sort on a calculated column?

Answer

At times, a given data schema will require that a data set will need to be
ordered by the result of a calculation. In Delphi applications using SQL,
this is possible, but the methodlogy varies slightly depending on the
database type used.

For local SQL involving Paradox and dBASE tables, the calculated field
would be given a name using the AS keyword. This allows the calculated
field to be referenced for such purposes as setting a sort order with an
ORDER BY clause in an SQL query. For example, using the sample table
ITEMS.DB:

  SELECT I."PARTNO", I."QTY", (I."QTY" * 100) AS TOTAL 
  FROM "ITEMS.DB" I
  ORDER BY TOTAL
  
In this example, the calculated field is designated to be referred to as
TOTAL, this column name then being available for the ORDER BY clause for
this SQL statement.

The above method is not supported for InterBase. It is still possible,
though, to sort on a calculated field in InterBase (IB) or the Local
InterBase Server tables. Instead of using the name of the calculated
field, an ordinal number representing the calculated field's position in
field field list is used in the ORDER BY clause. For example, using the
sample table EMPLOYEE (in the EMPLOYEE.GDB database):

  SELECT EMP_NO, SALARY, (SALARY / 12) AS MONTHLY
  FROM EMPLOYEE
  ORDER BY 3 DESCENDING

While IB or LIBS tables require this second method and cannot use the
first method described, either of the two methods can be used with local
SQL. For example, using the SQL query for the Paradox table and adapting
it to use the relative position of the calculated field rather than the
name:

  SELECT I."PARTNO", I."QTY", (I."QTY" * 100) AS TOTAL 
  FROM "ITEMS.DB" I
  ORDER BY 3

SQL: Summarizing a calculated column

Question

Answer

Occasionally in a Delphi application that uses SQL to access data, it
becomes necessary to summarize calculated data. That is, to create a
calculated column and apply the SUM function to it.

When performing this operation against SQL tables (such as those for the
Local InterBase Server), it is a simple matter of enclosing the
calculation within the SUM function. For example, using the sample table
EMPLOYEE (in the EMPLOYEE.GDB database):

  SELECT SUM(SALARY / 12)
  FROM EMPLOYEE

This same methodology can also be used when the returned data set is to be
grouped by the value in another column with a GROUP BY clause:

  SELECT EMP_NO, SUM(SALARY / 12)
  FROM EMPLOYEE
  GROUP BY EMP_NO
  ORDER BY EMP_NO

While SQL databases support the summarization of calculated columns,
local SQL will not. Other means would be needed to obtain the results,
such as copying the results of a query with a calculated column to a
temporary table (as with a TBatchMove component) and then using a TQuery
component to summarize the data in the temporary table.

SQL: using the substring function

Question

Answer

The SQL function SUBSTRING can be used in Delphi applications that include
local SQL queries, but is not supported for InterBase (IB) or the Local
InterBase Server (LIBS) tables. What follows is the syntax for the
SUBSTRING function, examples of its use in local SQL queries, and an
alternative that will return the same results for IB/LIBS tables.

The syntax for the SUBSTRING function is:

  SUBSTRING( FROM  [, FOR ])
  
Where:

   is the name of the column in the table from which the sub-
  string is to be extracted.
  
   is the point in the column value from which the sub-string to
  be extracted will start.
  
   is the length of the sub-string to be extracted.
  
Using these values, the use of the SUBSTRING function below would return
the second, third, and fourth characters from a column named COMPANY:

  SUBSTRING(COMPANY FROM 2 FOR 3)
  
The SUBSTRING function can be used either in the field list for a SELECT
query or in the WHERE clause of a query to allow for comparing a value
with a specific sub-set of a column. The SUBSTRING function can only be
used with String type columns (the CHAR type in SQL parlance). Here is an
example of the SUBSTRING function used in a columns list in a SELECT
query (using the sample Paradox table CUSTOMER.DB):

  SELECT (SUBSTRING(C."COMPANY" FROM 1 FOR 3)) AS SS
  FROM "CUSTOMER.DB" C

This SQL query extracts the first three characters from the COMPANY
column, returning them as the calculated column named SS. Now, an example
of the SUBSTRING function used in the WHERE clause of an SQL query (using
the same sample table):

  SELECT C."COMPANY"
  FROM "CUSTOMER.DB" C
  WHERE SUBSTRING(C."COMPANY" FROM 2 FOR 2) = "an"
  
This query returns all rows from the table where the second and third
characters in the COMPANY column are "ar".

As the SUBSTRING function is not supported at all by IB or LIBS databases,
it is not possible to have a sub-string operation in the column list of
a query (exception: IB can do sub-strings via User-Defined Functions).
But through use of the LIKE operator and the accompanying character
substitution marker, it is possible to effect a sub-string in a WHERE
clause. For example, using the sample table EMPLOYEE (in the EMPLOYEE.GDB
database):

  SELECT LAST_NAME, FIRST_NAME
  FROM EMPLOYEE
  WHERE LAST_NAME LIKE "_an%"
  
This SQL query would return all rows in the table where the second and
third characters of the LAST_NAME column are "an", similar to the
previous example for the Paradox table. While IB and LIBS databases
would require this method for performing sub-string comparisons in the
WHERE clause of a query and cannot use the SUBSTRING function, Paradox and
dBASE tables (i.e., local SQL) can use either method.