Micros 3700 Res5 OLEDB connection 1

[pmegan]

Hi,

I'm about to make my first jump into Res5 and need to find out how to connect to the database from a remote pc. I have some .Net applications that connect to my current Res4.x servers using this string:

Provider=ASAProv.90;EngineName=sql[i]ServerName[/i];UID=custom;PWD=custom;Links=TCPIP(Host=[i]ServerIP[/i]);

Does anybody have the equivalent connection string to a Res5 server now that it uses sybase version 11? Also, the prereq for Res4 installed the Sybase drivers. Is this the same for Res5, and if not do you have any idea there i can get those drivers loaded?

Thanks.

 

[regilobo]

I'm not positive, but I believe [tt]SAOLEDB[/tt] is the provider name for v10+

(Please update this thread with a working string if you have a definitive answer - Thanks!)

 

[Moregelen]

Have you considered just taking the SQL Anywhere 11 dll from the server and registering it on the remote PC? I just had to do that at a site; they had a custom Excel file for their accountant that uses a VisualBasic back-end and I upgraded them from RES 4 to RES 5.

 

[pmegan]

I was going to try to figure that out as my next step; I'll need to register the SQL Anywhere dll in order to get the remote machine to connect. If you can share the steps and file names I'd appreciate it.

The main application I'm working with is an automated polling program. It runs early in the morning on a central server in our corporate office, connects to each of our Micros servers, (19 Res4.x systems), pulls sales data back and dumps it into flat files that are exported into various other systems. I'm trying to get it connecting to a test Res 5 server before installing our first one this summer.

 

[Moregelen]

Sure. I just had to spend all of yesterday recoding the alert manager smtp email dll to get the damn thing to work with a res 5 site..wish we had thought to try it on a lab before upgrading, but didn't even know they had it. I'll be back in the office at about 1 and will post the Dll and instructions. The hardest part was figuring out that they changed the damn connection string again.

 

[Moregelen]

Rather than just straight up listing a 1,2,3 and its setup I went ahead and wrote up the steps I took to figure out how to set it up. This should be something that works as a guideline for any version.

https://www.dropbox.com/s/3ob8rs7okgs9wjn/Setting up the Micros connection on a remote computer.pdf

 

[pmegan]

Thanks man, that's going to be a big help.

 

[Wildbar]

Very nice work!

 

[Moregelen]

No problem guys..
We do our polling the other way around (the sites post to us, rather than us polling the sites), so it was a bit easier for me because I could just use the local connection. Only have one site I had to do this for because of the darn Excel spreadsheet with the VB backend. Still, at least if I ever need to do it... plus it means that when I was figuring out how to do it I setup the connection on my 'Power PC' and no longer have to do my coding directly on a Micros machine for testing. That is a nice plus.

 

[pmegan]

I had the exports running that way for years as well. The data requirement change pretty often though, and with 19 restaurants currently live and a bunch of scheduled openings, remoting into each one to change/add export scripts or sprocs, and sometimes modify/add autosequences & external programs, is just getting too labor intensive.

 

[Moregelen]

Ah. I could see how that would be a problem. I'm actually a dealer, so once I have it set up, its done. If they want changes, they have to fork over the $$ to get it =)

 

[pmegan]

Yep, I'm the customer, different sides of the same coin. The company I work for owns a bunch of restaurants and has tons of custom work so it's worth it to have somebody in my position on staff.


Regilobo - here's the full connection string once you get the dll registered. If you're going to use an oledb connection instead of odbc you have to register dboledb11.dll.

Provider=SAOLEDB.11;EngineName=sql[i]<servername>[/i];UID=[i]<user>[/i];PWD=[i]<password>[/i];Links=TCPIP(Host=[i]<server ip>[/i])

One last thing, this will work if the remote PC is running XP even though the libraries were taken from a Win7 box.

 

[Moregelen]

If you're at all interested, this is part of the class library I've been reusing in my programs that have to connect to the database. I run the stored procedure without using the built in stored procedure mechanics because they have been a bit flakey on me, at least in the past. I also don't do any error checking within the class; I prefer to catch my exceptions in the main program so that I can access a LogWriter object.

using System;
using System.Data;
using System.Data.Odbc;
using System.Text;

namespace Postec.Micros.Database
{
    public class MicrosDatabase : IDisposable
    {
        private OdbcConnection _OdbcConnection;

