|
SQL
SQL- comand JOIN for more tables
Searching with SQL
Two or more commands in TQuery-SQL Property
DBGrid and TQuery
BDE problem. Using non live TQuery
Assigning a SQL count to a variable
Query gauge bar
Error creating cursor handle
SQL date field
Find a record in an SQL dataset
Getting a query's memo field as a string
Creating and using parameterized queries
SQL: embedded spaces in field/column names
SQL: Sorting on a calculated column
SQL: Summarizing a calculated column
SQL: using the substring function
SQL- comand JOIN for more tables
Question
I've been messing around with doing JOINS on different tables using
the query component. Joining two tables is no problem, but what if
one wants to join three or four tables?
Maybe the SQL to do this isn't supported by the Delphi/BDE...?
Answer
A:
It's really easy. Just use ReportSmith to do the job for you. Specify
the tables, set inclusion parameters etc, and then look at the
generated SQL code. Copy it into a query component and... It works!
Searching with SQL
Question
And using SQL, can I do a search for "John" and have it come up with the field "Lennon, John"?
Answer
A:
Assuming:
1. Your person table is defined along the lines of...
last_name char (n),
first_name char (n)
do...
select
last_name+', '+first_name
from
person
where
first_name='john'
2. Your person table is defined along the lines of...
person_name char (n) (eg. Lennon, John)
do...
select
person_name
from
person
where
person_name like '%John' <--- 'John' has to be at end of string, else use '%John%'
Two or more commands in TQuery-SQL Property
Question
Is there a way in local sql use more than one sql-command? I got an error if I want to use a second sql command in TQuery-component.
Answer
A:
I presume you are trying to attach a new query to an existing TQuery.
Query1.Sql.Clear;
Query1.Close;
Query1.Sql.Add('select * from "monitor.dbf" order by location,dept');
Query1.Open;
Query1.Refresh;
The trick is in closing your query before assigning a new one.
DBGrid and TQuery
Question
How can I update the detail dbgrid information if I change the selection of
the master dbgrid if both are using tquery as data access method?
Answer
A:
1. Place 2 TQueries on your form with 2 associated TDatasources (Query1 will
be your Master, Query2 will be your Detail)
2. Place 2 TDBGrids attached to the Datasources (you've probably done this
already)
3. Using sample data that comes with Delphi:
Query1.SQL := 'Select * from customer'
Query2.SQL := 'Select * from Orders where
Orders."CustNo" = :CustNo'
(these can be done at design time or run time)
4. In the Query2 properties, choose the Params property and set 'CustNo' to
string. 'CustNo' has been defined as a parameter because the ':' was used
in the SQL string.
5. MOST IMPORTANTLY: set Query2.Datasource to the datasource attached to
Query1.
Everytime a record changes in the Query1 dataset, Query2 is updated. Calling
the parameter 'CustNo' matches the actual field name in the Customer table.
P.S.: search help for 'dynamic SQL'
BDE problem. Using non live TQuery
Question
I am currently working on an app which uses SQL with joins.
According to the "DataBase Application Developper's Guide", such a TQuery
cannot return a Live Result Set and one "must use a separate TQuery to
construct an Update statement".
Problem: The Data aware Components connected to the main TQuery are all
ReadOnly. This is mainly a problem for the DBGrid, other components could
be managed manually.
Attempted Solution: (not elegant) Modify the VCL source for TQuery to
return CanModify=true and use another TQuery (as a matter of fact TTable)
to do the updating. But ..
Problem: The BDE uses a caching mecanism and relies on triggers for
updating its view when another posting to a table is made. Unfortunatly,
those triggers won't update a complex (non live) TQuery even if we call
TQuery.Refresh. The only "solution" seems to be to close and open the
Query, which is unacceptably slow !
Answer
A:
I had the same problem and that was the only way I found around it. I
suspect that the reason is that Query1.Refresh does nothing if it is
readonly as it does not expect changes. One way I used successfully
(asuming a single join) was two use 3 TQueries, two grids and an
update form. That way I could set requestlive to true. You have to
prevent the user from editting the grid yourself (if that's what you
want)
Assigning a SQL count to a variable
Question
I would like to query a table to find the number of matches to a
string. The table has a field containing titles of positions.
I would like to query the table with something like:
select count(*) from table
where field = "XXX"
and then be able to copy the result into a variable.
Can I do something like:
query.Close;
query.SQL.Clear;
query.SQL.Add('select count(*) from table where field = "XXX");
variable :=query.Open;
I know that the above is incorrect, I am just using it to give you
the idea of what I want to do. I presume that the SQL cursor returns
the result as as set of parameters, I just don't know how to get at
them.
Answer
A:
query.Close;
query.SQL.Clear;
query.SQL.Add('select count(*) from table where field = :XXX);
Query.ParamByName('XXX').AsString := value;
query.Open;
while Query.Eof <> True do
begin
SqlCount := Query.Fields[0].AsInteger;
Query.Next
end;
A:
Applies to
TTable, TQuery, TStoredProc components
Declaration
property RecordCount: Longint;
Description
Run-time and read only. The RecordCount property specifies the number of
records in the dataset. The number of records reported may depend on the
server and whether a range limitation is in effect.
Query gauge bar
Question
How can i draw a gauge bar during a tquery.open session? I know the record
count of the table (=100%), but how can i get the actual record, which tquery
is processing? how can i operate during running query?
Answer
A:
Not possible.
The idea behind the TQuery object is to have a SQL server process the
request in the background and take the local machine completely out of
the loop. The app is never supposed to know what the TQuery is doing,
so a gauge that measures progress would have to get its data from a
SQL server. Since most SQL servers dont publish this info, you are
probably out of luck on this one...
A:
If you're using Paradox or DBase I think you can use the
DBIRegisterCallback function
>From the DBE users manual:
Usage:
Callbacks are used when a client application needs clarification about a given
engine function before completing an operation or to return information to the
client. DBIRegisterCallback allows the client to instruct the database engine
about what further actions should be taken by the engine upon occurence of an
event.
I've never used it before, so I can't give you the details.
Error creating cursor handle
Question
I'm writing a very simple query that updates a field called CARD1 with
a string "Gold" based on a second field called GPA, both of which are
in a table named STUDENTS.DB. The SQL is
update students
set card1 = "Gold"
where gpa > 3.0;
It generates an error message ERROR CREATING CURSOR HANDLE.
Answer
You've to use ExecSql instead of Open. For example, if your query name is
UpdateStudent, when you want to update the STUDENT.DB you've to write this
code:
Begin
.....
UpdateStudent.ExecSql;
.....
End;
Your query is a Passtrough query that can't return a result set so it can't
be opened but must be 'EXECUTED'.
SQL date field
Question
I'm trying this again. I got absolutely no response last time.
Is it possible to do this in SQL, have a field with the name Date?
select * from PFMANUAL where Date = 31/11/95
Answer
A:
There are a lot of ways to do this:
1) If the date is costant, use:
WHERE Date = #31/11/95#
Depending upon your country settings, this could be #11/31/95#. Try both:
one of them works.
2) If the date is variable, you must use a parameter, for instance in this way:
WHERE Date = :MyDate
Then, after clicking ok, select the Query Params in the Object inspector,
click on the ellipsis button, and set MyDate as Date type.
A:
SELECT * from PFMANUAL WHERE PRMANUAL."DATE" = "31/11/95"
I found it after having had a similar problem and using DataBase Desktop to
buil a QBE statement which it then "translated" to SQL
Find a record in an SQL dataset
Question
Now, i want the user to be able to enter a letter or letters in an edit
box and have the record pointer jump to the first occurance of a record
whose index begins with that letter (partial find). Something like
this:
ModelQuery.FindNearest([SpeedEdit.Text]);
Although this works perfectly with TTables, I can't get it to work with
TQueries.
Answer
A:
In the edit boxes change event, do:
Query1.Close;
Query1.SQL.Clear;
Query1.SQL.Add('SELECT * FROM WHERE Like ''' +
SpeedEdit.Text + '*''');
Query1.Open;
All records that begin with whatever is entered into the edit box will be
returned.
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.
|
|
|