Board index » delphi » Re: SQL problem Paradox
Bill Todd
Delphi Developer |
Bill Todd
Delphi Developer |
Re: SQL problem Paradox2006-11-24 10:50:16 PM delphi103 You cannot use a blob column in the WHERE clause. -- Bill Todd (TeamB) |
Glenn Greatwood
Delphi Developer |
2006-11-24 10:50:43 PM
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 . |
Wayne Niddery [TeamB]
Delphi Developer |
2006-11-24 11:42:43 PM
Re: SQL problem Paradox
Glenn Greatwood writes:
Quote
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 |
Glenn Greatwood
Delphi Developer |
2006-11-25 02:38:32 PM
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 QuoteGlenn Greatwood writes: |
Wayne Niddery [TeamB]
Delphi Developer |
2006-11-25 11:01:21 PM
Re: SQL problem Paradox
Glenn Greatwood writes:
Quote
-- Wayne Niddery - Winwright, Inc (www.winwright.ca) "It is error alone which needs the support of government. Truth can stand by itself." - Thomas Jefferson |
Support@Key-Data
Delphi Developer |
2006-11-26 02:36:03 PM
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 QuoteGlenn Greatwood writes: |
Wayne Niddery [TeamB]
Delphi Developer |
2006-11-27 12:17:41 AM
Re: SQL problem Paradox
Support@Key-Data writes:
Quote
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 |
Support@Key-Data
Delphi Developer |
2006-11-27 01:08:45 AM
Re: SQL problem Paradox
Thanks again Wayne
Glenn "Wayne Niddery [TeamB]" <XXXX@XXXXX.COM>writes QuoteSupport@Key-Data writes: |
Glenn Greatwood
Delphi Developer |
2006-11-28 12:46:00 AM
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 QuoteThanks again Wayne |
Wayne Niddery [TeamB]
Delphi Developer |
2006-11-29 03:41:29 AM
Re: SQL problem Paradox
Glenn Greatwood writes:
Quote
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. |
Glenn Greatwood
Delphi Developer |
2006-11-29 10:07:27 PM
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 QuoteGlenn Greatwood writes: |