        /// <summary>
        /// Constructs a MicrosDatabase object with the default custom,custom username and password; also assumes default DSN of micros has been configured
        /// </summary>
        public MicrosDatabase()
        {
            _OdbcConnection = new OdbcConnection("DSN=micros;UID=custom;PWD=custom");
            _OdbcConnection.Open();
        }
        /// <summary>
        /// Coonstructs a MicrosDatabase object using default DSN of micros using provided Username and Password
        /// </summary>
        /// <param name="Username">Database username to use for connection; using DBA account is NOT recommended</param>
        /// <param name="Password">Database user's password</param>
        public MicrosDatabase(string Username, string Password)
        {
            _OdbcConnection = new OdbcConnection(string.Format("DSN=micros;UID={0};PWD={1}", Username, Password));
            _OdbcConnection.Open();
        }
        /// <summary>
        /// Constructions a MicrosDatabase using the provided DSN. Connects using the provided Username and Password.
        /// </summary>
        /// <param name="Username">Database username to use for connection; using DBA account is NOT recommended</param>
        /// <param name="Password">Database user's password</param>
        /// <param name="DSN">The DSN configured to connect to micros on the machine. See SysWOW64 folder for connection manager</param>
        public MicrosDatabase(string Username, string Password, string DSN)
        {
            _OdbcConnection = new OdbcConnection(string.Format("DSN={0};UID={1};PWD={2}", DSN, Username, Password));
            _OdbcConnection.Open();
        }

        /// <summary>
        /// Returns a DataTable containing the results from the database query provided.
        /// </summary>
        /// <param name="Cmd">Database query to run against the database</param>
        /// <returns>DataTable containing SQL result set</returns>
        public DataTable Query(string Cmd)
        {
            using (OdbcCommand OdbcCmd = new OdbcCommand(Cmd, _OdbcConnection))
            using (OdbcDataReader OdbcReader = OdbcCmd.ExecuteReader())
            {
                if (!OdbcReader.HasRows)
                    return null;

                DataTable tempTable = new DataTable();
                DataColumn tempColumn;
                using (DataTable schemaTable = OdbcReader.GetSchemaTable())
                {
                    for (int i = 0; i < schemaTable.Rows.Count; i++)
                    {
                        tempColumn = new DataColumn((string)schemaTable.Rows[i]["ColumnName"], (Type)schemaTable.Rows[i]["DataType"]);
                        //have to do this because numeric fields can be null in the micros database
                        tempColumn.AllowDBNull = true;
                        tempTable.Columns.Add(tempColumn);
                    }
                }

                DataRow tempRow;
                while (OdbcReader.Read())
                {
                    tempRow = tempTable.NewRow();
                    for (int i = 0; i < OdbcReader.FieldCount; i++)
                        tempRow[i] = OdbcReader[i];
                    tempTable.Rows.Add(tempRow);
                }
                return tempTable;
            }
        }

        /// <summary>
        /// Runs a stored procedure
        /// </summary>
        /// <param name="SPName">The name of the stored procedure; full name. Eg: micros.sp_PurgeHistory</param>
        /// <param name="SpParams">The parameters, if any, that the stored procedure requires</param>
        /// <returns>DataTable containing SQL result set</returns>
        public DataTable RunStoredProcedure(string SPName, SPParam[] SpParams = null)
        {
            if (SpParams == null || SpParams.Length < 1)
                return this.Query(string.Format("call {0}", SPName));

            StringBuilder sb = new StringBuilder();
            sb.Append("call ").Append(SPName).Append("(").Append(SpParams[0].ToString());
            for (int i = 1; i < SpParams.Length; i++)
                sb.Append(",").Append(SpParams[i].ToString());
            sb.Append(")");

            return this.Query(sb.ToString());
        }

