Group Abstract

Message Boards

WOLFRAM COMMUNITY

12.4K Views

2 Replies

1 Total Like

View groups...

Follow this post

Share this post:

GROUPS:

Business Analytics

Create a mySQL table

Bruce Colletti

Posted 11 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

Sort By:

Caitlin Ramsey

Posted 11 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

Sander Huisman

Sander Huisman, University of Twente

Posted 11 years ago

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

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

Reply Preview

Attachments

Remove Add a file to this post

Follow this discussion

or Discard

Feedback