Scripting experiment. Inspector Blair page layout is currently static code. I would like to upgrade to a content managed layout. One of the first challenges is overcoming the diverse array of forms and subsequent data calls. Dynamic queries are NOT acceptable. Instead I’ll need to find a way to create dynamic inputs for stored procedures that can be driven from a forms database.

This experiment was to test a simple array generator. Normally saving to the database is handled by a set of class calls as in this excerpt:

case RECORD_NAV_COMMANDS::SAVE:
	
	// Stop errors in case someone tries a direct command link.
	if($obj_navigation_rec->get_command() != RECORD_NAV_COMMANDS::SAVE) break;
							
	// Save the record. Saving main record is straight forward. We’ll run the populate method on our 
	// main data object which will gather up post values. Then we can run a query to merge the values into 
	// database table. We’ll then get the id from saved record (since we are using a surrogate key, the ID
	// should remain static unless this is a brand new record). 
	
	// If necessary we will then save any sub records (see each for details).
	
	// Finally, we redirect to the current page using the freshly acquired id. That will ensure we have 
	// always an up to date ID for our forms and navigation system.			

	// Populate the object from post values.			
	$_main_data->populate_from_request();
	
	// --Sub data: Role.
	$_obj_data_sub_request = new class_account_role_data();
	$_obj_data_sub_request->populate_from_request();

	// Let's get account info from the active directory system. We'll need to put
	// names int our own database so we can control ordering of output.
	$account_lookup = new class_access_lookup();
	$account_lookup->lookup($_main_data->get_account());

	// Call update stored procedure.
	$query->set_sql('{call account_update(@id			= ?,
											@log_update_by	= ?, 
											@log_update_ip 	= ?,										 
											@account 		= ?,
											@department 	= ?,
											@details		= ?,
											@name_f			= ?,
											@name_l			= ?,
											@name_m			= ?,
											@sub_role_xml	= ?)}');
											
	$params = array(array('<root><row id="'.$_main_data->get_id().'"/></root>', 		SQLSRV_PARAM_IN),
				array($access_obj->get_id(), 				SQLSRV_PARAM_IN),
				array($access_obj->get_ip(), 			SQLSRV_PARAM_IN),
				array($_main_data->get_account(), 		SQLSRV_PARAM_IN),						
				array($_main_data->get_department(),	SQLSRV_PARAM_IN),						
				array($_main_data->get_details(), 		SQLSRV_PARAM_IN),
				array($account_lookup->get_account_data()->get_name_f(), SQLSRV_PARAM_IN),
				array($account_lookup->get_account_data()->get_name_l(), SQLSRV_PARAM_IN),
				array($account_lookup->get_account_data()->get_name_m(), SQLSRV_PARAM_IN),
				array($_obj_data_sub_request->xml(), 	SQLSRV_PARAM_IN));
	
	//var_dump($params);
	//exit;
	
	$query->set_params($params);			
	$query->query();
	
	// Repopulate main data object with results from merge query.
	$query->get_line_params()->set_class_name('blair_class_account_data');
	$_main_data = $query->get_line_object();
	
	// Now that save operation has completed, reload page using ID from
	// database. This ensures the ID is always up to date, even with a new
	// or copied record.
	header('Location: '.$_SERVER['PHP_SELF'].'?id='.$_main_data->get_id());
	
	break;

 

Before we can begin to control the above calls dynamically, we’ll need to break the call down and see if we can assemble the sql string. Here we will concentrate on building the SQL string.

The form parts and column names they send data too will likely be stored in a sub-table of the forms database, and output as a linked list. We need to use those column names in a call string for sending or retrieving data. This simple experiment uses a keyed array to simulate the list we might get and see if we can concatenate a usable stored procedure call string.

$_main_data->populate_from_request();
            
// --Sub data: Role.
$_obj_data_sub_request = new class_account_role_data();
$_obj_data_sub_request->populate_from_request();

// Let's get account info from the active directory system. We'll need to put
// names int our own database so we can control ordering of output.
$account_lookup = new class_access_lookup();
$account_lookup->lookup($_main_data->get_account());

$save_row['id']                = '<root><row id="'.$_main_data->get_id().'"/></root>';
$save_row['log_update_by']    = $access_obj->get_id();
$save_row['log_update_ip']     = $access_obj->get_ip();
$save_row['account']         = $_main_data->get_account();
$save_row['department']        = $_main_data->get_department();
$save_row['name_f']         = $account_lookup->get_account_data()->get_name_f();
$save_row['name_l']         = $account_lookup->get_account_data()->get_name_l();
$save_row['name_m']         = $account_lookup->get_account_data()->get_name_m();    
$save_row['sub_role_xml']     = $_obj_data_sub_request->xml();        

$sql_str = '{call account_update(@';
$sql_str .= implode(' = ?, @', array_keys($save_row));
$sql_str .= ')}';
echo $sql_str;

//

Obviously this alone won’t be enough, but the resulting output looks quite promising:

{call account_update(@id = ?, @log_update_by = ?, @log_update_ip = ?, @account = ?, @department = ?, @name_f = ?, @name_l = ?, @name_m = ?, @sub_role_xml)}