Use parameters in Query Text of Data Source

Stimulsoft Reports.PHP discussion
Post Reply
programmer2
Posts: 13
Joined: Thu Mar 31, 2016 10:47 am

Use parameters in Query Text of Data Source

Post by programmer2 »

I need some help with using parameters inside Query Text of Data Source. I have tried to pass a date range from user to sql query like 'select @DB from dual' but when report rendering always I get an error Load Data error, and in the report checker i get Parse error: The name 'DB' doesn't exist in current context.

Thank you in advance
Alex K.
Posts: 6488
Joined: Thu Jul 29, 2010 2:37 am

Re: Use parameters in Query Text of Data Source

Post by Alex K. »

Hello,

Can you please send us your report with test data which reproduces the issue for analysis.

Thank you.
programmer2
Posts: 13
Joined: Thu Mar 31, 2016 10:47 am

Re: Use parameters in Query Text of Data Source

Post by programmer2 »

Yes, of course I uploaded a test report with a signle simple select from dual sql.
DATERANGE.mrt
(4.56 KiB) Downloaded 436 times
Thank you
Alex K.
Posts: 6488
Joined: Thu Jul 29, 2010 2:37 am

Re: Use parameters in Query Text of Data Source

Post by Alex K. »

Hello,

Oracle SQL parameters are specified using ':'. Please try to use the following query:

Code: Select all

select :DB from dual
Thank you.
programmer2
Posts: 13
Joined: Thu Mar 31, 2016 10:47 am

Re: Use parameters in Query Text of Data Source

Post by programmer2 »

Trying to make it work but I'm missing something. When trying with the declaration of parameter in screenshot I'm still getting the same error: Retrieve columns error.
Capture.PNG
Capture.PNG (21.15 KiB) Viewed 5028 times
Thank you
HighAley
Posts: 8430
Joined: Wed Jun 08, 2011 7:40 am
Location: Stimulsoft Office

Re: Use parameters in Query Text of Data Source

Post by HighAley »

Hello.

There should not be curly braces in the Expression.
Please, remove them.

Thank you.
xiaochris
Posts: 26
Joined: Wed Feb 03, 2016 1:27 am

Re: Use parameters in Query Text of Data Source

Post by xiaochris »

Hi ,

I'm also facing the same issue. I'm unable to run the following query. I'm using MySQL connection.

Code: Select all

SET @tables = (
	SELECT GROUP_CONCAT(DISTINCT CONCAT(TABLE_SCHEMA, ".", TABLE_NAME) SEPARATOR ', ')
	FROM INFORMATION_SCHEMA.COLUMNS
	WHERE COLUMN_NAME LIKE 'peripherals'
	AND TABLE_SCHEMA LIKE 'tracker'
);
SET @select = (
	SELECT 
		GROUP_CONCAT(
			DISTINCT CONCAT(TABLE_SCHEMA, ".", TABLE_NAME, ".`peripherals` AS '", TABLE_SCHEMA, ".", TABLE_NAME, ".peripherals'") 
			SEPARATOR ', '
		)
	FROM INFORMATION_SCHEMA.COLUMNS
	WHERE COLUMN_NAME LIKE 'peripherals'
	AND TABLE_SCHEMA LIKE 'tracker'
);
SET @x := CONCAT('SELECT ', @select, ' FROM ', @tables);
PREPARE stmt FROM @x;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
Alex K.
Posts: 6488
Joined: Thu Jul 29, 2010 2:37 am

Re: Use parameters in Query Text of Data Source

Post by Alex K. »

Hello,

Can you please send us a test data for your query. Or send us a simple query which reproduces the issue for analysis.

Thank you.
Post Reply