executing a prepared mysql

Scriptcase 8 Discussion
#1

Seen below is my mysql query which creates the output I want. My goal is to get the output into a grid created in Scriptcase.
I can’t figure out where to write my @sql variable, or where to execute it. I’ve tried to place the code to create it in the onapplicationinit event, but SC doesn’t like it.

SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
“sum(case when wohdate2 = '”, wohdate2,"’ then contract_price else 0 end) as ", wohdate2, ""
)
)
into @sql
from wohdata;

SET @sql = CONCAT('SELECT woh_job_desc, ‘, @sql, ’ FROM wohdata GROUP BY woh_job_desc’);

PREPARE stmt FROM @sql;
EXECUTE stmt;

#2

You must use a macro sc_lookup Example:

sc_lookup(dataset, “select customer_id, customer_name, credit_limit from customers” );

To have access to the first line (Dataset), use :
{customer_id} = {dataset[0][0]};
{customer_name} = {dataset[0][1]};
{credit_limit } = {dataset[0][2]};

To have access to the second line (Dataset), use:
{customer_id} = {dataset[1][0]};
{customer_name} = {dataset[1][1]};
{credit_limit} = {dataset[1][2]};