        /// <summary>
        /// Executes a command against the database for which no return is expected.
        /// Typically used for UPDATE, INSERT, and DELETE statements
        /// </summary>
        /// <param name="Cmd">Command to run agaisnt database</param>
        /// <returns>The number of rows affected by the command.</returns>
        public int Execute(string Cmd)
        {
            using (OdbcCommand OdbcCmd = new OdbcCommand(Cmd, _OdbcConnection))
            {
                return OdbcCmd.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// Attempts to Reopen the database connection
        /// </summary>
        /// <returns>If the connection is now open</returns>
        public bool ReopenConnection()
        {
            if (Connected)
                return true;
            _OdbcConnection.Open();
            return Connected;
        }

        /// <summary>
        /// Returns if the current Database Connection is open
        /// </summary>
        public bool Connected
        {
            get
            {
                return _OdbcConnection.State == ConnectionState.Open;
            }
        }

        /// <summary>
        /// Calls dispose
        /// </summary>
        public void Close()
        {
            this.Dispose();
        }
        /// <summary>
        /// Calls Dispose(true)
        /// </summary>
        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }
        /// <summary>
        /// Clean up of database connection is performed here
        /// </summary>
        /// <param name="disposing"></param>
        protected virtual void Dispose(bool disposing)
        {
            if (disposing)
            {
                if (_OdbcConnection != null)
                {
                    _OdbcConnection.Dispose();
                    _OdbcConnection = null;
                }
            }
        }
    }
}

 

[Moregelen]

That's written in the .net 2.0 for compatibilities sake. I have to develop for things that are ooooold at times.

 

[Moregelen]

Out of curiosity, are you aware of the changes between res 4 and 5? Things like the divergence from having the database backups in auto-sequences, instead triggered by the windows 7 task scheduler via v5maint.exe? They aren't huge, but they can sure make upgrading a res 4 database a paint.

And FYI, 5.0 and 5.1 both had a lot of bugs, but the recent RES 5.1 MR 2 release fixes a whole lot more than the doc says it fixes; in fact, most of the bugs we have been tackling since the change over went away.

 

[pmegan]

That's kind of funny. I wrote a library that's very similar in vb.net, but I'm using OLEDB connections to the Micros servers. It makes it a lot easier to add new locations that way. I have a small database on one of our SQL Servers that holds all the restaurant names, Micros server names, Micros server IP addresses and an active flag (in the event we close a location). Rather than adding ODBC connections on all the PCs running the exports, the class just fills a datatable with the Micros servers info and returns it as a parameter so the user can pick and choose from the list.

I did put error handling in, but the traps just call an error event that's handled by a logging class. This is mainly so that I can handle individual data exceptions without importing all the data namespaces into every program. Both support classes are initiated as singletons, so regardless of where the data class is called from, the logging class handles the errors.

Now that we're going to be installing Res 5 I'm testing a procedure that calls a WMI query to get the MICROS_Current_Installation system variable from the Micros server and using the result to decide which connection string to use.

Sure beats editing touchscreens and menu items.

 

[Moregelen]

I'm sure I would have gone your way if I had to poll a bunch of restaurants (with possibly dynamic lifetimes). Typically though I'm only dealing with places with maybe 4-5 restaurants; the software is also almost always on the same networking pushing data out rather than pulling it in.

I admit that I have a class that inherits my MicrosDatabase class that has all of my functions in it; I just wanted a layer of separation on the actual database functions. Things like querying currents sales data, labor data, etc, into usable formats (most of what I end up dealing with is Labor and Sales exports for specific companies like Oasis). It has event hooks in it for when things blow up rather than just letting the exception slip past. I end up installing quite a few of these as active services where everything has to be automated; can't afford for the service to crash. I haven't had to start adding in watchdog services yet, but it is something I've had to seriously consider in the past...

Let us know how that goes; always interested in what other people are doing with the .net applications on micros.
So far everything has worked between micros versions; run into anything yet that caused problems because of table structure changes?

 

[Moregelen]

... and now I feel compelled to start rewriting the class library to include OleDB connection objects.. heh

 

[pmegan]

I finally just got Res 5 loaded, so haven't had a chance to play with it yet. I read through the release notes but will probably go through them again with a working server in front of me to see what I can break. I like the idea of using windows scheduler for the backup, this way if the autosequences hang up for some reason there's still a backup. The big pain is going to be sorting through all the custom procedures and views to make sure they all still work. I think my big saving grace will be that I've never used "Select * From...", I always list all the fields, even if I'm selecting the whole table.

And thanks for the heads up on the bugs. I'd probably go with the latest MR anyway but it's good to know that they have a lot of the bugs worked out.

 

[pmegan]

"... and now I feel compelled to start rewriting the class library to include OleDB connection objects.. heh"

Well, at least you have the connection string part out of the way, haha