mORMot Open Source

firstfriday

Member

Registered: 2015-07-21

Posts: 26

MySQL Wrong fetchtype with FORWARD ONLY cursor

I would like to use the TQuery wrapper with mySQL but I failed because of this error:

TODBCStatement - TODBCLib error: [HY106] [MySQL][ODBC 5.3(w) Driver][mysqld-5.5.5-10.1.21-MariaDB]Wrong fetchtype with FORWARD ONLY cursor (537)

Reproduction can be easily done by this code
TQuery uses this: while I.Step(true)

  Props :=TODBCConnectionProperties.Create('','DRIVER=MySQL ODBC 5.3 Unicode Driver;SERVER=localhost;DATABASE=testdb;USER=root;PASSWORD=;Port=3306','','');
  I:=Props.Execute('select * from table',[]);
  while I.Step(true) do // < ERROR but while I.Step(false) works!
  begin

  end;

igors233

Member

Registered: 2012-09-10

Posts: 241

Re: MySQL Wrong fetchtype with FORWARD ONLY cursor

> MySQL Wrong fetchtype with FORWARD ONLY cursor
> Reproduction can be easily done by this code
> TQuery uses this: while I.Step(true)

Basically you're asking here go to first record again and again...
Since you have a one way cursor/statement/ResultSet that's impossible and you get an exception.

You should simply write
while i.Step do
begin
  // some processing
end;

Last edited by igors233 (2017-05-04 20:08:39)

firstfriday

Member

Registered: 2015-07-21

Posts: 26

Re: MySQL Wrong fetchtype with FORWARD ONLY cursor

Yes...but my question is:
I use the wrapper TQuery of mormot and unfortunatly inside this function it uses

while i.Step(true) do

I use this TQuery with mssql and it works here but not with mySQL.

igors233

Member

Registered: 2012-09-10

Posts: 241

Re: MySQL Wrong fetchtype with FORWARD ONLY cursor

Yes...but my question is:
I use the wrapper TQuery of mormot and unfortunatly inside this function it uses

while i.Step(true) do

I use this TQuery with mssql and it works here but not with mySQL.

Are you talking about TQyery from SynDB unit? I've searched whole unit and there's nowhere while i.Step(True), do you have the latests nightly build?
Otherwise if that's your code, simply change it to
a)
i.Step(True);
repeat
  // some processing
until not i.Step;

b)
while i.Step do
begin
  // Some processing
end;

firstfriday

Member

Registered: 2015-07-21

Posts: 26

Re: MySQL Wrong fetchtype with FORWARD ONLY cursor

Thank you for your fast response. I have the latest version and here you see "first" and the end. this causes the error in mySQL.

unit SynDB;

procedure TQuery.Open;
var i, h: integer;
    added: boolean;
    ColumnName: string;
begin
  if fResultCount>0 then
    Close;
  Execute(true);
  for i := 0 to fPrepared.ColumnCount-1 do begin
    ColumnName := UTF8ToString(fPrepared.ColumnName(i));
    h := fResult.FindHashedForAdding(ColumnName,added);
    if not added then
      raise ESQLQueryException.CreateUTF8('Duplicated column name "%"',[ColumnName]);
    with fResults[h] do begin
      fQuery := self;
      fRowIndex := 0;
      fColumnIndex := i;
      fName := ColumnName;
    end;
  end;
  assert(fResultCount=fPrepared.ColumnCount);
  // always read the first row
  First;
end;

I wonder if I can change somehow the cursor type.

igors233

Member

Registered: 2012-09-10

Posts: 241

Re: MySQL Wrong fetchtype with FORWARD ONLY cursor

> Thank you for your fast response. I have the latest version and here you see "first" and the end. this causes the error in mySQL.

Inside First there's this call:   if fPrepared.Step(true), I think it should work when called for the first time like:

var
  qry: TQuery
begin
  qry := // Get it with your MySQL connection
  qry.First; // This should work
  qry.First; // This should fail
end;

Anyway can you show a complete code where you create TQuery and how you use it.

> I wonder if I can change somehow the cursor type.

I don't know about ODBC connection you're using in Zeos type, unidirection resultset is default one and I think there's no way to change it unless you change source code yourself. Take a look at https://synopse.info/forum/viewtopic.php?id=3526, I had a similar issue, try to see if you have somewhere option to change ResultSet to rtScrollInsensitive which would give bidirectional cursor.
I think that some option to change that would be good but ab haven't commented anything about it, perhaps if more people show interest for it it may be implemented.

Last edited by igors233 (2017-05-05 16:16:02)

firstfriday

Member

Registered: 2015-07-21

Posts: 26

Re: MySQL Wrong fetchtype with FORWARD ONLY cursor

The full code is this:

procedure TMainForm.Button1Click(Sender: TObject);
var
  Props :TODBCConnectionProperties;
  Q: TQuery;
begin
  Props :=TODBCConnectionProperties.Create('','DRIVER=MySQL ODBC 5.3 Unicode Driver;SERVER=localhost;DATABASE=db1;USER=root;PASSWORD=;Port=3306','','');
  Q := TQuery.Create(Props.NewConnection);
  try
    Q.SQL.Clear; // optional
    Q.SQL.Add('select * from table1);
    Q.Open;
    while not Q.Eof do
    begin
      Q.Next;
    end;
    Q.Close;    // optional
  finally
    Q.Free;
  end;
end;

Maybe TQuery is just not meant to use with mySQL. Of cource I could use the Step Command but for legacy purposes this is not an easy job. Or I can override this origin TQuery.

igors233

Member

Registered: 2012-09-10

Posts: 241

Re: MySQL Wrong fetchtype with FORWARD ONLY cursor

> Maybe TQuery is just not meant to use with mySQL.

If it's not stated in docs it's bug.

> Of cource I could use the Step Command but for legacy purposes this is not an easy job. Or
> I can override this origin TQuery.

And error happens in q.Open?
If this is reprodubicible every time I would report it to the bugtracker (with code example) and hopefully that would be fixed at some point. In the meantime for your to work normally I would suggest to copy whole SynDB to your project folder and make a change in there.