I cannot get tables names and columns names from database

1

1

I am working with database using OpenSQLConnection-JDBC. I am able to fetch the data perfectly from the tables but I cannot get nor the names of the tables (using SQLTableNames) neither the column names (using SQLColumnNames). I tried also to work with the help examples; but I got same problem.

what could be the reason?

conn = OpenSQLConnection[ 
  JDBC["MySQL(Connector/J)", 
   "IP/databasename"], 
  "Username" -> "username", "Password" -> "password"]

eventsdata = SQLSelect[
  conn, "tablename",
  SQLColumn["columnname"] == somevalue]

where: IP= Ip address of the database, databasename=database name that I am connecting to, tablename=table name which I have it before, columnname=columns names of the table that I have it before, somevalue=value so that I can fetch some of the data.

Algohi

Posted 2014-06-25T03:59:15.473

Reputation: 19 067

What command are you using to fetch the data? Can you show us the code that works? – mfvonh – 2014-06-25T04:22:05.733

@mfvonh I have add part of the code that works. – Algohi – 2014-06-25T04:50:27.880

What output do you get when you try these: SQLTables[conn], SQLTableNames[conn], SQLTableInformation[conn]? – mfvonh – 2014-06-25T04:52:53.947

I got all empty list {}. – Algohi – 2014-06-25T04:53:36.920

Try SQLExecute[conn, "use tablename"] and then SQLTables[conn]. (with a real table name of course) – mfvonh – 2014-06-25T04:58:49.250

I got JDBC: error when I run SQLExecute[conn, "use tablename"] with my real table name. JDBC::error:You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near'table name'at line 1 – Algohi – 2014-06-25T05:05:28.170

I misspoke (miswrote) earlier: can you try SQLExecute[conn, "use schemaName"] (not tableName)? – mfvonh – 2014-06-25T05:12:12.783

what do you mean by schemaName? if you mean database name, I have tried it now and get same error. – Algohi – 2014-06-25T05:15:14.107

Yeah, database name. Hm. I am fairly certain I had this problem months ago and I can't remember how to get around it. I think it is specific to MySQL, and it's just something really silly. Does SQLExecute[conn, "SHOW TABLES"] work? – mfvonh – 2014-06-25T05:16:33.577

no errors but empty list {}. – Algohi – 2014-06-25T05:21:28.003

I'm out of ideas, then :/ I'll mull it over and see if something pops up. As I said I'm pretty certain it is something super simple. – mfvonh – 2014-06-25T05:25:26.457

Answers

2

I would suggest to use SQL directly. For Oracle databases the following module should work.

Module[{dbConn, sqlAct, records},
"SELECT TABLE_NAME,COLUMN_NAME FROM USER_TAB_COLUMNS WHERE TABLE_NAME like'%your table name%'"// Set[sqlAct, #] &;
OpenSQLConnection["your connection"] // Set[dbConn, #] &;
SQLExecute[dbConn, sqlAct] // Set[records, #] &;
CloseSQLConnection[dbConn];
records
] // MatrixForm // Print;

RMMA

Posted 2014-06-25T03:59:15.473

Reputation: 2 550