SQLAlchemy is generally a wonderful ORM and database toolkit for Python. Personally, I think it's the best one in the Python landscape. However, there are some things that tend to be a little troublesome.

Some of these are simply due to the difference between a relational database and an object oriented language. Others, however, are due to RDBMS specific quirks.

The problem I encountered recently was how to call a stored procedure in MS SQL from SQLAlchemy. In general, the problem isn't a hard one. One can simply import func and be on their way.

from sqlalchemy import func

sess.query(func.sum(TableObject.column)).first()

However, func is geared towards SELECT based functions. For example, the above would be roughly translated into:

SELECT sum(column) FROM TableObject;

Now, MS SQL has stored procedures that return values in the parameters. For example:

DECLARE @noteidx AS NUMERIC(19,5), @err AS SMALLINT;
exec DYNAMICS.[dbo].[smGetNextNoteIndex] dbid, sessid, @noteidx OUTPUT, @err OUTPUT;

This will perform some internal magic and afterwards, @noteidx will hold the next index, and @err will hold whatever error number was encounterd, if any.

This doesn't translate well into SQLAlchemy's idiom of SQL function calling. Though I don't believe it's all SQLAlchemy's fault. It appears that there is  some  support for out parameters in Oracle via SQLAlchemy. Additionally, it appears that PyODBC is lacking  support for out parameters.

However, not willing to roll over and die, there is a solution to be had.

Since SQLAlchemy contains sufficiently quantities of awesome, the solution is only slightly hackish. SQLAlchemy easily allows for the running of straight SQL using the text function. The solution to the above problem:

def get_next_note_index(s=None):
    """ Returns the next note index to use.
    
    Calls the `smGetNextNoteIndex` stored procedure to handle the query
    and increment of the company master NOTEINDX

    If a session object is passed, it will use the one specified.  Otherwise
    it will call get_session() to get a session to use.
    """
    txt = """
        SET NOCOUNT ON;
        DECLARE @db AS CHAR(5), @id AS SMALLINT, @noteidx AS NUMERIC(19,5), 
                @err AS INT;
        SELECT @db=CMPANYID 
          FROM DYNAMICS.[dbo].[SY01500]
         WHERE INTERID = DB_Name();
        SELECT @id=@@SPID;
        EXEC DYNAMICS.[dbo].[smGetNextNoteIndex] @db, @id, @noteidx OUTPUT,
                                                 @err OUTPUT;
        SELECT @noteidx, @err;
        SET NOCOUNT OFF;"""[1:]
    s = s and s or get_session()
    r = s.execute(text(txt)).fetchall()
    s.commit()
    return r[0][0]

The few special things to note about the above are:

• Since there is no direct support for output parameters, one needs to SELECT the values to get them via SQLAlchemy
• Due to all sorts of PyODBC/FreeTDS/*nix/SQLAlchemy  nastiness one needs the SET NOCOUNT ON to keep SQLAlchemy from barfing.

In the end, however, it works nicely. Thank you SQLAlchemy. Also, thank you  MichaelJ2 for the TSQL code to translate.

Digging through Dynamics: GP stored procedures is painful, to say the least.

When you pull the SQL out of the database via SQL Server Management Studio it is one LONG line. No formatting, nothing. Reading the TSQL as one line isn't really an option. Reformatting it by hand takes FOREVER.

Fortunately, there is a wonderful online formatter over at  TSQLTidy. It can transform the GP stored procedures into something that can actually be parsed by a human brain.  TSQLTidy also has a  web service. Theoretically this is a  SOAP interface that will allow the creation of scripts/programs that can then use  TSQLTidy for its formatting. I've been trying to get it to work via  Python using  ZSI. However, I have had no such luck yet. Eventually.

Having recently upgraded from GP8 to GP10 and having switched from Horizons Manufacturing to GP: Manufacturing, I wanted to lay out a few do's and don'ts for anyone else considering a switch. Hopefully the below will save you some of the headache.

1. Take The Defaults: As with pretty much all Microsoft products, accepting the defaults is prudent and nigh a necessity. If you simply take the defaults and use the software exactly as designed, you'll have an acceptable experience.
2. Do not customize: This may seem to be the same as above. It kind of is. However, it's more than that. Great Plains can be customized simply through the various settings and incomprehensible menus it offers. However, you can really customize Great Plains with VBA and/or Dynamics code. Don't. Just Don't. No, really, DON'T!
3. Do not integrate 3rd Party Products: They're not worth it. Great Plains is a black box. Even to Microsoft. You can't know what's it's doing. It's one of the universe's great mysteries.
4. Do not roll-your-own Product: If you are masochistic and really enjoy pain, go right ahead. We have specific manufacturing needs. One of which is integration with our hand-held barcode scanners. We are now on the second generation of hand-held software. The first was abominably designed. The second has a decent architecture, if you exclude the whole Great Plains part. As stated above, no one knows the innards of Great Plains. It's like a  Mord-sith. Once you start looking and turn your power over to it, it makes you writhe in pain forevermore. Doing a quick count, I turned up 1,988 tables in my Great Plains database. This excludes the tables it uses in the DYNAMICS database as well as master. It also does not take into account the hundreds of temporary tables it creates during normal usage. 1,988 tables! That's insane. One can not understand Great Plains. The whole concept of understanding is anathema to Great Plains.
5. Don't Use Lots of Work Centers: If you see yourself using lots of work centers, you're doing it wrong. Especially if you have lots of items. This can be partially mitigated as long as an item only has a small set of possible work centers. If you're like us, with >900 work centers and >6,000 items, you're simply in for a long wait.

I hope that these tips will help. If you're already a Great Plains user, and are considering upgrading, ask yourself if it's really worth it. It's probably less painful to upgrade than switch to a new system. It isn't, however, pain-free.

I am not a Great Plains expert. I'm just a poor hapless IT manager thrown into the woes of maintaining, upgrading, supporting, and developing against Great Plains. If you know of a true Great Plains expert, please let me know. My requirements for a Great Plains expert are as follows:

• Knows a majority of the Great Plains tables by their physical name
• Knows what these tables do and how they are related
• Proficient at reading TSQL
• Proficient at maintaining and managing MS SQL Server
• Knows what is happening behind the scenes for a majority of the windows in the GP interface
• Can explain why Microsoft decided to go against most every UI interface and interaction convention in existence
• Can explain why Dynamics presents you with the most misleading lists and scroll bars ever created (Since when do lists start in the middle with no indication that there are actually items above the one at the top of the list?)
• Can explain why Great Plains lets you do things that break the consistency of your data
• Can explain why more operations in Great Plains aren't atomic.
• Is not content with simple answers and must understand the why behind the answer.

If you are, or know of some one that fits the requirements, please contact me.

By the way, if you want to know how to list the number of tables in a MS SQL database, here you go:

select count(*) as TableCount from Information_Schema.Tables where Table_Type = 'BASE TABLE';