Mega Code Archive
What type is it
<![CDATA[
The Perpetual Newbie - Log Entry #8.1
This article first appeared on http://www.undu.com
As you discover the joys of allowing users to do ad hoc queries, you also discover that reacting to their choices after the fact isn't always a joy.
If you don't know WHAT columns are going to be included in the query, you have two ways of coping with the columns after the fact. The first is brute force, comparing the actual field names with a list of all possible choices and reacting accordingly. Alternatively, you can get the field type information from Delphi and write a tight little routine.
BUT there's one catch. You'll be using some internal Delphi constants like ftSmallInt, ftBoolean and ftString. So, naturally, when you want to get the information, you will probably ask for ObjectName.fieldType and be puzzled when the compiler burps and refuses to continue. That's because the property you SHOULD be asking for is DataType. Yes, I know it's confusing as to why the constants couldn't have the same DT as the calling property, but of such anomalies are articles written here on the web.
You can do some things with this code once you know the type of field any column is. You can total up a query result and do some statistical analysis. For example, set up a grid and a query connected to it. Allow the query to be some sort of ad hoc query by whatever means you want. Have a memo field sitting under the grid. Loop through the fields, counting incidents of string fields, totaling numeric fields and producing True/False total stats from boolean fields. Include each column's summary as a line in the memo.
My own use was involved in creating a specialized spreadsheet in Excel, using Axolot Data's XLSReadWrite component (available at http//www.axolot.com/components/index.htm). Being able to differentiate PO numbers that were occasionally ALL numbers as a string instead, allowed for proper left/right justification of the results. The gist of the function that does that is listed below
var
Row,Col integer;
s string;
begin
// only continue if query Q is active and filled
if ((Q.active) and (Q.recordCount > 0))
then begin
XLSW.Filename = 'C\data\' + eXLS.Text + '.xls';
xlsW.OpenFile;
Q.first;
Row = 1;
while not Q.eof do begin
// write the column headers with the field name
for Col = 0 to (Q.fieldCount - 1) do
xlsW.WriteString(Col,0,2,uppercase(q.fields[col].fieldname));
// loop through the columns and export each cell to XLSReadWrite
for Col = 0 to (Q.fieldCount - 1) do begin
s = q.fields[Col].asString;
// use the field's datatype property, NOT fieldType!!!!!
// since these are members of an enumarated set, you can use a Case...
case q.fields[Col].dataType of
ftSmallInt xlsW.WriteNumber(Col,Row,1,StrToFloat(s));
ftWord xlsW.WriteNumber(Col,Row,1,StrToFloat(s));
ftFloat xlsW.WriteNumber(Col,Row,1,StrToFloat(s));
ftBCD xlsW.WriteNumber(Col,Row,1,StrToFloat(s));
ftInteger xlsW.WriteNumber(Col,Row,1,StrToFloat(s));
ftLargeInt xlsW.WriteNumber(Col,Row,1,StrToFloat(s));
ftBoolean if uppercase(S) = 'TRUE' // true checkbox
then xlsW.WriteBool(Col,Row,3,true)
else if uppercase(S) = 'FALSE' // false
then xlsW.WriteBool(Col,Row,3,false)
else xlsW.WriteString(Col,Row,2,'');
// everything else is a string, including dates
else xlsW.WriteString(Col,Row,2,S);
end;
end;
q.next;
Row = Row + 1;
end;
xlsW.closeFile;
end
else showDlgOops('No data to export!');
end;
As I know the riddle of identifying field type is a popular question in the various Delphi newsgroups, I hope this will steer you to a solution to your conundrum. And I hope it will encourage you to give your users ad hoc querying capability. They will LOVE it!
Gary Mugford
Idea Mechanic
Bramalea ON Canada
]]>