Database Custom Query "Request Failed: error,"

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 wrote all the code by myself, not copied from anywhere else, and it works perfectly in Navicat:

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