Arguments in Stored Procedure

[10001]

Need some help from someone who knows how V11 handles insertion of arguments. I'm using mysql, and a grid. Before I execute the grid, I execute a stored procedure to GROUP_CONCAT some results so that I can put them in a session variable, then use them in an IN clause. Simplified example:

SELECT * FROM tasks WHERE EmployeeID IN (5,9,14,34)

My problem is when I try to use the session variable with an argument in the SQL query. It's set up like this:

SELECT * FROM tasks WHERE EmployeeID IN (:argument1)

The problem appears to be that, since the session variable and the argument are character type variables, Alpha's automatically putting quotes around the argument when it inserts it into the SQL statement. However, I don't want that, because that causes the query to process incorrectly.

Is there any way to avoid this? I tried modifying the code in the onSQLSelectQuery event, but it didn't work very well with my search part. It would work fine the first time, then error out on any subsequent search ("Error expanding grid part template: Variable 'Data' not found.")

I came up with a workaround that in theory should work, but isn't. If Alpha is adding quotes, I could actually add another set of quotes and some commas and get it to work correctly:

SELECT * FROM tasks WHERE EmployeeID IN ("",5,9,14,34,"")

Unfortunately, this didn't work either, regardless of the method I tried (escaped double quotes, single quotes, the QUOTE function...)

Anybody have any ideas on this?

Thanks,

Cliff

[Michael1954]

Re: Arguments in Stored Procedure

I don't know if this helps http://news.alphasoftware.com/V12Pre...seNotesV12.htm

This is from version 12 check to see if Alpha supports the IN Clause in V11 I guess it will. If this helps there is a video

UX and Grid Component - Action Javascript - Print Report Action - SQL Reports - IN Clause - When using Action Javascript to display a report based on a SQL datasource, you might want to use the SQL IN clause in your report filter.

Watch Video
Download Component (Requires a connection string called 'Northwind' to sample Northwind database)

For example, you might have a checkbox control and you want to print the report showing all values checked in the checkbox control. The syntax for the IN clause is as follows:
SELECT * from PRODUCTS WHERE COLOR IN ('red', 'green', 'blue')
Translating this to use arguments:
SELECT * from PRODUCTS WHERE COLOR IN (:whatcolor)
In the above example, you cannot use a simple character argument. You must use a special 'array argument'.
In the Action Javascript builder, when you define arguments to use in the report filter, a special naming convention is used to indicate that the argument is an array argument. If the argument name starts with 'array_', the argument is assumed to be an 'array argument'


TIP: Here is some sample code that shows how you use Xbasic to populate an 'array argument':
dim args as sql::arguments
dim tempArray[0] as c
tempArray[] = "red"
tempArray[] = "blue"
tempArray[] = "green"
args.add("whacColor",tempArray)

Michael