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.

  • 0
    Here is a big hint. Open Crystal, Based a new report on AR_Customer. Pull the fields you want into the Details section. Go to Select Expert and filter so State = MN. Next go to Database, Show SQL Statement and copy the SQL statement to your query.
  • 0
    Note also that this: 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

    Should be this: DSN=MAS90;UID=sdobbins|CCM;PWD=xxxxx Directory=\\ccmapp02\M90w\MAS90;SERVER=NotTheServer
  • 0 in reply to BigLouie

    BigLouie:

    First, thanks for your response. I think you misinterpreted my situation. I have written an application program in LabVIEW to query the data directly from MAS 90 using the ProvideX ODBC system DSL connection. There is no reporting tool, Crystal Reports or otherwise, involved. In effect, my LabVIEW application is the reporting tool. The LabVIEW application queries data from other sources (DSLs that point to Access DBs) and that works fine. From the standpoint of the application, it shouldn't care what the data source is: MAS 90, Access, etc. I believe that was the whole point of implementing the ODBC layer, so application programs could seamlessly see data, regardless of the source.

  • 0 in reply to Scott Dobbins

    And I am saying that your SQL statement should look like this:

    SELECT "AR_Customer"."CustomerNo", "AR_Customer"."CustomerName", "AR_Customer"."AddressLine1", "AR_Customer"."AddressLine2", "AR_Customer"."City", "AR_Customer"."State", "AR_Customer"."ZipCode"
    FROM "AR_Customer" "AR_Customer"
    WHERE "AR_Customer"."State"='MN'

    If you are using the SOTAMAS90 DSN or a DSN based on the MAS 90 ODBC files.

  • 0 in reply to BigLouie
    BigLouie:

    I tried your SQL statement. It produced no errors, but produced no data either. In other words, it behaved exactly like my original query. Most example queries I have seen do not have double quotes around all the identifiers. Why add clutter and waste time by putting them there? I don't see the advantage. Also, the table name does not need to precede each column name, if each column is drawn from the table identified in the FROM clause.
  • 0 in reply to Scott Dobbins
    Scott the suggested method of creating the correct SQL statement has been first create a report in Crystal based on a single table and the pull fields into the detail section and then copy the SQL statement. This method produces the correct SQL statement for the SOTAMAS90 DSN and any DSN based on the MAS90 ODBC files. Sage has suggested this since the program first became ODBC compliant way back in MAS90 v3.0 days.
  • 0 in reply to Scott Dobbins
    I will note that what some do when having an issue such as this is to create an Access database. Create SQL Specific Pass Through queries and then have your program use Access as the data source.
  • 0 in reply to BigLouie
    Any chance it's as simple as having no customers in the Minnesota?
  • 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.
  • 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.