0

I am getting
Parameter 'Volm' can be removed; its initial value is never used,
despite, as far as I can tell, the value being used.

I am getting
Unnecessary assignment of a value to 'Volm'
for that same value being assigned.

And, on publish, I am getting
Function signature of "FillRow" method does not match SQL declaration for table valued CLR function'GetQuoteHistory' due to column 7,
despite having 7 columns for each, and matching data types.

If I comment out the last column in the function, table definition, and assignment, the project publishes.

I am banging my head, and not because there is music playing. Appreciate other sets of eyes. The above messages are noted on the relevant lines.

    public class TableFunctions
    {
        [SqlFunction(FillRowMethodName = "FillQuoteHistoryRow",
            TableDefinition = "Symbol NVarChar(10), TradeTime datetime, 
                               Opn money, High money, 
                               Low money, Clos money, 
                               Volm int")]
        public static IEnumerable GetQuoteHistory(string Symbol)
        {
            return new QuoteHistory(Symbol);
        } 

        private static void FillQuoteHistoryRow(Object Item, ref string Symbol, ref DateTime TradeTime,
                                                ref decimal Opn, ref decimal High, 
                                                ref decimal Low, ref decimal Clos, 
                                                int Volm)   // mouseover: Parameter 'Volm' can be removed; its initial value is never used.

// on publish, builds, but returns: 
// (278,1): SQL72014: .Net SqlClient Data Provider: Msg 6258, Level 16, State 1, Procedure GetQuoteHistory,
// Line 1 Function signature of "FillRow" method(as designated by SqlFunctionAttribute.FillRowMethodName)
// does not match SQL declaration for table valued CLR function'GetQuoteHistory' due to column 7.

        {
            QuoteHistoryItem QHItem = (QuoteHistoryItem)Item;
            Symbol = QHItem.Symbol;
            TradeTime = QHItem.Time;
            Opn = QHItem.Opn;
            High = QHItem.High;
            Low = QHItem.Low;
            Clos = QHItem.Clos;
            Volm = QHItem.Volm;  // mouseover: Unnecessary assignment of a value to 'Volm'

        } // method


    } // class  TableFunctions

    public class QuoteHistory : List<QuoteHistoryItem>
    {
        public QuoteHistory(string Symbol)
        {
            LoadQuoteHistory(Symbol);
        } // method QuoteHistory

        public void LoadQuoteHistory(string Symbol)
        {
            string path = "C:\\Github\\Uranus\\SQLCLR\\APIOrig\\";
            string func = "TIME_SERIES_INTRADAY_EXTENDED";
            string interval = "1min";
            string slice = "year1month1";
            string apikey = "XBM9114REDBJXZIV";
            string QueryURL = "https://www.alphavantage.co/query" +
                                   "?function=" + func +
                                   "&symbol=" + (string)Symbol +
                                   "&interval=" + interval +
                                   "&slice=" + slice +
                                   "&apikey=" + apikey;

            QueryURL = "file:///" + path + "Example.csv"; // FOR TESTING
            Uri queryUri = new Uri(QueryURL);

            using (WebClient client = new WebClient())
            {
                using (MemoryStream stream = new MemoryStream(client.DownloadDataTaskAsync(queryUri).Result))
                {
                    stream.Position = 0;

                    using (StreamReader APIData = new StreamReader(stream))
                    {
                        while (!APIData.EndOfStream)
                        {
                            var quoteLine = APIData.ReadLine();
                            var quoteFields = quoteLine.Split(',');

                            var QHItem = new QuoteHistoryItem(Symbol, quoteFields);
                            if (QHItem.Time > new DateTime(1900, 1, 1))
                            {
                                Add(QHItem);
                            }  // if
                        }  // while
                    }  // streamreader
                } // memory stream
            } // webclient
        } // LoadQuoteHistory


    public class QuoteHistoryItem
    {
        public string Symbol { get; set; }
        public DateTime Time { get; set; }
        public decimal Opn { get; set; }
        public decimal High { get; set; }
        public decimal Low { get; set; }
        public decimal Clos { get; set; }
        public int Volm { get; set; }

        public QuoteHistoryItem(string symbol, string[] quoteFields)
        {
            if (quoteFields != null && quoteFields.Length > 5)
            {
                try
                {
                    Symbol = symbol;

                    DateTime.TryParse(quoteFields[0], out DateTime time);
                    Time = time;

                    decimal.TryParse(quoteFields[1], out decimal opn);
                    Opn = opn;

                    decimal.TryParse(quoteFields[2], out decimal high);
                    High = high;

                    decimal.TryParse(quoteFields[3], out decimal low);
                    Low = low;

                    decimal.TryParse(quoteFields[4], out decimal clos);
                    Clos = clos;

                    int.TryParse(quoteFields[5], out int volm);
                    Volm = volm;
                }
                catch (FormatException fx)
                {
                    Console.Error.WriteLine(fx.Message);
                }
            }
        } // method QuoteHistoryItem

5
  • 2
    Missing ref?? Commented May 1, 2022 at 19:03
  • whats the value of the object when you step through in debugging Commented May 1, 2022 at 19:06
  • @Nikola Markovinović Oh bless you. That was that face palm moment I was expecting. Commented May 1, 2022 at 19:07
  • @stackunderflow Yeah, we've all been there. Commented May 1, 2022 at 19:11
  • Side notes: Why don't you use TextFieldParser to parse the CSV? I must say I think this whole thing is an exercise in frustration. Shoehorning web access into SQL Server is always going to be difficult. You are much better off using something like Powershell for this eg you could do it in three lines Get-WebRequest .... | Import-Csv ... | WriteDbaDbTable Commented May 1, 2022 at 21:48

0

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.