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
OK, here it is:report1627993324128.zip (48.5 KB)
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?
I don’t see anything else wrong.
Maybe @patrick can check what’s wrong here.
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