TSQLQuery with parameter error
lazarus / 16-Databases

----------------------------------------------------------------------
Sulhan Setiawan · 2012-04-10 19:19

I make a query with parameters. I set ParseSQL to false, and fill all
parameters with their apropriate value.
When I set the Active property to true, there is no problem. But, when
I run it, I always get this error message:

Parameter 0 is of type Unknown, which not supported yet.
I use Lazarus 0.9.30, fpc version: 2.4.2.

----------------------------------------------------------------------
Lacak2 · 2012-04-11 05:48

Can you please post demo project, which demonstrates this error?
My first guess is that, parameter type for first parameter was not
set?

BTW: FPC 2.4.2 is very old version, so may be that in mean-time this
error was fixed.

----------------------------------------------------------------------
Sulhan Setiawan · 2012-04-11 07:37

this is from my lfm file:

  object ADOInvoiceMaster: TSQLQuery
    IndexName = 'DEFAULT_ORDER'
    Active = True
    Database = ODBCConnection1
    Transaction = SQLTransaction1
    ReadOnly = False
    SQL.Strings = (
      'select * from Invoice '
      'WHERE (DateTime Between :DS And (:DE)+1)'
      'and(CustId Between :CS and :CE)'
      'ORDER BY DateTime DESC'
    )
    Params = <   
      item
        DataType = ftDate
        Name = 'DS'
        ParamType = ptInput
      end   
      item
        DataType = ftDate
        Name = 'DE'
        ParamType = ptInput
      end   
      item
        DataType = ftDate
        Name = 'CS'
        ParamType = ptInput
      end   
      item
        DataType = ftDate
        Name = 'CE'
        ParamType = ptInput
      end>
    UsePrimaryKeyAsKey = False
    ParseSQL = False
    left = 152
    top = 24
  end

----------------------------------------------------------------------
Lacak2 · 2012-04-11 08:39

You must upgrade your Lazarus/FPC.
For TODBCConnection there was added support for many parameter types
later.

----------------------------------------------------------------------
Sulhan Setiawan · 2012-04-11 11:42

I've upgraded to 0.9.30.4 fpc 2.6.0, but the problem still remains

----------------------------------------------------------------------
BigChimp · 2012-04-11 12:15

Just some random thought: perhaps this:
Code: [Select]'and(CustId Between :CS and :CE)'
needs a space:
Code: [Select]'and (CustId Between :CS and :CE)'

Furthermore, if you can post a simple demo project as Lacak2
indicated, it's easier to check ourselves. If there is a bug in
FPC/Lazarus, that project can also serve as a demo project..

Regards,
BigChimp

----------------------------------------------------------------------
Sulhan Setiawan · 2012-04-11 13:55

I've added the space, the problem remains. But if I set the active
property to false, then I activate it throug a button like this:

procedure TForm1.Button3Click(Sender: TObject);
begin
  with DataModule1.ADOInvoiceMaster do
  begin
    with Params do
    begin
      with ParamByName('DS') do
      begin
        DataType := ftDate;
        ParamType:= ptInput;
        Value:=0;
      end;
      with ParamByName('DE') do
      begin
        DataType := ftDate;
        ParamType:= ptInput;
        Value:=LastDate;
      end;
      with ParamByName('CS') do
      begin
        DataType := ftInteger;
        ParamType:= ptInput;
        Value:=0;
      end;
      with ParamByName('CE') do
      begin
        DataType := ftInteger;
        ParamType:= ptInput;
        Value:=200;
      end;
    end;
    Active:=True;
  end;
end;           

then the table can be opened without error.

I think, the problem is because TSQLQuey fails to read it params state
from lfm.
I try to place that procedue inside the OnCreate of DataModule1, and
it also works.