MAS 90 Data Access Problems with ODBC

MAS 90 version = 4.30.0.23
ProvideX driver version = 4.21.1000

I am attempting to query MAS 90 data using the ProvideX ODCB driver.  My queries are running, but no data is returned.

1) MAS 90 has been set up with a role that gives me read access to all tables I'm querying.

2) I created a 32 bit system DSN called MAS90, identical to the default user DSN SOTAMAS90.

3) My connection string for the driver:
DSN=MAS90; Directory=\\ccmapp02\M90w\MAS90; Prefix=\\ccmapp02\M90w\MAS90\SY\, \\ccmapp02\M90w\MAS90\==\; ViewDLL=\\ccmapp02\M90w\MAS90\Home\; LogFile=\PVXODBC.LOG; CacheSize=4; DirtyReads=1; BurstMode=1; StripTrailingSpaces=1; SERVER=NotTheServer

4) My connection string logon:
DSN=MAS90;Company=CCM;UID=sdobbins;PWD=xxxxx
successfully connects to the database.

5) Through the LabVIEW application program I have written, I can successfully query a list of tables in MAS 90, as well as the field names in any given table.

6) When I run a simple query, I get no error message, just zero rows returned (no data).  Seems there is a setting of some sort that I failed to configure, either in my application or in the DSN or in the MAS 90 permissions.  I can completely remove the WHERE clause from the query, and still no data is returned.  I know there are thousands of records in the table.

7) This is the query:

SELECT AddressLine1, AddressLine2, City, State, ZipCode
FROM AR_Customer
WHERE State = 'MN'
ORDER BY ZipCode



Does anyone know why my query would run, not error out, yet return no data?  Thanks all.

Parents
  • 0
    A few suggestions for you to try... Does your data source work properly if you try to use it in say Excel to grab data from Sage? Check this by going to Excel>Data>Get External Data>From Other Sources>From Microsoft Query>Choose your data source

    My other suggestion is just using the standard ODBC driver (SOTAMAS90) with a connection string within your SQL. Commonly referred to as a passthrough query.

    For instance here is one I use in Access that gives me Item Code and Item Type from CI_Item.

    SELECT CI_Item.ItemCode, CI_Item.ItemType
    FROM CI_Item IN '' [ODBC;DSN=SOTAMAS90;UID=youruserid|SMC;PWD=yourpassword;Directory=\\x.x.x.x\mas90;Prefix=\\x.x.x.x\mas90\SY\, \\x.x.x.x\mas90\==\;ViewDLL=\\x.x.x.x\mas90\HOME;LogFile=\PVXODBC.LOG;CacheSize=4;StripTrailingSpaces=];

    Your will obviously need to replace the "youruserid" with your own user id, the "yourpassword" with your password and the x.x.x.x's with the server's real IP address. This connection string also assumes that your network share for Sage is \\x.x.x.x\mas90.
Reply
  • 0
    A few suggestions for you to try... Does your data source work properly if you try to use it in say Excel to grab data from Sage? Check this by going to Excel>Data>Get External Data>From Other Sources>From Microsoft Query>Choose your data source

    My other suggestion is just using the standard ODBC driver (SOTAMAS90) with a connection string within your SQL. Commonly referred to as a passthrough query.

    For instance here is one I use in Access that gives me Item Code and Item Type from CI_Item.

    SELECT CI_Item.ItemCode, CI_Item.ItemType
    FROM CI_Item IN '' [ODBC;DSN=SOTAMAS90;UID=youruserid|SMC;PWD=yourpassword;Directory=\\x.x.x.x\mas90;Prefix=\\x.x.x.x\mas90\SY\, \\x.x.x.x\mas90\==\;ViewDLL=\\x.x.x.x\mas90\HOME;LogFile=\PVXODBC.LOG;CacheSize=4;StripTrailingSpaces=];

    Your will obviously need to replace the "youruserid" with your own user id, the "yourpassword" with your password and the x.x.x.x's with the server's real IP address. This connection string also assumes that your network share for Sage is \\x.x.x.x\mas90.
Children
  • 0 in reply to Jon_K

    Blorf, Jon_K:

    Thanks to you both for offering suggestions.  I found out that I had an error in my application program (LabVIEW) that prevented data from being fetched.  I changed a boolean, and all is working fine now.  It's great to know that the ODBC driver allows external applications to directly query data from the underlying DB.

    The connection string in my system DSN is

    DSN=MAS90; Directory=\\ccmapp02\M90w\MAS90; Prefix=\\ccmapp02\M90w\MAS90\SY\, \\ccmapp02\M90w\MAS90\==\; ViewDLL=\\ccmapp02\M90w\MAS90\Home\; LogFile=\PVXODBC.LOG; CacheSize=4; DirtyReads=1; BurstMode=1; StripTrailingSpaces=1; SERVER=NotTheServer

    The connection string I use in my application program to log in is

    DSN=MAS90;Company=xxx;UID=xxx;PWD=xxx

    Where xxx is the appropriate entry.

    This is a block diagram of my LabVIEW application program.  It works.