Error in connecting to MySQL table

Reply
Occasional Contributor
Posts: 15

Error in connecting to MySQL table

Hi there,

Without a dedicated administrator, I have been left to my own devices to try to fix an issue I am having when trying to connect to a MySQL database. I have set up the connection through the SAS Management Console, and made it comparable to existing connections that work (however they are for other departments therefore I shouldn't use them).

The issue I have is that when I try to assign my libname I get the error

"ERROR: Error trying to establish connection: Unknown database"

I would have hoped it would create the SQL database if it didn't exist, but that doesn't seem to be the case. My question is, how do I create the database and can I do it through the Management Console (similar to creating a new file, folder or process with a right click?) I don't have access to the server itself, so can't just log on and do what I need to do.

Oh and please excuse my ignorance, I am a programmer/analyst - not an Administrator (but can follow instructions if someone knows how to achieve what I am trying to do!)

Super Contributor
Posts: 644

Re: Error in connecting to MySQL table

If the MySQL database does not exist you need to log into MySQL as an administrator and create the database.  Once the database is created you can use SQL passthrough from SAS to create tables and the like.

Richard

Occasional Contributor
Posts: 15

Re: Error in connecting to MySQL table

Thanks Richard. I figured the guy that originally set it up for me would have done that. But alas, no (his care factor at the moment is non existent). Hopefully I can pry the mysql

information out of him before he leaves at the end of the week.

Super Contributor
Posts: 644

Re: Error in connecting to MySQL table

The most important thing is to ensure that this guy has responsibly passed on administrator account details and passwords.  Speak to his boss if necessary.  You can always get MySQL doco off the web.

Richard

Super Contributor
Posts: 644

Re: Error in connecting to MySQL table

@ddeaves

It occurs to me to wonder why you are trying to create MySQL tables if you are, as it seems, to be the sole user.  It would make more sense to store your data in permanent SAS libraries, so avoiding all the hassles you are experiencing.

Richard

Occasional Contributor
Posts: 15

Re: Error in connecting to MySQL table

Hi Richard - they are stored in permanent SAS libraries, however I need to connect them to Tableau and I've been told that Tableau doesn't support SAS directly very well, hence the conversion into MySQL.

Happy to listen to any other suggestions you may have Smiley Happy

Super Contributor
Posts: 644

Re: Error in connecting to MySQL table

Tableau will import from MS Access which you probably already have on your desktop.  Export to MS Access on your desktop might be more straightforward than setting up a MySQL server, given the uncertainty of admin support.  Not such a good idea though if your tables are very large - not sure what the current limit on MS Access is.

Richard

Occasional Contributor
Posts: 15

Re: Error in connecting to MySQL table

The problem I have is that I am using SAS on my desktop, connecting to a Linux server so I don't actually have any physical connection (such as directory mapping) to the server direct. I am old school and prefer to write my own code rather than use the file, export point and click method, and because I will be running the script every month, I find it easier to hard code what I want to do.

To get around it in the meantime, I managed to work out the path of the folder I had been assigned on the server and I have been converting my data to .csv format before zipping then  emailing it to myself (all using SAS code). It isn't the most ideal way of doing it, but with the limited support and resources here, I managed to find a way to at least give me some sort of result!

Valued Guide
Posts: 3,208

Re: Error in connecting to MySQL table

@ddeaves, You are lost in opinions and habits. Your SAS session is running on Linux that is not really different as Windows see that as your running environment instead of your laptop.
Bill-Gates implemented a lot in Windows like Unix, but some things are to be translated like:

- the \ (Windows backslash) into / (Unix forward slash) and the "my documents" as a folder like "\home\ddeaces" in shortkey "~".

- filenames being case-insensitive (Windows) into a case-sensitive approach (Unix). Advice: use lower-cases.

The Eguide usage is really for programmers and used that way offering more as the DMS. That some sales-person did propagate other expectations is not smart and not correct.   
http://support.sas.com/resources/papers/proceedings13/138-2013.pdf is a nice starter for a oldy. I think sometimes getting gray hairs of all those being block-mindeed.

Getting your data transferred:

  1. As there is a MYSQL-server implemented somewhere it makes sense you can accesss that from you SAS server. In that case a database connection (SAS/Access) must be present and configured.
    These details should be handed over to you.
  2. When the approach is different and you are expected to transfer data to MySQL using your laptop. You are needing that connection from your laptop.Still needing some SAS/Access module in the local sas session. Having the ODBC version licensed you are needing to define odbc connections.

If neither of these has been realized and tested verified, you are in a lost situation. Not a technical question just a misfit of actions.

Tableau is running in a MS environment as far as I can see. It is supporting ODBC  Tableau and ODBC | Tableau Software

When your tables are not too big (performance) and you are having a SAS/Share license. You could connect SAS datasets directly with that.
SAS/Share is able to open up SAS datasets being used by ODBC. SAS ODBC Drivers

---->-- ja karman --<-----
Super Contributor
Posts: 644

Re: Error in connecting to MySQL table

@ddeaves

More than one way to skin the cat!  If, as Jaap suspects, your SAS is installed on the server and you have Enterprise guide to play with, export to MS Access may still be a good option.  To Quote Chris Hemedinger at SAS:

"Using SAS Enterprise Guide, you can import and export MS Access tables and Excel spreadsheets via point-and-click, and SAS/ACCESS is not required. (This method does not use/generate a SAS program — and it does not support “update-in-place” for an existing table.)"

[Mapping an MS Access Database to SAS Library | Numbermonger]

To my mind, falling back on CSV and emails is a counsel of despair, even for an old timer.

Also an old schooler-

Richard

Ask a Question
Discussion stats
  • 9 replies
  • 922 views
  • 0 likes
  • 3 in conversation