Message Boards Message Boards

0
|
10921 Views
|
2 Replies
|
1 Total Likes
View groups...
Share
Share this post:

Create a mySQL table

Posted 10 years ago

Re Mathematica 10.0.2.0 under Windows 8.1.

I'm new to SQL and for now, what I read is gibberish. Hopefully I correctly installed MySQL Community Server (www.mysql.com) onto my PC. I don't want to use HSQLDB that comes with Mathematica's DatabaseLink.

My code below aims to build a small table but instead elicts the response "JDBC::error: No database selected." So I'm clearly missing something obvious to those who are SQL-literate. If you can easily see what's wrong with the code -- or better yet, reveal a cyberspace resource that I can study -- then please reply. The code follows. Thanks.

Needs@"DatabaseLink`";
conn = OpenSQLConnection[JDBC["MySQL(Connector/J)", "localhost"], 
   "Username" -> "root", "Password" -> "hhhhh"];
SQLTableNames@conn (* as expected this returns { } *)
SQLCreateTable[conn, SQLTable@"myTEST",
  {SQLColumn["AQF", "DataTypeName" -> "INTEGER"],
   SQLColumn["XBT", "DataTypeName" -> "VARCHAR", "DataLength" -> 4]}
  ];

CloseSQLConnection@conn;

"JDBC::error: No database selected."

POSTED BY: Bruce Colletti
2 Replies
Posted 10 years ago

If you are new to SQL, I would not recommend relying upon the JDBC driver. Instead, I would recommend a general reference for learning SQL and to use a graphical database management tool designed to go with your database system. A colleague and I recently worked on a prototype project that involved loading data into a relational database system from Mathematica using the JDBC driver. I have a fair amount of experience with both Mathematica and SQL/RDBMS, but was occasionally stymied at the interface between the two. (The driver is not going to behave exactly the way native SQL transactions would, and you may have a harder time finding references in the event you need to troubleshoot.)

Joe Celko is an author I'd recommend for SQL, though I am more familiar with his advanced and intermediate works. He authored an introductory database title called "Data and Databases: Concepts in Practice."

Another go-to, and one of my (literally) weather-worn references for little syntax variations among the different SQL database vendors is the "SQL Pocket Guide" by Jonathan Gennick. (Though I am not sure this will be of much use to you until you have your tables created and loaded.) If you absolutely must have an online reference, the same author has a nice blog with a variety database-related posts. On your topic of creating tables / loading data, I did find one article about using SQL*Loader, but this is for Oracle database, so I am not sure it will work for your situation. You might try searching his older blog posts: http://gennick.com/database/?offset=1041440400000

Good luck, and happy data source construction!

POSTED BY: Caitlin Ramsey

For my website I have something like:

Needs["DatabaseLink`"];
CloseSQLConnection/@SQLConnections[];
connection=OpenSQLConnection[JDBC["MySQL(Connector/J)","URL.com/shuismanmaps"], "Username"->"USERNAME","Password"->"PASSWORD"];
data=SQLExecute[connection, "SELECT * FROM Markers ORDER BY ID"];

Notice the /tablename

POSTED BY: Sander Huisman
Reply to this discussion
Community posts can be styled and formatted using the Markdown syntax.
Reply Preview
Attachments
Remove
or Discard

Group Abstract Group Abstract