question

Here is a bit of code I wrote a while ago to read the result of a query into a Global Table in the model.

It's fairly well-commented (even for me) so hopefully you will be able to figure it out.

if(dbgetmode() == DBMODE_CLOSED)
{
	showprogressbar("Connecting to ODBC-connection 'ODBC Name'...");
 
 
	dbopen("ODBC Name", "<SQL Query here>", 0);
}
 
 
showprogressbar("Importing data from the database...");
 
 
// Store the size of the query result
int rows = dbgetnumrows();
int cols = dbgetnumcols();
 
 
// Clear the bundle
Table target = Table("Result");
target.setSize(0, 0);
 
 
// We can save ourselves work if there is no data
// Plus I want to be sure that we have at least one row of data from now on.
if(rows > 0)
{	
	// Figure out the column types based on the first record, and add columns to our bundle
	for(int i = 1; i <= cols; i++)
	{
		int type = stringtonum(dbgettablestr(1, i)) > 0 ? BUNDLE_FIELD_TYPE_DOUBLE : BUNDLE_FIELD_TYPE_STR;
		addbundlefield(target, dbgetfieldname(i), type );
	}
 
 
	// Now create enough rows for all the data
	target.setSize(rows, cols + 1);	
	
	// Fill the data column by column so we only have to check each column type once
	// We expect about 30k rows, so every 1k rows should be informative and still smooth.
	for(int j = 1; j <= cols; j++)
	{
		int type = getbundlefieldtype(target, j - 1);
		for(int i = 1; i <= rows; i++)
		{
			// Updating the progress bar on EVERY record makes it a bit slow.
			if(i % 1000 == 0)
			{
				setprogressbar( 100 * ((j - 1) * rows + i) / (rows * cols) );
			}
			
			// This is a bit ugly but there is no dbgettableval(i, j)
			// Does FlexScript have branch prediction? ;-)
			if(type == BUNDLE_FIELD_TYPE_STR)
			{
				target[i][j] = dbgettablestr(i, j);
			}
			else
			{
				target[i][j] = dbgettablenum(i, j);
			}
		} // for(i)
	} // for(j)
} // rows > 0
 
 
hideprogressbar();
 
 
// Calls AssignPallets();
resetmodel();
 
 
if(msg("Close database?", "Do you want to close the database? If you want to read from the database again later on, it may be faster to keep it open. Select Yes to close the database, No to keep it open.", 4))
{
	dbclose();
}