I have been spending the last couple of days reading other posts about OpenSQLConnection[] without any success. I've looked at the documentation and I have tried the examples using the "Demo" database from the documentation.
The examples using the "Demo" database work as advertised in the documentation.
However, I simply cannot seem to be able to open a connection to a SQL database I created with Microsoft Sequel Server Management Studio.(MSSMS)
The database I created is called "StockDataDB" and holds stock data obtained from Wolfram's Financial Data.
It contains a table which holds Date, Open, High, Low, Close, Volume, Ticker, Period, etc... After I created the table, called dbo.StockDataTable with a simple script using SQL's Create Table, I used a C# program to read CSV files and fill the database. The csv files were created via a Mathematica program that obtained the financial data from Wolfram and then wrote the data into CSV files with columns Date, Open, High, Low, Close, and Volume.
My C# program was able to connect to the database and transfer all the CSV files into the database. I was able to verify that the data was properly stored in the database with MSSMS by:
1) running a couple of queries to get some stock data
and
2) running another C# program that connects to the database, queries some stock data, and displays it in a stock chart.
So, at this point, I know that the database exists and I know that I can connect to it and query data from it with a C# program.
I would like to connect to it from Mathematica,
I have tried using the OpenSQLConnection[] by using the version of the call that uses a named database by doing:OpenSQLConnection["StockDataDB"]
But I get the error: OpenSQLConnection::notfound: DataSource not found: StockDataDB
Can someone tell me how I can connect?
Here is the information from MSSMS: The StockDataTable properties returned by MSSMS are:
Product Name: Microsoft SQL Server Express Edition (64-bit)
Product Version: 15.0.4153 RTM
Authentication Method: Windows Authentication --> so I don't have a "username" or a "password"
Database: StockDataDB
Server: (LcalDB)\MSSQLLocalDB
Instance Name: LOCALDB#B5C431B2
User Name: HENRICK\Henri
Host Platform: Windows
Host Distribution: Windows 10 Home
I also have the path to the database file as: C:\Users\Henri\StockDataDB.mdf
When I run:
DataSourceNames[]
JDBCDriverNames[]
I get back
{"demo", "graphs", "publisher"}
{"Microsoft Access(UCanAccess)", "Derby(Embedded)", "Derby(Server)", \
"Firebird", "H2(Embedded)", "H2(Memory)", "H2(Server)", "hsqldb", \
"HSQL(Memory)", "HSQL(Server)", "HSQL(Server+TLS)", \
"HSQL(Standalone)", "HSQL(Webserver)", "HSQL(Webserver+TLS)", \
"jtds_sqlserver", "jtds_sybase", "mysql", "MySQL(Connector/J)", \
"PostgreSQL", "SQLite", "SQLite(Memory)", "Microsoft SQL \
Server(jTDS)", "Microsoft SQL Server(microsoft)", "Sybase(jTDS)"}
I would like to use the Microsoft SQL Server(microsoft) driver since Mathematica recognizes it (if that is possible)
Given all that information, is there a simple/magical invocation of OpenSQLConnection[] that will allow me to connect my program to the SQL database?