Handling SQL Query Data With Multiple Statements

[madmojo]

Hello, I'm new to most areas of programming, so forgive me if this is quite novice. I've finally wrapped my head around MySQL enough to get the query results that I want. However, to do so, I've had to make multiple statements with temporary tables, variables, etc.

In Retool, I'm trying to access that data, and I can't seem to do so properly. Prior to getting a working SQL statement, my first test in Retool was a simple, single statement that returned rows the way I expected, and I could access that data with {{ query1.data }}.

In the query I now want to use, the results seem to come back in multiple arrays. The "preview" option shows me 0: null, 1: null (etc) until I get 6: [] 13 items. If I try to populate a table in Retool with that data, using {{ query1.dataArray['6'] }}, the columns actually display as rows.

Hoping for some help to let me know how to reference that data as easily as I had with {{ query1.data }}.

Thank you!

[ScottR]

@madmojo welcome to the forum
Is it possible for you to share the query and what the result set looks like in Retool?

[madmojo]

Thank you @ScottR - I'm sure there's a much more efficient way of preparing my SQL statements, but at the moment they work and run quickly, so before I refine them, I'd like to just be able to see the results in Retool.

EDIT: I've tested extensively and it's not an issue with the SQL query. It's an issue with processing the response. This works fine:

SELECT * FROM products;

But, the same select as a stored procedure doesn’t:

CALL GetAllProducts();

Which, is the same SQL statement stored as a procedure. In the procedure, it returns results like below, which I can't seem to adjust or reference the Array to get the actual data out of...

[ScottR]

@madmojo

FORMAT(AVG(Total_Rental_Days),2)

Is it possible that your AVG calculation doesn't work until you get to something divisible by 2?

Also, I honestly cannot make heads or tails of your query and perhaps someone else with better db skills than me could assist....

[madmojo]

Thanks for the suggestion. Unfortunately, it's not that.

At first I thought the variables were causing issues, and they may have been. But, I've taken those out for testing. What I've found is that if I remove the first two SQL statements that CREATE TEMPORARY TABLEs, and just run the last two statements on hard-coded tables in my database, I get the results back just how I'd want and expect.

However, if I put those two statements back in, I get the data formatted as show in my screenshots. I've tried return {{ formatDataAsObject(query1.dataArray['4']) }} as a Transformer and no luck...

EDIT: I'm thinking this is related to the bug mentioned here:

Call MySQL Procedure from Retool

Hi, I'm trying to call a procedure against my MySQL DB, however the result in query.data doesn't come correctly. The retrieved data looks like a metadata from the actual result. And when I call this procedure from the query library I receive this error: [Screenshot from 2022-02-15 15-41-31] Can anyone help me?

Even when it's not a stored procedure. It seems like if Retool doesn't get a bland response from MySQL like so:

[
{
"Product_ID": 86,
"QtyOwned": 64,
}
]

And, instead, gets:

[
{
"output": {},
"recordset": [
{
"Product_ID": 86,
"QtyOwned": 64,
}
]
}
]

It can't parse out the results data and instead gets stuck looking at the metadata/headers.

Would love confirmation if I'm wrong, and/or any other suggestions or workarounds.

[madmojo]

FYI for anyone that runs into this issue in the future, Retool doesn't currently like multiple combined statements (ended by semicolons) in SQL, which is a requirement of Temporary Tables... Instead, it works fine to just use CTE's by starting with the WITH statement in your query.