MindQuake MySQL action plugin with BLOB field

[claus707]

Hello,
I am using MindQuake MySQL action plugin but I am having 2 issues

1- How can I insert/select blob field?!

2- How to get a specific field from a row that MySQL.QueryToTable sends back?!
(I call 3 fields SELECT row1, row2, row3 FROM table; but I want to do total = row1 * row2....)

[Ulrich]

1. You cannot INSERT binary data into a database with the plugin, however you can SELECT fields containing binary data of a previously created database which is shipping with your application. As I state in the documentation, BLOB fields are returned as Base64-encoded strings in MySQL.QueryToTable().
   
   If you need to store binary data at runtime, Base64-encode it and store it as TINYTEXT, TEXT, MEDIUMTEXT, or LONGTEXT in the database; upon retrieval convert back to binary and do what is required with the data.
   
   
2. MySQL.QueryToTable() returns a table, obviously. You get a specific field of the resultset by using the proper index of the field you want: tResultset[row][column]. It appears that you are slightly confused what "rows" are: A SELECT statement selects columns, not rows.

Ulrich

[claus707]

Thank you Ulrich, as always U are the best man !

For the first issue I found somehow the solution

resultset = MySQL.QueryToTable("SELECT poltronat.id, poltronat.poltrona, poltronat.modeli, poltronat.aktive, poltronat.info, sallat.salla FROM sallat INNER JOIN poltronat ON poltronat.id_salla = sallat.id;", false);
Debug.ShowWindow(true);
Debug.Clear();
for i,row in pairs (resultset) do
              Debug.Print("ID : " .. row[1] .. "   Poltrona: " .. row[2] .. "\r\n")
end

[Ulrich]

Here is a full example how to work with BLOB data with my MySQL plugin. In the image below you can see that I have a database named "test", where a table named "sample" was created, containing two fields, "id_sample" and "bindata". There is only one record in the table (id_sample=1), and the bindata of that record holds an image of a ladybug.



In your AutoPlay Media Studio project, you would use the MySQL plugin to retrieve the bindata field, which is Base64-encoded automatically by the plugin. All you need to do is save the string to a text file and convert it back to binary, or do whatever you want to do with it.

The attached sample project shows you how the image stored in the database appears on the page after the BLOB data was retrieved with aid of my plugin:



The project and database data is attached.

Ulrich