Re: [DUG]: SQL and Parameters

[Mark Howard]

Neven

See below.

 

> Things i'd suggest
>
> 1/ Try calling prepare before setting the param
>
Have tried putting the "Prepare" in several places - all with no effect

>
> 2/ Check that the param exists! and that it is of the correct type
>
> When you consider what the DBE does it assumes that the parameter is of the
> same type
> as the field it is 'expressed' against, As is all aumptions this is liable
> to error
>
>  ie.
>      Close;
>      SQL.LoadFromFile('DocNos.SQL');
>      Prepare;
>      try
>        ParamByName('SaveDate').AsDate := CurrentDate;
>        try
>         Open;
>       except
>          // unable to exec query
>       end
>      except
>          // unable to set param
>      end
>      MainForm.Memo.Lines.Add('Done');
>
In doing the above, the ParamByName works OK but the Open (in fact ExecSQL, nothing is returned from the SQL) fails.

 

I feel, as James suggested, that it is something to do with Creating the Parameters and I have been able to find virtually nothing in the documentation on this that makes any sense to me, although I DID find this in TParams help

Note: Parameters for TQuery components are only created by specifying parameters in the SQL statement. The TParam objects in a TQuery component's Params property are created automatically when parameter tokens are added to the SQL statement.

which tends to suggest that this ISN'T the problem.  Oh well, wasted a day of my time and a good part of the List's time on this yesterday, so think I'll just do the whole thing by building the SQL code in the programme.

 

Thanks for everyone's input yesterday

 

Mark

 >
> ----- Original Message -----
> From: James Low <[EMAIL PROTECTED]>
> To: Multiple recipients of list delphi <[EMAIL PROTECTED]>
> Sent: Wednesday, 30 May 2001 16:11
> Subject: RE: [DUG]: SQL and Parameters
>
>
> > Maybe it cannot find an object because the query has NO parameters (I
> cannot
> > see where your code creates one). So maybe you need to add (using
> > CreateParam or otherwise)  a Param Object to the datsets Params (with the
> > name "SaveDate", type Output (I think), ftdate) - before you call prepare.
> >
> > -----Original Message-----
> > From: Mark Howard [mailto:[EMAIL PROTECTED]]
> > Sent: 30 May 2001 15:41
> > To: Multiple recipients of list delphi
> > Subject: Re: [DUG]: SQL and Parameters
> >
> >
> > Mike
> > Have tried this change, now.  No better.  I assume the question about
> Short
> > Date Format is only relevant while I am using the DateToStr format?
> > I think there must be something quitye fundamental, that I have not passed
> > on to you guys.
> > I assume that I can use a vanilla TQuery with no properties set; and that
> > setting the ParamByName is ALL I need to.  (I have also tried
> > Params[0].AsDate := CurrentDate.)
> > Is there some other fundamental aspect that I may not be aware of?
> > Mark
> >
> >
> > ----- Original Message -----
> > From: Mike  <mailto:[EMAIL PROTECTED]> Osborne
> > To: Multiple  <mailto:[EMAIL PROTECTED]> recipients of list delphi
> > Sent: Wednesday, May 30, 2001 3:22 PM
> > Subject: Re: [DUG]: SQL and Parameters
> >
> > Mark
> > Have you tried -
> > ParamByName('SaveDate').AsDate := CurrentDate;
> > as opposed to
> > ParamByName('SaveDate').AsString := DateToStr(CurrentDate);
> >
> > What is your Short Date Format - dd/mm/yy or mm/dd/yy?
> > DateToStr may not be giving you a valid date for SQL
> > Why you would get "could not find object", I don't know.
> >
> > HTH
> > Mike
> >
> >
> > ----- Original Message -----
> > From: Mark  <mailto:[EMAIL PROTECTED]> Howard
> > To: Multiple  <mailto:[EMAIL PROTECTED]> recipients of list delphi
> > Sent: Wednesday, May 30, 2001 10:03 AM
> > Subject: [DUG]: SQL and Parameters
> >
> >
> > Hi
> >
> > Using Paradox.
> >
> > When I run the following SQL code (DocNos.Sql) using the LoadFromFile
> > method, things work fine;
> >
> > Delete from DocketNos
> > Where DocketNo in (
> > Select DocketNo from Dockets
> > Where ForCode not in (
> > Select distinct forcode from dockets
> > where docketdate > '03/31/2001'))
> >
> > But when I try to parameterise it as follows, and with the final line of
> the
> > Sql file changed to :
> >
> > where docketdate > :SaveDate))
> >
> > then I get an error message "Could not find object"
> >
> >   procedure RunPQuery(SQLFile: string);
> >   begin
> >     with RSQuery do
> >     begin
> >       Close;
> >       Unprepare;
> >       SQL.LoadFromFile('DocNos.SQL');
> >       ParamByName('SaveDate').AsString := DateToStr(CurrentDate);
> >       Prepare;
> >       Open;
> >       MainForm.Memo.Lines.Add('Done');
> >     end;
> >   end;
> >
> > Can any one see where I have gone wrong?
> >
> > TIA  Mark
> >
> >
>
> ---------------------------------------------------------------------------
>     New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED]
>                   Website: http://www.delphi.org.nz
> To UnSub, send email to: [EMAIL PROTECTED]
> with body of "unsubscribe delphi"
>