Board index » delphi » Re: SQL problem Paradox

Re: SQL problem Paradox


2006-11-24 10:50:16 PM
delphi103
You cannot use a blob column in the WHERE clause.
--
Bill Todd (TeamB)
 
 

Re: SQL problem Paradox

Hi All
I am having trouble with the following query, DiaryPrint is a BDE TQuery.
The query fails with a 'Type Mismatch in Expression' error...the part that
is causing the issue is the ResourceId field that is a BLOB type field...If
I alter the field type to Integer it works fine (but I need to keep it
BLOB!).....
Any help much appreciated
Thanks
procedure TOfficeDiary.PrintAppListClick(Sender: TObject);
var
D1 : Variant;
D2 : Variant;
TheUser : Variant;
begin
TheUser:=Scheduler.selresource.ResourceID;
With DiaryPrint do
begin
Close;
SQL.CLEAR;
SQL.ADD('Select * from cxSchedulerTable');
SQL.ADD('WHERE');
SQL.ADD('Start>=:Date1');
SQL.Add('AND');
SQL.ADD('Start<=:Date2');
SQL.Add('AND');
SQL.Add('Viewing is null');
SQL.Add('AND');
SQL.Add('ResourceID=:TheNegotiator');
SQL.Add('Order By Start');
D1:=DateToStr(Scheduler.DateNavigator.Date);
D1:=D1+' 00:00:00';
D2:=DateToStr(Scheduler.DateNavigator.Date);
D2:=D2+' 23:59:59';
Params[0].DataType:=ftDateTime;
Params[1].DataType:=ftDateTime;
Params[2].DataType:=ftInteger;
Params[0].value:=StrToDateTime(D1);
Params[1].value:=StrToDateTime(D2);
Params[2].value:=TheUser;
end;
Showmessage('This will be the appointment diary for user number:
'+IntToStr(TheUser));
ppReport2.Print;
end;
--
Kind Regards
Glenn Greatwood
Key-Data Systems
www.key-data.co.uk
.
 

Re: SQL problem Paradox

Glenn Greatwood writes:
Quote

I am having trouble with the following query, DiaryPrint is a BDE
TQuery. The query fails with a 'Type Mismatch in Expression'
error...the part that is causing the issue is the ResourceId field
that is a BLOB type field...If I alter the field type to Integer it
works fine (but I need to keep it BLOB!).....

procedure TOfficeDiary.PrintAppListClick(Sender: TObject);
var
D1 : Variant;
D2 : Variant;
TheUser : Variant;
begin
TheUser:=Scheduler.selresource.ResourceID;
With DiaryPrint do
begin
Close;
SQL.CLEAR;
SQL.ADD('Select * from cxSchedulerTable');
SQL.ADD('WHERE');
SQL.ADD('Start>=:Date1');
SQL.Add('AND');
SQL.ADD('Start<=:Date2');
SQL.Add('AND');
SQL.Add('Viewing is null');
SQL.Add('AND');
SQL.Add('ResourceID=:TheNegotiator');
SQL.Add('Order By Start');
D1:=DateToStr(Scheduler.DateNavigator.Date);
D1:=D1+' 00:00:00';
D2:=DateToStr(Scheduler.DateNavigator.Date);
D2:=D2+' 23:59:59';
Params[0].DataType:=ftDateTime;
Params[1].DataType:=ftDateTime;
Params[2].DataType:=ftInteger;
Params[0].value:=StrToDateTime(D1);
Params[1].value:=StrToDateTime(D2);
Params[2].value:=TheUser;
end;
Why does something like a user resourceid need to be a blob? While Paradox
SQL will allow you to search a text blob, it does not seem to be able to
handle it as a query, you would have to actually format the condition
directly, e.g:
SQL.Add('ResourceID=' + QuotedStr(TheUser));
But I think you'd be better off putting this into a varchar field instead,
then you can use parameters with it.
Some other things to improve you handling of parameters (and fields):
1. Stop using variants, they are slow and will cause you grief, especially
when handling dates
2. Avoid manipulating dates in string format because formats differ from
place to place and on computer settings, as well as being inefficient.
You should not need to assign the parameter types in code, though it does no
harm. Thus the entire code to assign the parameters is for the two dates
(since the blob has to be formatted directly as above):
Params[0].AsDateTime:= Truncate(Scheduler.DateNavigator.Date);
Params[1].AsDateTime:= Truncate(Scheduler.DateNavigator.Date) +
EncodeTime(23, 59, 59, 999);
If you change the resourceid to a varchar then likewise this can be assigned
as a parameter directly:
Params[2].AsString:= Scheduler.selresource.ResourceID;
--
Wayne Niddery - Winwright, Inc (www.winwright.ca)
"The purpose of morality is to teach you, not to suffer and die, but to
enjoy yourself and live." - Ayn Rand
 

Re: SQL problem Paradox

Thanks chaps
The field had to be a BLOB apparently if I wanted to share the ResourceID
with several 'users'. On reflection I will not need this functionality as I
can't query on a BLOB field I will alter the field to an Integer type.
Wayne, thank you for your suggestions, I will implement alterations
accordingly.
Glenn
"Wayne Niddery [TeamB]" <XXXX@XXXXX.COM>writes
Quote
Glenn Greatwood writes:
>
>I am having trouble with the following query, DiaryPrint is a BDE
>TQuery. The query fails with a 'Type Mismatch in Expression'
>error...the part that is causing the issue is the ResourceId field
>that is a BLOB type field...If I alter the field type to Integer it
>works fine (but I need to keep it BLOB!).....
>
>procedure TOfficeDiary.PrintAppListClick(Sender: TObject);
>var
>D1 : Variant;
>D2 : Variant;
>TheUser : Variant;
>begin
>TheUser:=Scheduler.selresource.ResourceID;
>With DiaryPrint do
>begin
>Close;
>SQL.CLEAR;
>SQL.ADD('Select * from cxSchedulerTable');
>SQL.ADD('WHERE');
>SQL.ADD('Start>=:Date1');
>SQL.Add('AND');
>SQL.ADD('Start<=:Date2');
>SQL.Add('AND');
>SQL.Add('Viewing is null');
>SQL.Add('AND');
>SQL.Add('ResourceID=:TheNegotiator');
>SQL.Add('Order By Start');
>D1:=DateToStr(Scheduler.DateNavigator.Date);
>D1:=D1+' 00:00:00';
>D2:=DateToStr(Scheduler.DateNavigator.Date);
>D2:=D2+' 23:59:59';
>Params[0].DataType:=ftDateTime;
>Params[1].DataType:=ftDateTime;
>Params[2].DataType:=ftInteger;
>Params[0].value:=StrToDateTime(D1);
>Params[1].value:=StrToDateTime(D2);
>Params[2].value:=TheUser;
>end;

Why does something like a user resourceid need to be a blob? While Paradox
SQL will allow you to search a text blob, it does not seem to be able to
handle it as a query, you would have to actually format the condition
directly, e.g:

SQL.Add('ResourceID=' + QuotedStr(TheUser));

But I think you'd be better off putting this into a varchar field instead,
then you can use parameters with it.


Some other things to improve you handling of parameters (and fields):

1. Stop using variants, they are slow and will cause you grief, especially
when handling dates
2. Avoid manipulating dates in string format because formats differ from
place to place and on computer settings, as well as being inefficient.

You should not need to assign the parameter types in code, though it does
no harm. Thus the entire code to assign the parameters is for the two
dates (since the blob has to be formatted directly as above):

Params[0].AsDateTime:= Truncate(Scheduler.DateNavigator.Date);
Params[1].AsDateTime:= Truncate(Scheduler.DateNavigator.Date) +
EncodeTime(23, 59, 59, 999);

If you change the resourceid to a varchar then likewise this can be
assigned as a parameter directly:

Params[2].AsString:= Scheduler.selresource.ResourceID;

--
Wayne Niddery - Winwright, Inc (www.winwright.ca)
"The purpose of morality is to teach you, not to suffer and die, but to
enjoy yourself and live." - Ayn Rand

 

Re: SQL problem Paradox

Glenn Greatwood writes:
Quote

The field had to be a BLOB apparently if I wanted to share the
ResourceID with several 'users'.
Could you elaborate on this a little? I am curious what that the actual goal
is here, and expect that too can be solved or explained.
--
Wayne Niddery - Winwright, Inc (www.winwright.ca)
"It is error alone which needs the support of government. Truth can
stand by itself." - Thomas Jefferson
 

Re: SQL problem Paradox

Hi Wayne
I use use a third party scheduling component
(www.devexpress.com/Products/VCL/ExScheduler/) that is the basis of
an office/individual user diary for my application. It allows MS Outlook
look and functionality quite simply. The database they shipped as an example
application with their demo became my start database which is where the
field ResourceID of type BLOB came from. I understand that the sceduling
component allows sharing between recources (which I do not require) which is
why a BLOB is required (this bit I know not why), but I can use an integer
type field quite happily for my application.
Cheers
Glenn
"Wayne Niddery [TeamB]" <XXXX@XXXXX.COM>writes
Quote
Glenn Greatwood writes:
>
>The field had to be a BLOB apparently if I wanted to share the
>ResourceID with several 'users'.

Could you elaborate on this a little? I am curious what that the actual
goal is here, and expect that too can be solved or explained.

--
Wayne Niddery - Winwright, Inc (www.winwright.ca)
"It is error alone which needs the support of government. Truth can
stand by itself." - Thomas Jefferson

 

Re: SQL problem Paradox

Support@Key-Data writes:
Quote

I use use a third party scheduling component
(www.devexpress.com/Products/VCL/ExScheduler/) that is the
basis of an office/individual user diary for my application. It
allows MS Outlook look and functionality quite simply. The database
they shipped as an example application with their demo became my
start database which is where the field ResourceID of type BLOB came
from. I understand that the sceduling component allows sharing
between recources (which I do not require) which is why a BLOB is
required (this bit I know not why), but I can use an integer type
field quite happily for my application.
Ok, being that it is a *sample* application from them, it is acceptable for
it to take various shortcuts either for purposes of demonstration and/or
expedience, so no criticism is warranted on that basis. But I can assure you
such a link between blobs and sharing *only* applies to that particular
sample application, Blobs have *nothing* to do with whether, or how, data is
shared.
--
Wayne Niddery - Winwright, Inc (www.winwright.ca)
"Nurture your mind with great thoughts. To believe in the heroic makes
heroes." ?Benjamin Disraeli
 

Re: SQL problem Paradox

Thanks again Wayne
Glenn
"Wayne Niddery [TeamB]" <XXXX@XXXXX.COM>writes
Quote
Support@Key-Data writes:
>
>I use use a third party scheduling component
>(www.devexpress.com/Products/VCL/ExScheduler/) that is the
>basis of an office/individual user diary for my application. It
>allows MS Outlook look and functionality quite simply. The database
>they shipped as an example application with their demo became my
>start database which is where the field ResourceID of type BLOB came
>from. I understand that the sceduling component allows sharing
>between recources (which I do not require) which is why a BLOB is
>required (this bit I know not why), but I can use an integer type
>field quite happily for my application.

Ok, being that it is a *sample* application from them, it is acceptable for
it to take various shortcuts either for purposes of demonstration and/or
expedience, so no criticism is warranted on that basis. But I can assure
you such a link between blobs and sharing *only* applies to that
particular sample application, Blobs have *nothing* to do with whether, or
how, data is shared.

--
Wayne Niddery - Winwright, Inc (www.winwright.ca)
"Nurture your mind with great thoughts. To believe in the heroic makes
heroes." - Benjamin Disraeli

 

Re: SQL problem Paradox

Hi again Wayne
I have altered the code as you suggested to loose strings and variants and
have a strange thing happening?
Params[0].AsDateTime:= Truncate(Scheduler.DateNavigator.Date);
Params[1].AsDateTime:= Truncate(Scheduler.DateNavigator.Date) +
EncodeTime(23, 59, 59, 999);
When I first updated the code I was told it couldn't compile due to
'incomatible types'...I closed and reopened and it compiled fine....tried
again and got the error message again, took out the word 'Truncate' and it
worked fine, put 'truncate' back in again and it compiled? notw it won't
compile with 'truncate' in the statement?
Any idea whats going on here, I have never seen inconsistant compilation
issues in eight years!
I'm using Delphi 2005
Cheers
Glenn
"Support@Key-Data" <XXXX@XXXXX.COM>writes
Quote
Thanks again Wayne

Glenn
"Wayne Niddery [TeamB]" <XXXX@XXXXX.COM>writes
news:4569be10$XXXX@XXXXX.COM...
>Support@Key-Data writes:
>>
>>I use use a third party scheduling component
>>(www.devexpress.com/Products/VCL/ExScheduler/) that is the
>>basis of an office/individual user diary for my application. It
>>allows MS Outlook look and functionality quite simply. The database
>>they shipped as an example application with their demo became my
>>start database which is where the field ResourceID of type BLOB came
>>from. I understand that the sceduling component allows sharing
>>between recources (which I do not require) which is why a BLOB is
>>required (this bit I know not why), but I can use an integer type
>>field quite happily for my application.
>
>Ok, being that it is a *sample* application from them, it is acceptable
>for it to take various shortcuts either for purposes of demonstration
>and/or expedience, so no criticism is warranted on that basis. But I can
>assure you such a link between blobs and sharing *only* applies to that
>particular sample application, Blobs have *nothing* to do with whether,
>or how, data is shared.
>
>--
>Wayne Niddery - Winwright, Inc (www.winwright.ca)
>"Nurture your mind with great thoughts. To believe in the heroic makes
>heroes." - Benjamin Disraeli
>


 

Re: SQL problem Paradox

Glenn Greatwood writes:
Quote

When I first updated the code I was told it couldn't compile due to
'incomatible types'...I closed and reopened and it compiled
fine....tried again and got the error message again, took out the
word 'Truncate' and it worked fine, put 'truncate' back in again and
it compiled? notw it won't compile with 'truncate' in the statement?

Any idea whats going on here, I have never seen inconsistant compilation
issues in eight years!
I'm using Delphi 2005
That's definitely very strange, I have never seen it do that before either.
However, it should consistently give you that error in fact, since what I
should've given you is Trunc(datevar), not Truncate(datevar). Both are
functions, but Truncate is a file operation, not a numeric one. See if that
"corrects" the problem.
--
Wayne Niddery - Winwright, Inc (www.winwright.ca)
"True peace is not the absence of tension, but the presence of
justice." - Martin Luther King, Jr.
 

Re: SQL problem Paradox

Hi Wayne
My code now reads:
procedure TOfficeDiary.Print1Click(Sender: TObject);
begin
With DiaryPrint do
begin
Close;
SQL.CLEAR;
SQL.ADD('Select * from cxSchedulerTable');
SQL.ADD('WHERE');
SQL.ADD('Start>=:Date1');
SQL.Add('AND');
SQL.ADD('Start<=:Date2');
SQL.Add('AND');
SQL.Add('Viewing=True');
SQL.Add('Order By Start');
Params[0].DataType:=ftDateTime;
Params[1].DataType:=ftDateTime;
Params[0].AsDateTime:= Trunc(Scheduler.DateNavigator.Date);
Params[1].AsDateTime:= Trunc(Scheduler.DateNavigator.Date) +
EncodeTime(23, 59, 59, 999);
end;
ppReport1.Print;
end;
Looks a lot neater and now compiles nicely. Many thanks.
Regards
Glenn
"Wayne Niddery [TeamB]" <XXXX@XXXXX.COM>writes
Quote
Glenn Greatwood writes:
>
>When I first updated the code I was told it couldn't compile due to
>'incomatible types'...I closed and reopened and it compiled
>fine....tried again and got the error message again, took out the
>word 'Truncate' and it worked fine, put 'truncate' back in again and
>it compiled? notw it won't compile with 'truncate' in the statement?
>
>Any idea whats going on here, I have never seen inconsistant compilation
>issues in eight years!
>I'm using Delphi 2005

That's definitely very strange, I have never seen it do that before either.

However, it should consistently give you that error in fact, since what I
should've given you is Trunc(datevar), not Truncate(datevar). Both are
functions, but Truncate is a file operation, not a numeric one. See if
that "corrects" the problem.

--
Wayne Niddery - Winwright, Inc (www.winwright.ca)
"True peace is not the absence of tension, but the presence of
justice." - Martin Luther King, Jr.