SQL and Wonderware help

Hello all,

I am trying to connect an older application in our plant to an Oracle database. I'm using Wonderware 7.0 with SQL access. SQL is new to me, and I'm having trouble connecting to the database. I am using the following code in an action tied to a pushbutton:
Result= SQLConnect(ConnectionID, "DSN=####[;uid=####[;pwd=####;SRVR=t:####)");
When I go into runtime and press the button, all I get is a connection failed error. When I look at the wonderware logger, there are two lines as follows:

Error: Connection failed DSN=####[;uid=####[;pwd=####;SRVR=t:####)

4149: (Microsoft)(ODBC Driver Manager)Data source name not found and no default driver specified.

Of course I'm substituting the ###'s for the DSN info setup in ODBC Data Sources through Control Panel. My PC is using Windows XP service pack 2, and I think the Oracle is Version 9.2.

Thanks in advance,
Mike

Define your DSN

Mike,

Try this format for your SQLConnect:

ResultCode = SQLConnect (ConnectID, "Provider=#####; Data Source=#####; Initial Catalog=#####; UID=#####; PWD=#####");

The Provider for Oracle I believe should be ORAOLEDB, but you might want to do some homework on that. Data Source is your server name, Initial Catalog is name of your database.

Also, it is good practice to put your SQLConnects in your startup script, and then use the Connection ID for the various SQL functions in your application. Make sure to also put a SQLDisconnect in your shutdown script to sever the connection to the database when your application closes.

Hope this gets you moving in the right direction.

Jeff

Jeff,
One thing I am unsure of is the server name. Is that the same as the DSN, or am I on the wrong track.
Thanks,
Mike

Mike,

The server name is the name of the computer that the database resides on.

Jeff

Driver?

I'm still getting a "default driver not specified" error message.
Any ideas, anyone?

Thanks,
Mike

Mike,

For the "Provider = ", try OraOLEDB.Oracle. So your string should look something like:

ResultCode = SQLConnect (ConnectID, "Provider=OraOLEDB.Oracle; Data Source=#####; Initial Catalog=#####; UID=#####; PWD=#####");

Fill in the appropriate data for the #'s. Make sure your quotation marks and semicolons are as I have them here. You might just want to copy and paste this statement, as that is what I did for this post from a working application.

Jeff

Don't you have to set up an ODBC DNS in the ODBC Data Source Manager?

Dave,

Not if you're using OLEDB. I used to do exactly what you're saying, but found it to be easier this way as you do not have to make sure there is a DSN configured on every client that might need to run this application. If any of your connection parameters change, it's also easier to maintain this way rather than having to touch every client machine using this connection.

Jeff

I have a new problem now. I have two action scripts assigned to two buttons, and each works fine. When I try to combine the two to selectively filter the query results, I always get the following error:
[Microsoft][ODBC driver for Oracle][Oracle]ORA-00933:SQL command not properly ended
Ultimately,I will need to use four different criteria for my query, but as of now, can't even get two to work together.

Here are the two individual scripts that work fine:

1. Result = SQLSelect( ConnectionID, "so_load_tbl", "TagSQLBind", "so_load_key= " + Text(LOAD_NUMBER,"####"),"");

2. Result = SQLSelect( ConnectionID, "so_load_tbl", "TagSQLBind", "in_whs_key = '038S'","");

And here is the combined script:

Result = SQLSelect( ConnectionID, "so_load_tbl", "TagSQLBind", "so_load_key= " + Text(LOAD_NUMBER,"####") + "and" + "in_whs_key = 038S","");

any ideas or suggestions would be greatly appreciated.

" and " space ?

That was part of the problem, I also found a coulpe more syntax errors. It's working now! Thanks.