Error when using temp table in browse form

Questions related to using nuBuilder Forte.

Error when using temp table in browse form

Unread postby oli » Sat Sep 25, 2021 4:14 pm

Hello,
I added an Iframe with a specific Form to list all Items that are assigned to a project.
The data will be collected by selecting the data from different tables and writing the result in a temp table by following code in the BEFORE BROWSE custom code of the Items Form:
Code: Select all
// Temp Table
$create = "CREATE TABLE #TABLE_ID# ";

// get items from assigned project
$q = 'SELECT pro_items FROM project WHERE project_id = "' . "#tas_project#" . '";';
$x = nuRunQuery($q);
$y = db_fetch_object($x);
$items = $y->pro_items;


$items = str_replace(']', '',str_replace('[', '', $items));     // remove [ and ] from the result

// create temp TABLE
if ($items != ""){
    $qry = 'SELECT * FROM item WHERE item_id IN (' . $items . ')';  // get data
    nuRunQuery($create.$qry);                                       // create temp table
}


It works great when there are items assigned to a project.
But if there are no results I got following error in the debug log because the temp table will not be created:

Code: Select all
1st Debug entry:
__________________________________________

SQLSTATE[42S02]: Base table or view not found: 1146 Table 'db195572x3263722.___nu1614ec21204891___' doesn't exist

===SQL===========

SELECT COUNT(*) FROM (SELECT item_id,ite_label,ite_status,ite_manufacturer,ite_model,ite_saler
FROM ___nu1614ec21204891___ END
WHERE 1) nuTCount

__________________________________________
2nd debug entry:
__________________________________________

SQLSTATE[42S02]: Base table or view not found: 1146 Table 'db195572x3263722.___nu1614ec21204891___' doesn't exist

===SQL===========

SELECT item_id,ite_label,ite_status,ite_manufacturer,ite_model,ite_saler
FROM ___nu1614ec21204891___ END
WHERE 1 LIMIT 0, 7


I tried to avoid the error message by using a condition in the SQL statement (Form - Browse) but the error still occurs:
IF (EXISTS (#TABLE_ID#))
BEGIN
SELECT * FROM #TABLE_ID#
END


Are there any solutions how to avoid the error?

BR, Oli
oli
 
Posts: 85
Joined: Sat Mar 20, 2021 11:52 pm
nuBuilder Version: 4.5

Re: Error when using temp table in browse form

Unread postby kev1n » Sat Sep 25, 2021 4:40 pm

The Temporary Table must be created under all circumstances. Maybe you could select a "dummy" row when $items is blank?

Code: Select all
// create temp TABLE
if ($items != ""){
    $qry = 'SELECT * FROM item WHERE item_id IN (' . $items . ')';  // get data
   
} else {

   $qry = "SELECT NULL as 'item_id',NULL as 'ite_label',NULL as 'ite_status',NULL as 'ite_manufacturer',NULL as 'ite_model',NULL as 'ite_saler'";
}

nuRunQuery($create.$qry);                                       // create temp table
kev1n
nuBuilder Team
 
Posts: 2059
Joined: Mon Oct 15, 2018 2:13 am
nuBuilder Version: 4.5

Re: Error when using temp table in browse form

Unread postby oli » Sat Sep 25, 2021 4:53 pm

Thanks kev1n ... great support again !!

solved
oli
 
Posts: 85
Joined: Sat Mar 20, 2021 11:52 pm
nuBuilder Version: 4.5


Return to General

Who is online

Users browsing this forum: No registered users and 12 guests