Multiple statements

[Lelelo1]

Multiple statements

When I run the following sql:

SELECT v.entity_id INTO @id FROM catalog_product_entity_varchar v JOIN catalog_product_entity p ON v.entity_id = p.entity_id WHERE v.attribute_id = '283' AND v.value = '889501092529' LIMIT 1;

SELECT v.value INTO @ean FROM catalog_product_entity_varchar v WHERE v.entity_id = @id AND v.attribute_id = '283' LIMIT 1;

(SELECT g.value INTO @image_front FROM catalog_product_entity_media_gallery g, catalog_product_entity_media_gallery_value gv WHERE g.entity_id IN (SELECT r.parent_id FROM catalog_product_relation r WHERE r.child_id = @id) AND g.value_id = gv.`value_id` AND (gv.position = '1') ORDER BY gv.position ASC) LIMIT 1;

(SELECT g.value INTO @image_back FROM catalog_product_entity_media_gallery g, catalog_product_entity_media_gallery_value gv WHERE g.entity_id IN (SELECT r.parent_id FROM catalog_product_relation r WHERE r.child_id = @id) AND g.value_id = gv.`value_id` AND (gv.position = '2') ORDER BY gv.position ASC) LIMIT 1;

SELECT @id, @ean, @image_front, @image_back 

...it failes.
It also happened to me with mysql1 driver pub package. As stated the code runs fine in phpMyAdmin (and returns one result set). The error code lead to SO posts about needing to specify delimiter.

When I adding it, it fails:

DELIMITER ; SELECT v.entity_id INTO @id FROM catalog_product_entity_varchar v JOIN catalog_product_entity p ON v.entity_id = p.entity_id WHERE v.attribute_id = '283' AND v.value = '889501092529' LIMIT 1 ; DELIMITER

While this code works fin everywhere:

SELECT v.entity_id INTO @id FROM catalog_product_entity_varchar v JOIN catalog_product_entity p ON v.entity_id = p.entity_id WHERE v.attribute_id = '283' AND v.value = '889501092529' LIMIT 1 ;

Is it supported to make more statements than one in the same conn.execute(sql) call? Also, any help understanding why it not seem to work in (2) Flutter mysql drivers but works in phpMyAdmin would be appreciated.

[Lelelo1]

I found the following: https://dev.mysql.com/doc/internals/en/multi-statement.html

[zim32]

As you can see multiple statements is still in the roadmap. Right now I am working on refactoring errors, this is more important and hard to implement with all this async stuff under the hood. So... Not now

[zim32]

Multiple statements is something that need to be supported and negotiated between client and server and also there some specific things in parsing protocol packets

[Lelelo1]

Which one is it, Multiple ResultSet?

[zim32]

CLIENT_MULTI_STATEMENTS

[zim32]

The question is how to handle multiple result sets on select statements. How to return multiple result sets

[zim32]

Right now execute() method returns single ResultSet

[zim32]

Should we add another pair of methods like executeMultiple and prepareMultiple? Or maybe make ResultSet possible to contain inner result sets

[zim32]

I think good solution is to leave execute and prepare methods as is. Instead make ResultSet extends Iterator, and add property called next to it. So it can ve iterated in for loop or manually

[Lelelo1]

The above code I posted return one result in phpMyAdmin. (The first SELECTs are stored in variables).

In my use case I need to get id to then fetch other product attributes by certain value from the Flutter app. My goal is to fetch all product attributes in one execute

[zim32]

Ok I will start to research this issue

[zim32]

I've pushed recently support for multiple statements to master branch. Can you switch temporary to master branch in your pubspec file and test it? It should now support multiple statements

[Lelelo1]

That's super to hear! I will try it the first thing I do on Monday and report back

[Lelelo1]

It works, printing the results of the iterator you added:

static void printResults(Iterator<IResultSet> iterator) {
    while (iterator.moveNext()) {
      iterator.current.rows.forEach((element) {
        print(element.assoc());
      });
    }
  }

I/flutter (20891): {@id := v.entity_id: 142508}
I/flutter (20891): {@ean := v.value: 889501092529}
I/flutter (20891): {@image_front := g.value: /f/r/freya-expression-apex-aa5494nae-front.jpg}
I/flutter (20891): {@image_back := g.value: /f/r/freya-expression-apex-aa5494nae-back.jpg}
I/flutter (20891): {@id: 142508, @ean: 889501092529, @image_front: /f/r/freya-expression-apex-aa5494nae-front.jpg, @image_back: /f/r/freya-expression-apex-aa5494nae-back.jpg}

It can be noted I could not use original syntax v.entity_id INTO @id but @id := v.entity_id instead

[zim32]

Just iterate over results in for loop. It extends Iterable. See updated docs

[zim32]

So I can close this issue?

[Lelelo1]

Yes you can close