ATTENTION

This FlexSim Community Forum is read-only. Please post any new questions, ideas, or discussions to our new community (we call it Answers) at https://answers.flexsim.com/. Our new Question & Answer site brings a modern, mobile-friendly interface and more focus on getting answers quickly. There are a few differences between how our new Q&A community works vs. a classic, threaded-conversation-style forum like the one below, so be sure to read our Answers Best Practices.


flexsim.com

Go Back   FlexSim Community Forum > FlexSim Software > Tips and Tricks
Downloads

Tips and Tricks Share helpful modeling ideas

  #1  
Old 11-04-2010
AJ Bobo's Avatar
AJ Bobo AJ Bobo is offline
Flexsim Senior Developer
 
Join Date: Jul 2007
Posts: 108
Downloads: 8
Uploads: 0
Thanks: 23
Thanked 89 Times in 41 Posts
Rep Power: 221
AJ Bobo is a jewel in the roughAJ Bobo is a jewel in the roughAJ Bobo is a jewel in the roughAJ Bobo is a jewel in the rough
Default Database Stored Procedures

Recently I was asked about Flexsim's abilities to work with Stored Procedures in a database. I wasn't sure how to answer, so I did a little research into it and discovered how to call stored procedures for some different types of databases. The method for creating stored procedures is different for each type of database, as is the method for calling them. I'm not going to go into how to create them here, but I want to record how to call them using Flexsim's dbsqlquery() command.

For Access 2007, you use the EXECUTE command: (This might work with 2003, but I don't know. If someone can try it and let me know, that would be great.)
Code:
dbsqlquery("EXECUTE MyQuery"); // Calls a query with no parameters
dbsqlquery("EXECUTE MyOtherQuery 10"); // Calls a query with 1 parameter
For MySQL, you use the CALL command:
Code:
dbsqlquery("CALL MyQuery()"); // Calls a routine with no parameters
dbsqlquery("CALL MyOtherQuery(10)"); // Calls a routine with 1 parameter
For SQLServer, you can use the EXEC command:
Code:
dbsqlquery("EXEC MyQuery"); // Calls a stored procedure with no parameters
dbsqlquery("EXEC MyOtherQuery @param1 = 10"); // Calls a stored procedure with 1 parameter
NOTE: Don't call SELECT queries in a SQLServer stored procedure. They return tables with forward-only cursors and Flexsim doesn't support them (you'll see a "Fetch type out of range" error). However, you can call stored procedures that update tables and then you can call the SELECT query yourself.
I haven't wished for stored procedures too many times while building models, but once I figured this out, I thought I should share this with everyone so that we all know what can be done (and we can find the syntax for it later).
The Following 6 Users Say Thank You to AJ Bobo For This Useful Post:
Vadim Fooks (12-12-2012)


Thread Thread Starter Forum Replies Last Post
Connect Flexsim to Oracle database RalfGruber Q&A 18 08-05-2013 12:15 PM
advise on using database operation David Chan Q&A 2 09-27-2010 03:38 AM
Problems with import of data from a database Matthias Hofmann Q&A 1 09-26-2008 09:42 AM
Sample Access Database Brandon Peterson Tips and Tricks 1 07-09-2008 12:15 PM


All times are GMT -6.
Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2020, vBulletin Solutions Inc.
Copyright 1993-2018 FlexSim Software Products, Inc.