question

Damodaran Moturu G avatar image
0 Likes"
Damodaran Moturu G asked Michael Machado commented

Import data to global table from database

Hi,

I have global tables in my model and as of today I'm importing data using excel import option. Going forward, I will have to get the data from SQL Server database instead of excel. I could not find any documentation or Q&A on how to import data to global tables from database. Please advise whether it is possible to import data to global table from database. If yes, would be grateful if you could guide me with sample code.

Thanks

Damu

FlexSim 18.0.0
database import
5 |100000

Up to 12 attachments (including images) can be used with a maximum of 23.8 MiB each and 47.7 MiB total.

Mischa Spelt avatar image
3 Likes"
Mischa Spelt answered ShabDz edited

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();
}


· 4
5 |100000

Up to 12 attachments (including images) can be used with a maximum of 23.8 MiB each and 47.7 MiB total.

Michael Machado avatar image Michael Machado commented ·

Hi @ANIL KUMAR N

Bear in mind that version 2018 Update 1 has a new feature, which is pretty esasy conect many databases with FlexSim. See picture below.

1 Like 1 ·
db.png (60.9 KiB)
Damodaran Moturu G avatar image Damodaran Moturu G commented ·

Thank you @Mischa Spelt. Your code is very helpful indeed. I could able to get the data from oracle table to flexsim global table. I learnt a lot of new coding techniques from your code. Thanks again :)

0 Likes 0 ·
ANIL KUMAR N avatar image ANIL KUMAR N commented ·

Where to include this code?

0 Likes 0 ·
Damodaran Moturu G avatar image Damodaran Moturu G ANIL KUMAR N commented ·

Hi @ANIL KUMAR N,

It depends on your model logic. I wanted to import data at the beginning of model run. So I included the code under 'On Run Start' in 'Model Trigger'. See flextrigger.png

Cheers.

0 Likes 0 ·
flextrigger.png (283.8 KiB)
Ben Wilson avatar image
1 Like"
Ben Wilson answered Damodaran Moturu G commented

Hi @Damodaran Moturu G,

This MySQL+ODBC tutorial can walk you through the basics of connecting an external database software to FlexSim. It is not a perfect match - it looks like you'll be using a different database software, but the basic principles will be the same.

The main difference is that you will need to find the proper ODBC driver for your database application.

Comment back if you get stuck or have any questions connecting your database.

If you have other questions unrelated to the top post, please ask them in a new question.

Thanks!

· 1
5 |100000

Up to 12 attachments (including images) can be used with a maximum of 23.8 MiB each and 47.7 MiB total.

Damodaran Moturu G avatar image Damodaran Moturu G commented ·

Hi @Ben Wilson Your tutorial is the first document i referred to. Without which I could have not connected flexsim to the database. I could use it as reference it to connect to oracle db. Thanks you very much for compiling it.

0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 12 attachments (including images) can be used with a maximum of 23.8 MiB each and 47.7 MiB total.