I cannot recreate your issue locally. Can you restart Wappler with logging again and send me the latest log created here, after you recreate the issue?
C:\Users\YOUR_USERNAME\AppData\Roaming\Wappler\logs
No Jay, that is not the report log …
restart Wappler with logging again and send me the latest file created here:
C:\Users\YOUR_USERNAME\AppData\Roaming\Wappler\logs
It’s a file named somefilename.log
Here is what I found in the location you mentioned, it was wappler.log and I have zipped it to upload it.
wappler.zip (3.5 KB)
Also, something I have noticed is that if I edit the sql file in the modal (to eliminate the comments) later when I close and open Wappler again, in the modal appears the commented custom sql. In other words the edits on the SQL file don’t get saved.
I still see all the comments in your log.
Please delete the server action and create a new one with a new custom query step. Make sure to paste inside just the custom query code, no comments, nothing else - just pure sql code.
See if it makes any difference.
Ok, I created a new API action and query. I copied an uncommented sql custom query, but the error is the same as before:
The new log file I get is the following:
wappler2.zip (3.5 KB)
and when I try to close the modal, I get an additional message that I didn’t get before:
There are still weird characters in the log.
Where/how exactly do you copy this sql from before pasting it in the Wappler custom query dialog?
Can you please paste the exact query code here, so i can test with it?
Here it is, I am just copying a text file and it still looks funny:
WITH live_auction AS (
SELECT
call_id,
user_id,
buying_quantity,
selling_quantity,
share_price,
sum(buying_quantity) over(order by share_price Desc, call_id desc) as demand,
sum(selling_quantity) over(order by share_price asc, call_id asc) as supply
FROM auction_calls
ORDER BY share_price ASC, call_id
),
users_name AS (
SELECT
user_id,
first_name,
last_name
FROM users
),
auction_orders AS (
SELECT
call_id,
user_id,
buying_quantity,
selling_quantity,
share_price,
supply,
demand,
case
when demand < supply then demand * share_price
else supply * share_price
END AS revenue
FROM live_auction
ORDER BY share_price ASC, call_id
),
auction_orders2 AS (
SELECT
call_id,
user_id,
selling_quantity,
buying_quantity,
share_price,
supply,
demand,
revenue,
MAX(revenue) over() as LargestRevenue
FROM auction_orders
ORDER BY share_price ASC, call_id
),
auction_orders3 as (
SELECT
call_id,
user_id,
buying_quantity,
selling_quantity,
share_price,
supply,
demand,
revenue,
LargestRevenue,
case
when revenue = LargestRevenue then share_price
else '-'
end as clearing_price,
case
when revenue = LargestRevenue and supply < demand then supply
when revenue = LargestRevenue and supply > demand then demand
else '-'
end as volumetransacted
FROM auction_orders2
ORDER BY share_price ASC, call_id
),
auction_orders4 as (
SELECT
call_id,
user_id,
buying_quantity,
selling_quantity,
share_price,
supply,
demand,
revenue,
LargestRevenue,
clearing_price,
volumetransacted,
MAX(clearing_price) over() as cp,
MAX(volumetransacted) over() as vt
FROM auction_orders3
ORDER BY share_price ASC, call_id
),
auction_orders5 as (
SELECT
call_id,
user_id,
buying_quantity,
selling_quantity,
share_price,
supply,
demand,
revenue,
LargestRevenue,
clearing_price,
cp,
volumetransacted,
vt,
vt-demand as vtd,
vt - supply as vts
FROM auction_orders4
ORDER BY share_price ASC, call_id
),
auction_orders6 as (
SELECT
call_id,
user_id,
buying_quantity,
selling_quantity,
share_price,
supply,
demand,
revenue,
LargestRevenue,
cp,
clearing_price,
volumetransacted,
vt,
vts,
vtd,
LAG(vts, 1, 0) over (ORDER BY share_price ASC, call_id) as lvts,
LEAD(vtd, 1, 0) over (ORDER BY share_price ASC, call_id) as lvtd
From auction_orders5
order by share_price asc, call_id
),
auction_orders7 as (
SELECT
call_id,
user_id,
buying_quantity,
selling_quantity,
share_price,
supply,
demand,
revenue,
LargestRevenue,
cp,
clearing_price,
volumetransacted,
vt,
vts,
vtd,
lvts,
lvtd,
case
when vtd >= 0 then buying_quantity
when vtd < 0 and lvtd >= 0 then lvtd
else '0'
end as shares_bought,
case
when vts >= 0 then selling_quantity
when vts < 0 and lvts >= 0 then lvts
else '0'
end as shares_sold
From auction_orders6
order by share_price asc, call_id
)
SELECT
call_id,
first_name,
last_name,
buying_quantity,
selling_quantity,
share_price,
shares_bought,
shares_sold,
case
WHEN buying_quantity > 0 AND shares_bought = 0 THEN 'unsuccessful order'
WHEN buying_quantity > 0 AND shares_bought = buying_quantity THEN 'order filled'
WHEN buying_quantity > 0 AND shares_bought < buying_quantity THEN 'order partially filled'
WHEN selling_quantity > 0 AND shares_sold = 0 THEN 'unsuccessfull order'
WHEN selling_quantity > 0 AND shares_sold < selling_quantity THEN 'order partially filled'
WHEN selling_quantity > 0 AND shares_sold = selling_quantity THEN 'order filled'
END AS comments
From auction_orders7
LEFT JOIN users_name
ON auction_orders7.user_id = users_name.user_id
order by share_price asc, call_id
I get a syntax error when trying to use this code. Are you sure you’ve not copied it wrongly/not fully copied it? Or maybe you edited it and broke the code?
Are you sure you know what you are doing with this code?
Just to summarize for when Patrick is available:
The error is independent of the complexity of the query. A simple custom query throws the same error in the Database Custom Query modal:
When exiting the modal, I get the following error:
Queries to the container in AWS work fine with Navicat:
The log file produced with the simple query shown above is the following:
wapplerlog3.zip (2.2 KB)
I hope it helps.
Best.
Jay
I changed from version 4.0.1 to 3.9.9 and the problem persisted. Using 3.9.9, I decided to create a local target with the same docker, node, mysql configuration and then the custom query worked, see below:
I have decided to try to develop everything in the local target, but there are some situations (like for instance Ken’s user registration course) that still require the use of a live target.
I understand now that working with custom queries and live targets can be problematic, better develop in a local target and when done and tested just deploy at once.
Hope it helps.
Best.
Jay
Fixed in Wappler 5.2.0