5
$\begingroup$

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?

$\endgroup$
3
  • 2
    $\begingroup$ Does SQLExecute[conn, "select * from QBDateByYear_vw"] work? $\endgroup$
    – mfvonh
    Aug 9, 2015 at 17:40
  • $\begingroup$ Yes that brings in the data in the view. Now I have to figure out why the WHERE statement isn't working. $\endgroup$
    – David Kerr
    Aug 10, 2015 at 2:28
  • $\begingroup$ 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. $\endgroup$
    – mfvonh
    Aug 10, 2015 at 12:10

2 Answers 2

3
$\begingroup$

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.

$\endgroup$
1
  • 1
    $\begingroup$ 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. $\endgroup$ Aug 8, 2016 at 11:31
2
$\begingroup$

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.

$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.