SQL Command to View all tables in MAS200 via ODBC

[HiTechFred]

Hey Everyone,

I'm trying to write a mini Perl app that will show a list of all tables in MAS200.

I have made lots of apps to grab data through the SOTAMAS90 ODBC driver (ver 3.21). Here's what I have tried as my SQL statement all I get is an error:

DBD::ODBC::db prepare failed: [ProvideX][ODBC Driver][FILEIO]Table is not access
ible (SQL-S000)(DBD: st_prepare/SQLPrepare err=-1) at C:\Documents and Settings\
XXXXX\Desktop\show_all.pl line 38.

There are the SQL Sommands I have tried:

1. SHOW ALL
2. SHOW ALL FROM *
3. SHOW TABLES
4. select * from sysobjects
5. SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' ORDER BY TABLE_NAME
6. SELECT * FROM *

So far nothing, anyone help?

 

[dgillz]

In MS SQL Query Analyzer, select * from sysobjects will do the trick -- for ANY database. The message you are getting sounds like there is a permissions error in performing that simple query.



Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[Blorf]

The Mas 200 3.X non SQL ODBC is kinda Funky.

To do this, I linked all the tables using Access, then copied the table names from the Access MySysObjects table.

ChaZ

There Are 10 Types Of People In The world:
Those That Understand BINARY And Those That Don’t.

 

[HiTechFred]

Thanks guys for the input.

I don't think its a permissions error because I used to get this error when I tried to make a simple C# app that connected to MAS200 and view contents of a table. It turned out that it was VERY picky of the connection string.

I will try the suggestions, but I have to raise a point. When I go into Crystal Reports 8.5 and select the DSN and then select the tables I'm able to see all of them via Crystal, so I'm thinking there is a way to do it since Crystal Reports does thru ODBC.

 

[BigLouie]

Your problem is that in the 4.xx versions of MAS there are virtual tables. Tables that do not exist until a report is run. So if you do an ODBC call for ALL tables you will error out with a message about tables not being found because the virtual table cannot be found.

 

[HiTechFred]

I actually got this working with perl, my next venture will be with C#. From my googling I see it might be calling a stored procedure called SP_TABLES, but I have no idea at this point.

Theres a function with the DBI module called "table_info()". I'm not sure what it does behind the scenes, but it returns ALL the table names in an array. Here's my code if anyone is interested. I'm happy I got this working with Perl so C# is next.

##### CODE ######

use strict;

use DBI;

my $user = 'XXX';
my $password = 'XXXXX';
my @array;
my $dbh;
my $sth;

Connect();
GetTables();
Disconnect();

#------------------------------------------

sub Connect() {

$dbh = DBI->connect('dbi:ODBC:mas200_DSN', $user, $password, {odbc_version =>3})
or die $DBI::errstr;

print "Connected ...\n";
}

#------------------------------------------

#------------------------------------------
sub GetTables
{

$sth = $dbh->table_info();

while( @array = $sth->fetchrow_array() ) {
print "$array[2]\n";
}
}

#------------------------------------------
sub Disconnect() {

$dbh->disconnect();
print "Disconncted ... \n";

}

 

[HiTechFred]

We aren't on 4.XX we still are on 3.71 with hopes to move over to 4.05 by Feb 06.

 

[afarinpour]

I'm working with MAS90 ver9.0 (not the SQL version) and am trying to pull data from three tables use the data to populate a Excel Worksheet. Below is the code I am using;

Private Sub cmdForcast2005_Click()

'Variable settings
Dim ws As Workspace
Dim MAS90db As DAO.Database
Dim MAS90im As DAO.Recordset
Dim MAS90arn As DAO.Recordset
Dim MAS90aro As DAO.Recordset
Dim sqlIM As String
Dim sqlARN As String
Dim sqlARO As String

'Create a default workspace Object
Set ws = CreateWorkspace("", "", "", dbUseODBC)

'Create a Database object
Set MAS90db = ws.OpenDatabase("ODBC;DSN=SOTAMAS90;Company='Valrhona(001)';UID='arya';password='hunter'")

'Create a Recordset object
'ARN table - holds invoice information
sqlARN = "SELECT * FROM ARN_InvHistoryHeader"
Set MAS90arn = MAS90db.OpenRecordset(sqlARN)
'ARO table - holds invoice detail
sqlARO = "SELECT * FROM ARO_InvHistoryDetail"
Set MAS90aro = MAS90db.OpenRecordset(sqlARO)
'IM Master Table - holds item weight
sqlIM = "SELECT * FROM IM_90_UDF_IMMasterfile"
Set MAS90im = MAS90db.OpenRecordset(sqlIM)


For i = 6 To 20
loc1 = "D" & i
loc2 = "C" & i
Range(loc1).Value = MAS90im!Weight
Range(loc2).Value = MAS90im!ItemNumber
MAS90im.MoveNext
Next i

End Sub

Whan I run the code I get a Run-Time error '3146' ODBC call faild. Code stops at the MAS90im.MoveNext.....

Anyone have an idea as to why this is happening?..thanks

Arya

 

[Blorf]

You set a reference to ADO in excell, lest it would not compile.

Is there a change that a .movefirst will solve this?

ChaZ

There Are 10 Types Of People In The world:
Those That Understand BINARY And Those That Don’t.