Connect Flexsim to Oracle database

Hi,

I just tried to connect Flexsim to an Oracle database (Oracle 10g XE) but I failed. The same code which is working fine for MS Access databases doesn´t work here.
I can connect from Excel to the databse using the ODBC driver for Oracle, but when I try from Flexsim, it just does nothing...

Someone else who ran into that problem and found a solution for that?

Thanks

Ralf
aka ralle (Flexsim Germany)

Ralf,

If it's opening the database to read data, have you tried to open it in both modes (table and SQL)? Also do you need to supply login parameters (Windows or Oracle)?

If you're trying to write data, the formatting of SQL statements can be different to Access - worth looking at the ANSI standard.

Just initial thoughts. Would be interested in the solution when you find it.

Jason

Hi Jason,

thanks for your good will

I tried it in both, SQL and table mode and both failed. Yes Oracle databases always require a user login and I did it the same way it worked with access either using dbusername() or forcing the login window to open with the fourth parrameter ob dbopen() set to "1". The system console prints out the following, when I try dbopen...

ex: Exception caught in flexscript execution of ex: line 3 instruction 2. Discontinuing execution.

Do you have any experience with other databases than access where I can get a hint from to solve my issue? Everything is welcome.

Thanks in advance

Ralf
aka ralle
(Felxsim Deutschland)

Hi Ralf,

Try first to add the user name and password to your ODBC connection.

I use a database server called Firebird in a software I have developed and I tried to connect
to one database on my Firebird server from Flexsim, it works for my.

What I did was to define a Data Source (ODBC) to point at my database and
in that definition I also put my user name and password for the database.
Now I could use dbopen to get the data I needed.

I did the test in version 4.5

Lars-Olof

Hi Lars-Olof,

I created an ODBC alias too and that works fine (it contains a button to check the connection), but I didn´t find an option to put login data in? Where did you find that? I am using the Oracle XE ODBC driver which is installed with the software.

Thanks in advance

Ralf
aka ralle
Flexsim Deutschland

Hi Ralf,

In Firebird there is 2 fields one for user name and one for the password.

Try this for oracle.
In User ID for the ODBC connection you should write user name/password
For example if the user name is test1 and password is test2 the user id should be: test1/test2

I do not have Oracle installed, tried to install the client in VmWare but get set some error.

Hope this can help you otherwise I out of ideas for the moment.

Lars-Olof

Hi Ralf,

Did you get it to work?

Lars-Olof

I have been working with Ralf on this one for a few days. So far, I've found two problems:

1) Flexsim isn't reporting error messages from Oracle correctly. This is why there's no obvious error displayed. I've fixed this problem for the next Flexsim release.
2) Once Flexsim was displaying the error messages correctly it showed me this message:

Specified driver could not be loaded due to system error 1114 (Oracle in XE).

I'm still working on how to fix this one. Has anyone seen this error before in other situations?

Hi,

I did a search on internet and it seams it no direct solution for this problem.
It looks like it is everything from missing dll like MFC71.DLL, Mfc71enu.dll and
msvcr71.dll to multiple instance of the dll for oracle.
The MFC71.DLL, Mfc71enu.dll and msvcr71.dll should be in the same directory as the dll for
Oracle ODBC drivers.
Some have installed version 10.1 and get that to work.
Another thing that seamed to work for some is to set the environment variable ORACLE_HOME.

This place may give you some more ideas: http://forums.oracle.com/forums/thre...86332&tstart=0


Ralf is it important for you to use Oracle? Is it because a customer have Oracle?


Lars-Olof

Well, it looks like we found a solution. After looking through a lot of the same message boards that Lars-Olof found (and posting my own questions to some), I discovered that the ORACLE_HOME environment variable was mentioned several times. So I decided to try that route.

I created a new environment variable, called it "ORACLE_HOME" and set it to:
C:\oraclexe\app\oracle\product\10.2.0\server (that's where Oracle XE is installed on my computer)

Once I did that, everything seemed to work correctly. I don't know if this will fix every problem with Oracle or if I just got lucky. But I thought I'd post this solution here so that we remember to try it in the future.

Hi AJ,

I know this is an old topic but I have exactly the same problem. If I test the connection on the ODBC setup GUI everything is fine (Connection Succesfull)

But if I try it from within Flexsim it gives the error 1114.

So for my understanding and apply your fix correctly :
1. Did you create the ORACLE_HOME in system variables or in user variables? or shouldn't that matter?
2. Did you remove the oracle path from the "Path" system variable or not?
3. And with you, does it point to the BIN directory or 1 directory up from BIN.

I do hope that you or Ralf can remember anything

Steven,

Here's what I remember:
1. I think I put ORACLE_HOME in my system variables.
2. I probably left the Oracle Path in in the "Path" system variable.
3. I can't really remember whether the path pointed to Bin or not. It probably did.

Here's another thing I've learned about Oracle 10g in the last year or so though. It does NOT work well on 64-bit computers. Oracle configuration files use ( and ) as delimiters a lot. As a result Oracle gets very confused if a path uses those two characters. I found that a program installed in "Program files (x86)" could not work with Oracle through ODBC at all. If I copied the program to a different directory that didn't have ( or ) in the name, then it could communicate with Oracle. I don't know if this bug has been fixed in Oracle 11 or not. I think I read that it was.

I have got it to work yesterday so because it is still fresh I'll like to give some more details:

My path is: C:\oracle\product\10.2.0\client_1

So I created the ORACLE_HOME in system variables and give it the above path so without a slash, BIN or any other directory.

I did remove the oracle path from the PATH variable (if I kept it, it didn't work)

I didn't have to restart my computer but I did restart Flexsim

Ok, I have another question. I try to take a look at one of my oracle database tables. So I have tried 2 different approaches:
1. with dbviewtable
2 dbimportnode

But both methods are painstakingly slow. I have a table of 72 rows by 25 cols and it takes anywhere from 20 to 90 seconds.
The other commands (insert into or select ) are quite fast considering a vpn connection over the internet.
Has anybody else encountered this? Is there a setting or something I can do to speed it up?

Steven,

When you try to view a database table, it has to read every single cell in the table. 1800 cells can take a while to read and copy into the tree. This is even slower if you need to create nodes to store those values (like dbviewtable() does). I don't know any way to speed that process up.

That does sound slow to me. For comparison, a table with 6000 rows and 22 columns and each row having a subtable with between 30 and 300 cells imports in about 30 seconds using dbimportnode. That's on a local db and not Oracle, and is a set of queries, some of which are stored in the database.

Hi everybody, this is my fist post.
I have a database of 100 tables, I want to send information from one of them called "STORES" of 286 rows and 10 columns (small) to a "Global Table", my question is: how is the query to get this?
My database odbc is "Farma"
I've already created a username

Thanks

Have you looked at the command list with all the db commands?

use either dbimportnode or dbimporttable after you made a query on the database.

Hi Steven.
Yes, i used it thanks. According to the table that I use (286 lines, 10 columns), I have observations:
1. Using

dbopen("All database","table.i.used",1);
dbimporttable ("GlobalTable" 1,10,286,10);
dbclose();

send to the global table just the first column with 286 rows. And the other 9 columns return zeros into the global table.
2. The reading process is slow.
3. Instead of import the table from the data base (for lightweight process) to create a trigger on an object, which:
- Go to read only the required data directly to the database.
thanks
Pedro Gallardo