Technical and Installation Discussions

[viperbri]

Hey...my first post on the new boards...I am liking the new boards!  Anywhoo...

I am assisting a client who has created a bin location lookup page using their intranet (ASP page) and it uses MySQL to store the data.  We are currently exporting the IM2 table to a text file and then he is manually importing it into the mySQL table.  

We want to try and streamline/automate this process so I created a VI Export job to try and write to the table via the MySQL ODBC drive.  It seems to work as far as connecting but when I try to get it to write to the tables I get an Error 15 SQL error.  I checked field sizes and masking and it all seems to work just fine.

Has anyone had any with MySQL and VI Exports?

[BigLouie]

VI? You want this to finish sometime in their life time don't you. Why not just pull? I could pull the IM2 ouf of MAS in about 30 seconds with either Access or Excel. I have not worked with MySQL but if it is anything like Access you could just write queries and pull the data out of MAS is a flash.

[viperbri]

Thanks Louie.  I would like to do that.  I just can't seem to find a good ODBC connect string for the MAS 4.05 version that works.  I keep getting table inaccessible errors when we tried pulling that.

[mas90guru]

I would echo Louies comments. It's been a long time since I've used VI to export anything.

I'm confused by your explanation of what you're trying to do because it sounds like you're trying to append/write to a MYSQL out of MAS VI?

I haven't heard of that being done but there's third party stuff that  could help like In-Synch or SQL Mirroring -- but I'm guessing that's overkill and the best way is to continue in some type of two step (export from MAS , import to MySQL) process that's automated with scripts in some way.

[viperbri]

Thanks Wayne.  That is exactly what we ended up doing.  I do like the SQL mirroring (DSD, right?) but that was overkill for this small project.

Here is what we did: We are exporting the data from MAS200 via ODBC to a csv file, running an automated script to import into MySQL table every 5 minutes.  Been running for a couple days without a hitch.

Ideally I would like to do an ODBC read from MAS200 and write it to the table in MySQL without have to use the export to file, import from file.  I guess I just need to educated myself further on MySQL.  I see lots of potentional there for web apps that query MAS data.

[clarkw]

I've been trying to get the ODBC link from MAS 90 to My SQL, but without success. I'd be interested to know if you ever get it to work cleanly. It has lots of potential.

Thanks - Clark

[clearis]

I suppose you use the tool you're familiar with and mine is vbscript. I've found that pulling data from MAS 90/200 is significantly faster than using VI. Here is a VB script to pull data from the AR_Customer table: 

' Create Flat File of Customer Information for importing into MySQL
Const ForWriting = 2Const Path = "C:\"
Dim objFSO, objFile, strFileName, strRecordSet2ttx'--- Setup export file ---
strFileName = Path + "ar_customer.ttx"
Set objFSO = CreateObject("Scripting.FileSystemObject")
If objFSO.FileExists(strFilename) Then
   Set objFile = objFSO.OpenTextFile(strFilename, ForWriting, False)
Else
   Set objFile = objFSO.CreateTextFile(strFileName)
End If' --- Setup the connection to the ODBC Driver and login ---
strConnectionString = "DSN=SOTAMAS90;UID=MGS|ABC;PWD=12345"
Set objMASConnection = CreateObject("ADODB.Connection")
objMASConnection.Open strConnectionString
Set rsCustomers = CreateObject("ADODB.Recordset")

 ' --- SQL statement to retrieve customers and get recordset of customers ---
Sql_Stmt = "SELECT * From AR_Customer"
rsCustomers.Open Sql_Stmt, objMASConnection
rsCustomers.MoveFirst' --- Send retrieved data to tab delimited text file ---
While Not rsCustomers.EOF
   strRecordSet2ttx = ""
   For Each field In rsCustomers.Fields
      strRecordSet2ttx = strRecordSet2ttx & rsCustomers(field.Name) & vbTab
   Next
   'Write data out, but remove extra tab
   objFile.WriteLine(Left(strRecordSet2ttx,Len(strRecordSet2ttx)-1))
   rsCustomers.MoveNext
Wend' --- Clean Up ---
objFile.Close
Set objFSO = Nothing
Set rsCustomers = Nothing
Set objMASConnection = Nothing 

