5

I am trying to import a SQL view not a table in Mathematica. The command:

SQLSelect[conn, "DataByYear"]

This will correctly bring in the table. However using the view name

SQLSelect[conn, "QBDateByYear_vw"]

Gives an error

JDBC::error: Invalid object name 'dbo.QBDataByYear'. >>

How do I specify this correctly?

Szabolcs
  • 234,956
  • 30
  • 623
  • 1,263
David Kerr
  • 583
  • 1
  • 4
  • 11
  • 2
    Does SQLExecute[conn, "select * from QBDateByYear_vw"] work? – mfvonh Aug 09 '15 at 17:40
  • Yes that brings in the data in the view. Now I have to figure out why the WHERE statement isn't working. – David Kerr Aug 10 '15 at 02:28
  • If you are comfortable with SQL, I would recommend using SQLExecute for all querying -- the other commands are useful but they can end up doing strange things depending on which database you are using. – mfvonh Aug 10 '15 at 12:10

2 Answers2

3

In the question you do not specify which database driver you use to open the connection with JDBC. I have been experimenting with Microsoft Access Database drive and I had similar problems in the past.

In that case you must enclose the table parameter of type view in your SQLExecute or SQLSelect query with square brackets, e.g.

SQLExecute[conn, "select * from [vwTx-BNAM]"]

or

SQLSelect[conn, "[vwTx-BNAM]"]

Typical cases of table/view that need square brackets around the name identifier are those with spaces, dashes and underscores.

Community
  • 1
Athanassios
  • 1,291
  • 10
  • 22
  • 1
    I don't think that this is the OPs problem as his identifiers don't need any quoting. Nevertheless you probably should mention that square brackets for "delimited identifiers" is a specific feature for MS-Access and MS-SQLServer. I think the "standard" is double quotes but there are other characters in use, e.g. mysql seems to use backquotes as a default. – Albert Retey Aug 08 '16 at 11:31
2

You may use SQLTable in SQLSelect so that the table names are properly formatted for the database system you are using.

SQLSelect[conn, SQLTable["QBDateByYear_vw"]]

You may also find SQLColumn of interest.

Hope this helps.

Edmund
  • 42,267
  • 3
  • 51
  • 143