Message Boards Message Boards

0
|
2097 Views
|
4 Replies
|
0 Total Likes
View groups...
Share
Share this post:

How do I connect to and access a SQL Database in Mathematica

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?

POSTED BY: Henrick Jeanty
4 Replies
Posted 10 months ago

Can you share the C# code that successfully connects to the DB?

POSTED BY: Rohit Namjoshi

Hello Rohit, Sure: First, here is my connection string:private const string ConnectionString = @"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog = StockDataDB; Integrated Security = True; Connect Timeout = 30; Encrypt=False"; Then, here is the C# code for the function that reads data which returns a List of candlesticks as well as a DataTable object (for easy databinding to charts, gridviews, etc...). It all works perfectly.

      /// <summary>
       /// Here we get a list of candlesticks and the DataTable obtained from the database
       /// Provide the Ticker to read (e.g. "IBM")
       /// The Period (e.g. Day, Week or Month)
       /// The startDate and endDate we are interested in
       /// </summary>
       /// <param name="Ticker"></param>
       /// <param name="Period"></param>
       /// <param name="startDate"></param>
       /// <param name="endDate"></param>
       /// <returns></returns>
       internal (List<StockAnalyzer.aCandlestick>, DataTable) GetDataTableFromDatabase(String Ticker, String Period, DateTime startDate, DateTime endDate)
       {
         // Create the DataTable 
         DataTable dataTable = new DataTable();
         List<StockAnalyzer.aCandlestick> candlesticks = null;

         // Connect to the database
         using (SqlConnection conn = new SqlConnection(ConnectionString))
         {
          // Define SQL command
          string sql = "SELECT [Date], [Open], High, Low, [Close], Volume,Peak, Valley,Ticker,Period FROM StockDataTable WHERE Ticker = @Ticker AND Period = @Period AND Date BETWEEN @StartDate AND @EndDate Order by Date";
          using (SqlCommand cmd = new SqlCommand(sql, conn))
          {
              // Use SQL Parameters to prevent SQL Injection attacks
              cmd.Parameters.AddWithValue("@Ticker", Ticker);
              cmd.Parameters.AddWithValue("@Period", Period);
              cmd.Parameters.AddWithValue("@StartDate", startDate);
              cmd.Parameters.AddWithValue("@EndDate", endDate);

              try
              {
                 // Open connection
                 conn.Open();
                 SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
                 // Fill the DataTable with the result set
                 dataAdapter.Fill(dataTable);
                 // Prepare to fill the associated list of candlesticks
                 candlesticks = new List<StockAnalyzer.aCandlestick>(dataTable.Rows.Count);
                 foreach (DataRow row in dataTable.Rows)
                 {
                   StockAnalyzer.aCandlestick cs = new StockAnalyzer.aCandlestick
                   (
                    Date: Convert.ToDateTime(row[0]),
                    Open: Convert.ToDouble(row[1]),
                    High: Convert.ToDouble(row[2]),
                    Low: Convert.ToDouble(row[3]),
                    Close: Convert.ToDouble(row[4]),
                    Volume: Convert.ToInt64(row[5]),
                    Peak: Convert.ToBoolean(row[6]),
                    Valley: Convert.ToBoolean(row[7]),
                    Ticker: Convert.ToString(row[8]),
                    Period: Convert.ToString(row[9])
                   );

                   candlesticks.Add(cs);
                 }
              }
              catch (Exception ex)
              {
                 // Log or handle the exception as needed
                 MessageBox.Show("Error: " + ex.Message);
              }
          }
         }

         // return the DataTable
         return (candlesticks, dataTable);
       }

This works without any hiccups. I would love to be able to do the same (simple) thing in Mathematica. 1) Connect to StockDataDB (the database)\ 2) Execute a (simple) SQL command to read the candlesticks associated with a particular Ticker, for a particular period (daily, weekly or monthly), between a starting and ending dates. Pretty simple, isn't it? After that, all the code I have already written and that knows how to read from a CSV file can take over by doing things like plotting, finding trendline patterns, finding Elliott Waves, etc... I would be immensely grateful for a (simple) solution.

POSTED BY: Henrick Jeanty
Posted 10 months ago

Try

conn = OpenSQLConnection[
  JDBC["Microsoft SQL Server(microsoft)", "(LcalDB)\\MSSQLLocalDB"],
  "Catalog" -> "StockDataDB", "instance" -> "LOCALDB#B5C431B2"]  

Are you sure this is correct

Server: (LcalDB)\MSSQLLocalDB

Is it LocalDB and not LcalDB?

POSTED BY: Rohit Namjoshi

Hello Rohit, Thank you for the reply. First, you are correct, it is LocalDB (I must have deleted the o in my post). I had tried and tried your solution again (making sure to change the LcalDB to LocalDB). I had:

connection = 
 OpenSQLConnection[
  JDBC["Microsoft SQL Server(microsoft)", "(LocalDB)\\MSSQLLocalDB"], 
  "Catalog" -> "StockDataDB", "instance" -> "LOCALDB#B5C431B2"]

but I received the error:

JDBC::error: The connection to the host (LocalDB), named instance MSSQLLocalDB failed. Error: "java.net.UnknownHostException: (LocalDB)". Verify the server and instance names and check that no firewall is blocking UDP traffic to port 1434. For SQL Server 2005 or later, verify that the SQL Server Browser Service is running on the host.

I am trying to make sense of all this. I tried the expression you provided and received the error. What I am trying to understand is this: 1) I created the database by using Microsoft SQL Server Management Studio and called it StockDataDB, 2) I then ran a script in MSSMS to create one table called StockDataTable to hold the Ticker, Period, Date, Open, High, Low, Close, and Volume (the typical candlestick information) 3) Via a Windows Form Applications written in C# I read a set of CSV file that held the stock data. Those CSV files were actually created from a Mathematica program that downloaded stock data and exported them to the csv files. For instance, the daily data for IBM is stored in IBM-Day.CSV and has columns for Date, Open, High, Low, Close, and Volume. The Windows Form Application read the CSV file and filled the StockDataTable with about 400 tickers with Daily, Weekly, and Monthly data. 4) To check that everything worked, I made a query to read some of the ticker data in the database and the data was indeed in the database. I was able to connect to the database and read the data from a Windows Forms Application in C# as well as from MSSMS So far, fairly simple and straightforward.

What I don't seem to understand is why it is so difficult to connect to a database with Mathematica. For instance, when I look at the documentation for SQLExecute[] and execute the expressions in the documentation notebook, everything works splendidly. When I run the conn = OpenSQLConnection["demo"]; the connection works great and the expressions that get data or create data into the "Demo" database work perfectly. What I also do not understand is how in the example we only need to provide the name of the database "demo" to connect. Why can't I also just run conn = OpenSQLConnection["StockDataDB"]; ? I have spent 2 days just trying to connect to a simple SQL database. All my other code is ready to go. So far, I have been reading my data from CSV files. This is far, far less than ideal as I can't make simple or complex queries. Please, anyone, let me know what kind of information from MSSMS I need to put in my OpenSQLConnection[] function call so that I can connect. One note: I have looked at the ODBC Data Source Administrator and I don't see a Microsoft SQL Server (microsoft) entry under the "Drivers" tab. There is just a "SQL Server" associated with SQLSRV32.DLL from 5/10/2023 in case this helps.

POSTED BY: Henrick Jeanty
Reply to this discussion
Community posts can be styled and formatted using the Markdown syntax.
Reply Preview
Attachments
Remove
or Discard

Group Abstract Group Abstract