The script uses the generic connection string for MAS and has the password in the script. You can  preserve your security by creating a "silent DSN" or setting up a user in MAS just for ODBC queries. The script logic is pretty generic. Just substitute the table name and the appropriate file. You could even set up an array of table and file names and have the script pull multiple tables. The fastest way I've found loading data into MySQL in using the LOAD Data command. I've loaded over 47,000 customer records (all fields) into a MySQL table in under 30 seconds (done locally on my notebook). The approach using vbscript is as follows: '*** Script to update MySQL tables. ***
Const sQ = "'"
Const Path = "C:/"
Dim strFile, strTable, strSQL, strSQL1, strSQL2, strSQL3' --- Setup string constants ---
strSQL1 = "LOAD DATA INFILE " + sQ
strSQL2 = sQ + " INTO TABLE "
strSQL3 = " FIELDS TERMINATED BY " + sQ + "\t" + sQ + " LINES TERMINATED BY " + sQ + "\r\n" + sQ + ";"strTable = "ar_customer"
strFile = Path + "ar_customer.ttx"' --- Setup the connection to the ODBC Driver and login ---
strConnectionString = "DSN=Local_MySQL"
Set cnConnection = CreateObject("ADODB.Connection")
Set cmMySQL = CreateObject("ADODB.Command")
cnConnection.Open strConnectionString
cmMySQL.ActiveConnection = cnConnection' --- Create SQL commands to TRUNCATE existing tables and LOAD DATA from file ---
cmMySQL.CommandText = "TRUNCATE TABLE " + strTable + ";"
cmMySQL.Execute
strSQL = strSQL1 + strFile + strSQL2 + strTable + strSQL3
cmMySQL.CommandText = strSQL
cmMySQL.Execute

Set cmMySQL = Nothing
cnConnection.Close
Set cnConnection = Nothing

  Substitute the DSN for the MySQL server you have set up. As of 11/11/2008, the 3.51 MySQL ODBC is working best as the newer drivers still have some issues. The script created truncates the table and loads the data. The script assumes that the ar_customer table has been setup in the database specified by the DSN. The LOAD DATA command follows the MySQL syntax and provides the source file, destination table and specifies that the records are delimitated by a tab and line terminated by a return carriage and the new line character (enter for those in the Windows world). Again this script could be modified have a two dimensional array holding tables and file names. Thus, multiple tables can be done at once. 

(Note: The HTML show emotion characters. I didn't edit them.

Message Edited by clearis on 11-11-2008 08:59 PM

[davida19]

I recently did something very similar, I connected to MAS90 via ODBC, using C#. I will post the code it could probably help you.

// ODBC Connection

OdbcConnection conn = new OdbcConnection();

conn.ConnectionString = "DSN=SOTAMAS90;UID=da|ABC;PWD=da001;SERVER=NotTheServer";

//ODBC Command to execute Query

OdbcCommand comm = conn.CreateCommand();
                comm.CommandText = "SELECT AR_Customer.CustomerNo, AR_Customer.CustomerName, AR_Customer.AddressLine1 FROM AR_Customer";

OdbcDataAdapter da = new OdbcDataAdapter(comm);

da.Fill(dataset, "AR_Customer" ) ;

// the dataset will feed a datatable.

Hopefully this can help you.

[francisco]

Hello Clearis.

I am treying to retrieve data from MAS90 using excel and the driver SOTOMAS.

The data that I am looking for is basically costumer order information. The problem that I am having is that if the order numbers are consecutive, the query run ok and fast, but when the orders that

i am looking for are not consecutive (ie. 740800, 740801, 740802, 740875, 740924, 740959) the query takes to long that I have to shot down the query.

Will there be another way to retrieve the data in a faster way?

or is it that I am dooing something wrong?

If you can help me, I'll really appreciate it.

Thanks

Francisco

[hrichards]

I use VI to export to mysql hourly using one pass.

I was getting many errors when I first started until I editted the mysql query string in the mas vi job.

Here is my copy of it. This is my vijob sql query file contents located in the mas vi folder.  This was the booger that mas always messed up.  You have to look at the log file on the root drive called myodbc.txt to find out what the error was then go to this file and fix it.  A tip I have for anyone trying this.  Once you get this job to work, don't modify it.  Add all the columns you will ever need and then just change the mysql statement below to tweak it to your usage. 

replace INTO
info
(sales_number,
customer_number,
shiptoname,
shiptoaddress1,
shiptoaddress2,
shiptoaddress3,
shiptocity,
shiptostate,
shiptozipcode,
shipvia,
shipweight,
confirmto,
tracking,
grosscost,
customerpo,
comments,
email,
inserted)
VALUES
('0001',
'0002',
'0003',
'0004',
'0005',
'0006',
'0007',
'0008',
'0009',
'0010',
'0011',
'0012',
'0013',
'0014',
'0015',
'0016',
'0017',
'0018')

Here is my myodbc log of the entry

-- Query logging
--
--  Driver name: MySQL ODBC 5.1 Driver  Version: 05.01.0005
-- Timestamp: 100511  8:45:14

replace INTO info (sales_number,customer_number,shiptoname,shiptoaddress1,shiptoaddress2,shiptoaddress3,shiptocity,shiptostate,shiptozipcode,shipvia,shipweight,confirmto,tracking,customerpo,email) VALUES('0118239','5557553','Amet','5 Ave','609-555-5555','','Manahawkin','NJ','08050','','00004','Domin','','','');