OT: MySQL - Access SELECT INTO....

[stanl]

Not a WB problem, but part of an compiled script.

I have a compiled script that creates a 'snapshot' of agent activity from 2 call centers based on querying live MYSQL data, but relating it to local Access lookup tables to eventually create a multi-tabbed workbook.

The centerpiece of the application is SELECTING the MySQL data into a base Access table. If I manually execute a pass through query the data is transferred quickly, but the compiled script which uses ADO cannot execute a pass-through directly, so I use the basic syntax

Code Select Expand


SELECT  [Fields] INTO [Access Table]
FROM [Tables  IN "" [ODBC;Driver={MySQL ODBC 5.2 ANSI Driver};Server=;Database=;User=;Password=;Option=3;]
WHERE [];


This, however, runs 10 times slower than using a pass through, although both use the same driver.

So, I thought about reversing and SELECTING directly from MySQL. It appears MySQL does not support SELECT INTO... but INSERT INTO... SELECT - so I tried

Code Select Expand


INSERT INTO [Access Table] IN '' [MS Access;DATABASE=] SELECT [MySQL Fields] FROM [MySQL Table(s)];


But get an error that the [Access Table] is an undeclared variable.

I have a config file with parameters to determine whether to DROP the base Access file for SELECT INTO, or DELETE rows for INSER INTO; therefore I can test either method.

The exe will eventually go to other users so I would like to nail down and correct the error so I can test which is ultimately a better choice. 

[JTaylor]

Hmmmmm...haven't tried this before but was playing around to see what happens.   Running into some roadblocks though.   I'm not finding that "IN" statement as an option for MYSQL.  Also, I can only find examples of passthrough queries using Access as the front end option.  If you email me the code you are using, minus the logins/passwords stuff of course, I'll see if I can figure something out.   Just not finding a good starting point going that direction.

I assume you have tried putting double-quotes around it so that MySQL knows it is an object of some type?  Not sure if that would help or not.

Jim

[stanl]

I assume you have tried putting double-quotes around it so that MySQL knows it is an object of some type?  Not sure if that would help or not.

Jim

Thanks for looking. I tried that and tried removing the IN and using

INSERT INTO [MS Access;DATABASE=].[Access Table]  SELECT ......

But keep getting syntax errors. I had used similar syntax with Office 20013 to move data between SQL Server and Access. The alternative would be to try using ADOX to delete and re-build the pass-through query and DAO to execute, but I understand that MS is phasing out DAO with Office 2013, saying use ADO for queries.

As I said, the original code SELECTING INTO with MySQL as the ODBC source works but is so much slower than running the pass-througn directly from Access.

[JTaylor]

Not sure what all Access allows as I do very little with it but any viable options if you were to pull the data via GetString() or GetRows() from MySQL and then some type of batch load?  A Fabricated Recordset on the Access side?  Not sure what form the data must be in to do such a thing.

Jim

[stanl]

Not really a lot of time to fiddle around.  I tried a 3pm snapshot. Created the initial MySQL file -> access file by manually running the pass-through query - took about 14 seconds; then tried again with the WB exe. Ran for 15 minutes and then went into not responding.... bummer, since they just upgraded my ram to 8gig.

So, I put a flag in the code - if not set, then the EXE assumes the pass through was done manually and executes the rest of the code which creates 4 addtional tables and queriues them into 4 tabs with updated totals on a workbook. The config file lets you specify the drivers so I have tried 5.1 - 5.3 with equal failures.

[stanl]

Well, I did have to fiddle....

Created a subroutine that queried MySQL then used getstring() for a delimited file, then used the Ace Provider to INSERT INTO my access table from the csv file. At first, a little frustrating as I tried HDR=No and kept getting 'Field F1 Does not Exist'; so modified the getstring to include field headers, set HDR=Yes and it worked well.  My creating/deleting the .csv based on the User environ setting, should have no issues deploying to multiple users.

Jim - thanks for the suggestion to entertain getstring, although I wished the original ODBC INSERT would have work in a short time (it did work just took 15-20 minutes). Working in an MS environ, I have to use Access. Although MySQL is robust, my experience with it isn't - had better outomes with PostGres or SQLite.

Bottom line: once again WB allows me to create a simple distributable exe.   

[JTaylor]

Glad to hear you found a workable solution.   Odd on the performance of the other method though.

Jim

[stanl]

Probably worth mentioning, although I have a 64-bit laptop, Office is 32 bit as are the compiled WB exes. Having read that MySQL 32-bit drivers on 64-bit OS need the MSDASQL Provider, I use

Code (WINBATCH) Select Expand


If StrIndex(Environment("PROCESSOR_ARCHITECTURE"),"86",0,@FWDSCAN)
   cConn1='Driver={%drv%};Server=[svr];Database=[db];User=[user];Password=[pwd];Option=3;'
Else
   cConn1='Provider=MSDASQL;Driver={%drv%};Server=[svr].44;Database=[db];User=[user];Password=[pwd];Option=3;'
Endif



In retrospect, perhaps introducing MSDASQL is responsible for the slow performance of the original SQL that performs well as a pass-through. 

I have tested your suggestion with a full-day's data (40,000 + calls), or as an early morning snapshot (when only one center is operating). Using getstring and a temp .csv is about as fast as the original pass-through, so the solution is pretty much automated w/out distracting queries in the access file.

[JTaylor]

Assuming we are talking about the same situation...that is, I run 32-bit WB scripts on a 64-bit system...I always use this for my connections.  Perhaps what you are doing is different as you are interacting with Access as well?

Driver={MySQL ODBC 5.1 Driver}


Jim

[td]

Interesting blog post about 'msdasql' with bits about 'slower' and 'deprectated'.


http://blogs.msdn.com/b/selvar/archive/2007/11/10/msdasql-oledb-provider-for-odbc-drivers.aspx

[stanl]

Assuming we are talking about the same situation...that is, I run 32-bit WB scripts on a 64-bit system...I always use this for my connections.  Perhaps what you are doing is different as you are interacting with Access as well?

Driver={MySQL ODBC 5.1 Driver}


Jim

I referred to (see attached)

[stanl]

Interesting blog post about 'msdasql' with bits about 'slower' and 'deprectated'.


http://blogs.msdn.com/b/selvar/archive/2007/11/10/msdasql-oledb-provider-for-odbc-drivers.aspx

As I replied to Jim the connection strings site recommends MSDASQL with 32-bit MySQL on 64 bit system. But, having time to test, I commented out the If..Endif in the snippet I posted and used the Driver directly.... pretty much the same poor, slow results [when trying to Insert Into].  Either way, performing the SELECT then sending getstring() to a temp .csv for Inserting was fast.   

[JTaylor]

Okay...I was just mentioning that I [think] I am using the 32-bit driver on 64-bit machines and I make no reference to a Provider.  May not be the same situation though.  Not trying to beat a dead horse as I know you have a viable solution.

Jim

[stanl]

Okay...I was just mentioning that I [think] I am using the 32-bit driver on 64-bit machines and I make no reference to a Provider.  May not be the same situation though.  Not trying to beat a dead horse as I know you have a viable solution.

Jim

Well, to finally clarify:  the original issue was getting MySQL into Access. (1) It worked quickly with a pass through query in an Access db (2) it was slow as molasses using the ACE Provider attempting an INSERT from MySQL to Access, though it did work (3) Cannot insert into Access from MySQL directly (4) just SELECTING from MySQL with the driver whether or not it addtionally used MSADSQL (32 bit) makes no discernible difference.

So, moving between the two platforms is really a moot point. Using a .csv as middleware is viable.

I may be getting Office 64-bit, then I can play with the 64-bit MySQL drivers. My original aim, in even calling the script a snapshot, was to gather, transform, make the pretty Excel stuff and publish (with a timestamp) in under 2 minutes. I can do that and just zipped up a large exe with support files for a couple of colleages to test.

One tweak I will have to make is to normalize the times between Mountain and Eastern Standard as the MySQL Servers are in NY and only record EST. Lost in all this is the real beauty of the WB script to load the Excel template, clear and format the tabs with data in seconds.