none
Type conversion issue RRS feed

  • Question

  • In the code below I get a compile error cannot convert double to string unless I enclose the dafault value 0.0 in quotes. It does not make any sense to me. How could it be?

     

    Code Snippet

    string sqlCommand = "INSERT INTO " + tbOurs.Name +

    " (opened, high, low, closed, volume, dateTimed ) " +

    " VALUES ( @opened, @high, @low, @closed, @volume, @dateTimed )";

    UserDefinedFunctionParameter[ ] parametrs = new UserDefinedFunctionParameter[ 5 ];

    UserDefinedFunctionParameter par1 = new UserDefinedFunctionParameter ( );

    par1.DataType = DataType.Float;

    par1.DefaultValue = "0.0";  // <== here

    par1.Name = "opened";

     

    The same thing happens to other types: BigInt and DateTime. What shall I do?

     

    Thanks.

    Tuesday, October 21, 2008 7:56 PM

Answers


  • Hi,

    Default value property of parameter is defined as string so you have to specify default value in quotes.
    http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.parameter.defaultvalue.aspx

    Since parameter can be of any type ,the default value has been defined as string to handle all types.

    Regards,
    Alok Parmesh
    Wednesday, October 22, 2008 6:26 AM
    Answerer

  • Hi,

    There are no actual parameter values involved as SMO is used to handle DDL only.

    Regards,
    Alok Parmesh
    Wednesday, October 22, 2008 4:13 PM
    Answerer
  • Hi,


    I will post the links for SMO code samples later.
    Meanwhile this is how one creates a user defined function using SMO.

     


    Code Snippet

    Server srv = new Server(".");
    Database db = new Database(srv,"dd");
    db.Create();


    //string sqlCommand = "INSERT INTO " + tbOurs.Name +" (opened, high, low, closed, volume, dateTimed ) " + " VALUES ( @opened, @high, @low, @closed, @volume, @dateTimed )";

    UserDefinedFunction udf = new UserDefinedFunction(db, "function1", "dbo");
    udf.TextMode = false;//by default this is true if it is true you can directly provide text header and body
    udf.FunctionType = UserDefinedFunctionType.Table;
    udf.TableVariableName = "@t";
    udf.TextBody = "begin \n INSERT @t \n Select @opened, @high, @low, @closed, @volume, @dateTimed \n return \n end";

    UserDefinedFunctionParameter par1 = new UserDefinedFunctionParameter(udf,"@opened");

    par1.DataType = DataType.Float;

    par1.DefaultValue = "0.0";

    UserDefinedFunctionParameter par2 = new UserDefinedFunctionParameter(udf,"@high");

    par2.DataType = DataType.Float;

    par2.DefaultValue = "0.0";

    UserDefinedFunctionParameter par3 = new UserDefinedFunctionParameter(udf,"@low");

    par3.DataType = DataType.Float;

    par3.DefaultValue = "0.0";

    UserDefinedFunctionParameter par4 = new UserDefinedFunctionParameter(udf,"@closed");

    par4.DataType = DataType.Float;

    par4.DefaultValue = "0.0";

    UserDefinedFunctionParameter par5 = new UserDefinedFunctionParameter(udf,"@volume");

    par5.DataType = DataType.BigInt;

    par5.DefaultValue = "0";


    UserDefinedFunctionParameter par6 = new UserDefinedFunctionParameter(udf,"@datetimed");

    par6.DataType = DataType.DateTime;

    par6.DefaultValue = "\""+DateTime.MinValue.ToString()+"\"";

    udf.Parameters.Add(par1);
    udf.Parameters.Add(par2);
    udf.Parameters.Add(par3);
    udf.Parameters.Add(par4);
    udf.Parameters.Add(par5);
    udf.Parameters.Add(par6);

    udf.Columns.Add(new Column(udf,"opened",DataType.Float));
    udf.Columns.Add(new Column(udf,"high",DataType.Float));
    udf.Columns.Add(new Column(udf,"low",DataType.Float));
    udf.Columns.Add(new Column(udf,"closed",DataType.Float));
    udf.Columns.Add(new Column(udf,"volume",DataType.BigInt));
    udf.Columns.Add(new Column(udf,"datetimed",DataType.DateTime));

    udf.Create();

    db.ExecuteNonQuery("Select opened, high, low, closed, volume, dateTimed INTO dbo.t1 from dbo.function1(0,0,0,0,0,'1/1/1') ");

    Thursday, October 23, 2008 8:37 AM
    Answerer

  • Hi
    ,

    You can find SMO samples here http://www.codeplex.com/MSFTEngProdSamples/ .
    Although this is not exhaustive but it is good for start.

    Regards,
    Alok Parmesh
    Thursday, October 23, 2008 3:11 PM
    Answerer

  • Hi,

    I would have to thank windows live alerts for the quick response and difference in the timezone of India and US for the delayed ones.

    You need to enclose your datetime value into quotes to avoid this issue.
    INSERT
    dbo.tdam_AAPL (opened, high, low, closed, volume, dateTimed) Select opened, high, low, closed, volume, dateTimed  from dbo.function1(2306.28,2307.79,2306.28,2307.79, 0, '4/1/2008 8:31:00 PM' )

    INSERT
    dbo.tdam_AAPL (opened, high, low, closed, volume, dateTimed) Select opened, high, low, closed, volume, dateTimed  from dbo.function1(2306.28,2307.79,2306.28,2307.79, 0, '4/1/2008 18:31:00' )


    Sql Server stores datetime in 24 hour format. But you can convert a 24 hour format to 12 hour format by using date formatting options in t-sql.

    Regards,
    Alok Parmesh


    Wednesday, November 5, 2008 9:39 AM
    Answerer

All replies


  • Hi,

    Default value property of parameter is defined as string so you have to specify default value in quotes.
    http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.parameter.defaultvalue.aspx

    Since parameter can be of any type ,the default value has been defined as string to handle all types.

    Regards,
    Alok Parmesh
    Wednesday, October 22, 2008 6:26 AM
    Answerer
  • Thanks, but wait a second. It is a bunch of absurdity. How about the actual parameter values at runtime: shall they also be enclosed in quotes?

     

    Also shall I enclose in quotes (as default value) the "DateTime.MinValue" ? Oh, boy!

    Wednesday, October 22, 2008 3:06 PM

  • Hi,

    There are no actual parameter values involved as SMO is used to handle DDL only.

    Regards,
    Alok Parmesh
    Wednesday, October 22, 2008 4:13 PM
    Answerer
  • It is interesting but how do I Add parameters after I defined them, how do I apply parameters at runtime? Any code samples you could reference me to? This is what I've got so far. It's been a tought going over an uncharted territory for me so far.

     

    Code Snippet

    string sqlCommand = "INSERT INTO " + tbOurs.Name +

    " (opened, high, low, closed, volume, dateTimed ) " +

    " VALUES ( @opened, @high, @low, @closed, @volume, @dateTimed )";

    UserDefinedFunctionParameter[ ] parameters = new UserDefinedFunctionParameter[ 5 ];

    UserDefinedFunctionParameter par1 = new UserDefinedFunctionParameter ( );

    par1.DataType = DataType.Float;

    par1.DefaultValue = "0.0";

    par1.Name = "opened";

    UserDefinedFunctionParameter par2 = new UserDefinedFunctionParameter ( );

    par2.DataType = DataType.Float;

    par2.DefaultValue = "0.0";

    par2.Name = "high";

    UserDefinedFunctionParameter par3 = new UserDefinedFunctionParameter ( );

    par3.DataType = DataType.Float;

    par3.DefaultValue = "0.0";

    par3.Name = "low";

    UserDefinedFunctionParameter par4 = new UserDefinedFunctionParameter ( );

    par4.DataType = DataType.Float;

    par4.DefaultValue = "0.0";

    par4.Name = "closed";

    UserDefinedFunctionParameter par5 = new UserDefinedFunctionParameter ( );

    par5.DataType = DataType.BigInt;

    par5.DefaultValue = "0";

    par4.Name = "volume";

    UserDefinedFunctionParameter par6 = new UserDefinedFunctionParameter ( );

    par6.DataType = DataType.DateTime;

    par6.DefaultValue = "DateTime.MinValue";

    par6.Name = "dateTimed";

     

     

    What shall I do next?

     

    Thanks.

    Wednesday, October 22, 2008 7:10 PM
  • Hi,


    I will post the links for SMO code samples later.
    Meanwhile this is how one creates a user defined function using SMO.

     


    Code Snippet

    Server srv = new Server(".");
    Database db = new Database(srv,"dd");
    db.Create();


    //string sqlCommand = "INSERT INTO " + tbOurs.Name +" (opened, high, low, closed, volume, dateTimed ) " + " VALUES ( @opened, @high, @low, @closed, @volume, @dateTimed )";

    UserDefinedFunction udf = new UserDefinedFunction(db, "function1", "dbo");
    udf.TextMode = false;//by default this is true if it is true you can directly provide text header and body
    udf.FunctionType = UserDefinedFunctionType.Table;
    udf.TableVariableName = "@t";
    udf.TextBody = "begin \n INSERT @t \n Select @opened, @high, @low, @closed, @volume, @dateTimed \n return \n end";

    UserDefinedFunctionParameter par1 = new UserDefinedFunctionParameter(udf,"@opened");

    par1.DataType = DataType.Float;

    par1.DefaultValue = "0.0";

    UserDefinedFunctionParameter par2 = new UserDefinedFunctionParameter(udf,"@high");

    par2.DataType = DataType.Float;

    par2.DefaultValue = "0.0";

    UserDefinedFunctionParameter par3 = new UserDefinedFunctionParameter(udf,"@low");

    par3.DataType = DataType.Float;

    par3.DefaultValue = "0.0";

    UserDefinedFunctionParameter par4 = new UserDefinedFunctionParameter(udf,"@closed");

    par4.DataType = DataType.Float;

    par4.DefaultValue = "0.0";

    UserDefinedFunctionParameter par5 = new UserDefinedFunctionParameter(udf,"@volume");

    par5.DataType = DataType.BigInt;

    par5.DefaultValue = "0";


    UserDefinedFunctionParameter par6 = new UserDefinedFunctionParameter(udf,"@datetimed");

    par6.DataType = DataType.DateTime;

    par6.DefaultValue = "\""+DateTime.MinValue.ToString()+"\"";

    udf.Parameters.Add(par1);
    udf.Parameters.Add(par2);
    udf.Parameters.Add(par3);
    udf.Parameters.Add(par4);
    udf.Parameters.Add(par5);
    udf.Parameters.Add(par6);

    udf.Columns.Add(new Column(udf,"opened",DataType.Float));
    udf.Columns.Add(new Column(udf,"high",DataType.Float));
    udf.Columns.Add(new Column(udf,"low",DataType.Float));
    udf.Columns.Add(new Column(udf,"closed",DataType.Float));
    udf.Columns.Add(new Column(udf,"volume",DataType.BigInt));
    udf.Columns.Add(new Column(udf,"datetimed",DataType.DateTime));

    udf.Create();

    db.ExecuteNonQuery("Select opened, high, low, closed, volume, dateTimed INTO dbo.t1 from dbo.function1(0,0,0,0,0,'1/1/1') ");

    Thursday, October 23, 2008 8:37 AM
    Answerer
  • Thank you very much Alok. It is very helpful. Yesterday I tried to find SMO samples. Not that easy. The pointers to them are kind of disorganized. After about 15 min I gave up. One has to sift thru so many various downloads. That would be very helpful if you could specify what I need.

    Thursday, October 23, 2008 1:54 PM

  • Hi
    ,

    You can find SMO samples here http://www.codeplex.com/MSFTEngProdSamples/ .
    Although this is not exhaustive but it is good for start.

    Regards,
    Alok Parmesh
    Thursday, October 23, 2008 3:11 PM
    Answerer
  • Alok hi,

     

    Your code which I tried first time only today (I am running many branches of my project concurrently and Sql is only a part of it) give me a runtime exception. The function cannot be created,

     

    I changed your code ever so slightly to accomodate my needs and what to post the par that leads up to the troubled spot.

     

    Code Snippet

    DataContext dCon = new DataContext ( serverString );

    UserDefinedFunction udf = new UserDefinedFunction ( db, "function1", "dbo" );

    udf.TextMode = false;

    udf.FunctionType = UserDefinedFunctionType.Table;

    // udf.TableVariableName = "@t";

    udf.TableVariableName = "@" + "tdam_" + symbolName;

    udf.TextBody = "begin \n INSERT @"+

    "tdam_" + symbolName + " \n Select @opened, @high, @low, @closed, @volume, @dateTimed \n return \n end";

    UserDefinedFunctionParameter par1 = new UserDefinedFunctionParameter ( udf, "@opened" );

    par1.DataType = DataType.Float;

    par1.DefaultValue = "0.0";

    UserDefinedFunctionParameter par2 = new UserDefinedFunctionParameter ( udf, "@high" );

    par2.DataType = DataType.Float;

    par2.DefaultValue = "0.0";

    UserDefinedFunctionParameter par3 = new UserDefinedFunctionParameter ( udf, "@low" );

    par3.DataType = DataType.Float;

    par3.DefaultValue = "0.0";

    UserDefinedFunctionParameter par4 = new UserDefinedFunctionParameter ( udf, "@closed" );

    par4.DataType = DataType.Float;

    par4.DefaultValue = "0.0";

    UserDefinedFunctionParameter par5 = new UserDefinedFunctionParameter ( udf, "@volume" );

    par5.DataType = DataType.BigInt;

    par5.DefaultValue = "0";

    UserDefinedFunctionParameter par6 = new UserDefinedFunctionParameter ( udf, "@dateTimed" );

    par6.DataType = DataType.DateTime;

    par6.DefaultValue = "\"" + DateTime.MinValue.ToString ( ) + "\"";udf.Parameters.Add ( par1 );

    udf.Parameters.Add ( par2 );

    udf.Parameters.Add ( par3 );

    udf.Parameters.Add ( par4 );

    udf.Parameters.Add ( par5 );

    udf.Parameters.Add ( par6 );

    udf.Columns.Add ( new Column ( udf, "opened", DataType.Float ) );

    udf.Columns.Add ( new Column ( udf, "high", DataType.Float ) );

    udf.Columns.Add ( new Column ( udf, "low", DataType.Float ) );

    udf.Columns.Add ( new Column ( udf, "closed", DataType.Float ) );

    udf.Columns.Add ( new Column ( udf, "volume", DataType.BigInt ) );

    udf.Columns.Add ( new Column ( udf, "dateTimed", DataType.DateTime ) );

    udf.Create ( ); // <== Exception here

     

     

     

    One statement is commented out. I first ran the code with this statement present and got the same error. Then I changed it into the true name of my table in DB. Also in teh next statement I replaced your "t" with my table name.

     

    The Exception is simple: Cannot create user defined function 'function1.dbo' I can post it in full.

     

    What is wrong?

     

    Thanks.

    Wednesday, October 29, 2008 8:48 PM

  • Hi Alex,

    Can you post the complete exception  generated like why it couldn't create dbo.function1.
    Also can you post the script generated by udf.script() if you use it instead of udf.create()

    Regards,
    Alok Parmesh
    Thursday, October 30, 2008 11:27 AM
    Answerer
  • Alok hi,

     

    I am going to be at that computer in about an hour. In the meantime I want to give you my thoughts as to what possibly happened. I noticed that when I ran the code first time ever (with "t" as the function name since I did not notice that) that statement executed BUt I got an exception at the next statement which was another routine call and it had a simple bug.

     

    When I ran the code second tiem I was puzzled as to why it happened that I was able to run it once. When I went home I thought about it and realized that perhaps the udf function "function1" had been created and got lodged in the server. Perhaps under ordinary circumstance if the routine ran thu that would have been taken care of but since it broke down the cleanup did not happen.

     

    Is it a sensible hypothesis? And if this is correct what can I do about it? I will be there soon.

     

    If I do not recieve your answer by that time I simply rename the function and try.

     

    Thanks.

    Thursday, October 30, 2008 3:12 PM
  • Hi,

     

    It is quite possible that since dbo.function1 got created and since you had not cleaned it up  it could not get created next time.

     

    Regards,

    Alok Parmesh

    Thursday, October 30, 2008 3:23 PM
    Answerer
  • Yep, that was the right guess. I opened the DB in VS2008 and lo and behold it was there.

     

    I deleted it.

     

    Feel great relief. It is so strange that simple ideas (I've had similar situations countless times in the past) don't come to mind right away. I always beging to think about complicated, lofty reasons and lose time.

     

    Thanks.

    Thursday, October 30, 2008 4:57 PM
  • OK, I just ran the procedure. It gave me a short shrift. The ExecuteNonQuery failed.

     

    ExecuteNonQuery failed for Database 'Hist_OHLC_Ameritrade'.

    at Microsoft.SqlServer.Management.Smo.Database.ExecuteNonQuery(StringCollection sqlCommands, ExecutionTypes executionType)

    at Microsoft.SqlServer.Management.Smo.Database.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)

    at Microsoft.SqlServer.Management.Smo.Database.ExecuteNonQuery(String sqlCommand)

    at ComeAndGet.SqlTypes.fillAmeritradeTables(String symbolName) in C:\VCsharp_Projects\ComeAndGet\ComeAndGet\SqlTypes.cs:line 1080

     

    My joy was short lived but it is still a step forward. The downside is I have no idea why?

     

    Could it be that there are problems with the string?

     

     

    Thursday, October 30, 2008 7:21 PM

  • Hi,

    It could be similar issue once the command runs first time dbo.t1 table gets created.
    So when you run it next time it gives error as it is already created.

    Regards,
    Alok Parmesh
    Thursday, October 30, 2008 7:36 PM
    Answerer
  • That cannot be. ONLY ExecuteNonQuery is in the loop, not the creation code. It is strictly outside (preceeding the try/catch block where ENQ sits). Besides, I could not find a single record in the table. Also I printed out the Exception conditional on the first round of the loop because I did not want to have too many exceptions in the Console (the whole stream). I have about 60,000 records to put in.

     

    Thursday, October 30, 2008 7:59 PM

  • Hi,

    I am not able to follow you. Can you post the part of the code where ExecuteNonQuery is in the loop.
    What I meant was
    Select opened, high, low, closed, volume, dateTimed INTO dbo.t1 from dbo.function1(0,0,0,0,0,'1/1/1')
    creates dbo.t1 table and cannot be used with existing table so if you execute this statement once it would create the table
    and when it is executed next it would result in error.
    If you want to add entries in a table using function you should use INSERT.

    Regards,
    Alok Parmesh
    Thursday, October 30, 2008 8:15 PM
    Answerer
  • Alok hi,

     

    I am not familiar with Smo to such a degree that I can detect the prolem myself. I have never seen this form of command. I followed your INSERT Trans-SQL Statement literally. I replaced your table name with my table name which I defined manually in Server Explorer of VS2008. I also replaced your values 0,0,0,... with my double values which are coming from a stream. This is the whole code fragment:

     

    DataContext dCon = new DataContext ( serverString );UserDefinedFunction udf = new UserDefinedFunction ( db, "function1", "dbo" );

    udf.TextMode = false; // by default this is true if it is true you can directly provide text header and body

    udf.FunctionType = UserDefinedFunctionType.Table;

    udf.TableVariableName = "@" + "tdam_" + symbolName;

    udf.TextBody = "begin \n INSERT @"+

    "tdam_" + symbolName + " \n Select @opened, @high, @low, @closed, @volume, @dateTimed \n return \n end";

    UserDefinedFunctionParameter par1 = new UserDefinedFunctionParameter ( udf, "@opened" );

    par1.DataType = DataType.Float;

    par1.DefaultValue = "0.0";

    UserDefinedFunctionParameter par2 = new UserDefinedFunctionParameter ( udf, "@high" );

    par2.DataType = DataType.Float;

    par2.DefaultValue = "0.0";

    UserDefinedFunctionParameter par3 = new UserDefinedFunctionParameter ( udf, "@low" );

    par3.DataType = DataType.Float;

    par3.DefaultValue = "0.0";

    UserDefinedFunctionParameter par4 = new UserDefinedFunctionParameter ( udf, "@closed" );

    par4.DataType = DataType.Float;

    par4.DefaultValue = "0.0";

    UserDefinedFunctionParameter par5 = new UserDefinedFunctionParameter ( udf, "@volume" );

    par5.DataType = DataType.BigInt;

    par5.DefaultValue = "0";

    UserDefinedFunctionParameter par6 = new UserDefinedFunctionParameter ( udf, "@dateTimed" );

    par6.DataType = DataType.DateTime;

    par6.DefaultValue = "\"" + DateTime.MinValue.ToString ( ) + "\"";udf.Parameters.Add ( par1 );

    udf.Parameters.Add ( par2 );

    udf.Parameters.Add ( par3 );

    udf.Parameters.Add ( par4 );

    udf.Parameters.Add ( par5 );

    udf.Parameters.Add ( par6 );udf.Columns.Add ( new Column ( udf, "opened", DataType.Float ) );

    udf.Columns.Add ( new Column ( udf, "high", DataType.Float ) );

    udf.Columns.Add ( new Column ( udf, "low", DataType.Float ) );

    udf.Columns.Add ( new Column ( udf, "closed", DataType.Float ) );

    udf.Columns.Add ( new Column ( udf, "volume", DataType.BigInt ) );

    udf.Columns.Add ( new Column ( udf, "dateTimed", DataType.DateTime ) );udf.Create ( );

    string[ ] result = readCsvFile ( symbolName );

    char[ ] delim = { ',' };

    Console.SetCursorPosition ( 0, 0 );

    foreach ( string line in result )

    {

    string[ ] values = line.Split ( delim, StringSplitOptions.RemoveEmptyEntries );

    try

    {

    db.ExecuteNonQuery ( "Select opened, high, low, closed, volume, dateTimed " +

    " INTO dbo.t1 from dbo.function1(" +

    values[ 0 ] + values[ 1 ] + values[ 2 ] + values[ 3 ] + values[ 4 ] + values[ 5 ] + ") " );

    success++;

    string strSuc = success.ToString ( );

    Console.Write ( "S: " + success.ToString ( ) );

    Console.SetCursorPosition ( 0, 0 );

    }

    catch (Exception ex )

    {

    failure++;

    string strFail = failure.ToString ( );

    Console.SetCursorPosition ( 30, 0 );

    Console.Write ( "F: " + strFail );

    Console.SetCursorPosition ( 0, 0 );

    if ( failure == 1 )

    {

    Console.WriteLine ( " {0} \r\n {1} ", ex.Message, ex.StackTrace );

    }

    }

    }

    } // this is the end of the routine. The beginning is not shown.

     

    This is the auxiliary routine that read the csv file (for clarity):

     

    private string[ ] readCsvFile ( string symbolName )

    { // reading Ameritrade history tables

    string filePath = Globals.cur_drive +

    @"\Program Files\TD AMERITRADE\StrategyDesk\Spreadsheets\" + symbolName + ".csv";

    string[ ] lines = File.ReadAllLines ( filePath );

    string[ ] col = new string[ lines.Length - 1 ];

    Array.Copy ( lines, 1, col, 0, col.Length );

    return col;

    } // readCsvFile

     

    I would appreciate if you examine how I insert values in the command string (TextBody).

     

    Now, I have to comment on what you said about INSERT/SELECT. I blindly copied what you poaster. I don't know if I should have changed the form of the command. Perhaps I should have removed SELECT clause. I simply have no idea since I've never seen commandText (TextBody) strings in Smo. I presume they might be different but I don't know. Perhaps they should be the same like regular CommandText strings?

     

    Thanks.

    Friday, October 31, 2008 5:26 PM
  •  Alok Parmesh - MSFT wrote:

    Hi,

    I am not able to follow you. Can you post the part of the code where ExecuteNonQuery is in the loop.
    What I meant was
    Select opened, high, low, closed, volume, dateTimed INTO dbo.t1 from dbo.function1(0,0,0,0,0,'1/1/1')
    creates dbo.t1 table and cannot be used with existing table so if you execute this statement once it would create the table
    and when it is executed next it would result in error.
    If you want to add entries in a table using function you should use INSERT.

    Regards,
    Alok Parmesh

     

    I keep thinking about what you've said. My TextBody is different. I replaced your t1 with my table name. I don't understand why both INSERT and SELECT are present in that TextBody (of mine) and it bothers me that I do not understand the syntax. Should I remove the SELECT since I have a preset, already defined table?

     

    Thanks.

    Friday, October 31, 2008 8:30 PM
  • Hi,

     

    Sorry for not being clear in my original post. The "SELECT INTO" command was for demonstrating that udf has been created and can be used.

     AlexBB wrote:

    udf.TextBody = "begin \n INSERT @"+

    "tdam_" + symbolName + " \n Select @opened, @high, @low, @closed, @volume, @dateTimed \n return \n end";

     

    This is udf's body which you would have  written if  you had used T-SQL after AS

    If you want to put any logic into it you should put here. Right now it is returning output=input i.e. it is putting a single row which is input parameter into table tdam+symbolName.

     

    The command text are same as one writes in T-SQL.

     

     AlexBB wrote:

    foreach ( string line in result )

    {

    string[ ] values = line.Split ( delim, StringSplitOptions.RemoveEmptyEntries );

    try

    {

    db.ExecuteNonQuery ( "Select opened, high, low, closed, volume, dateTimed " +

    " INTO dbo.t1 from dbo.function1(" +

    values[ 0 ] + values[ 1 ] + values[ 2 ] + values[ 3 ] + values[ 4 ] + values[ 5 ] + ") " );

    success++;

    string strSuc = success.ToString ( );

    Console.Write ( "S: " + success.ToString ( ) );

    Console.SetCursorPosition ( 0, 0 );

    }

    catch (Exception ex )

    {

    failure++;

    string strFail = failure.ToString ( );

    Console.SetCursorPosition ( 30, 0 );

    Console.Write ( "F: " + strFail );

    Console.SetCursorPosition ( 0, 0 );

    if ( failure == 1 )

    {

    Console.WriteLine ( " {0} \r\n {1} ", ex.Message, ex.StackTrace );

    }

    }

    }

    } // this is the end of the routine. The beginning is not shown.

     

    The problem is with SELECT INTO command  since you have already created a Table t1 you need to use

    db.ExecuteNonQuery ( "INSERT dbo.t1(opened, high, low, closed, volume, dateTimed) Select opened, high, low, closed, volume, dateTimed " +

    " from dbo.function1(" +values[ 0 ] + values[ 1 ] + values[ 2 ] + values[ 3 ] + values[ 4 ] + values[ 5 ] + ") " );

     

    Regards,

    Alok Parmesh

    Friday, October 31, 2008 10:04 PM
    Answerer
  • Alok hi,

     

    Now I can see the "light" and logic of the expression. Will try it tomorrow. Thanks much.

    Saturday, November 1, 2008 1:01 AM
  • Alok, a bad news. I just tried it and it is not working. As I said I can work on it only piecemeal since I run a number of swimlanes in my project concurrently.

     

    I am posting an almost entire rotuine with many debug statements which I placed in to verify that the inpur data is OK, etc. There is something wrong with the strings, I guess. Please take a look. I ran it a few times, carefully removing the Function1 every time because otherwise it fails at an earlier stage, of course.

     

    DataContext dCon = new DataContext ( serverString );

    UserDefinedFunction udf = new UserDefinedFunction ( db, "function1", "dbo" );

    udf.TextMode = false; // by default this is true if it is true you can directly provide text header and body

    udf.FunctionType = UserDefinedFunctionType.Table;

    udf.TableVariableName = "@" + "tdam_" + symbolName;

    udf.TextBody = "begin \n INSERT @"+

    "tdam_" + symbolName + " \n Select @opened, @high, @low, @closed, @volume, @dateTimed \n return \n end";

    UserDefinedFunctionParameter par1 = new UserDefinedFunctionParameter ( udf, "@opened" );

    par1.DataType = DataType.Float;

    par1.DefaultValue = "0.0";

    UserDefinedFunctionParameter par2 = new UserDefinedFunctionParameter ( udf, "@high" );

    par2.DataType = DataType.Float;

    par2.DefaultValue = "0.0";

    UserDefinedFunctionParameter par3 = new UserDefinedFunctionParameter ( udf, "@low" );

    par3.DataType = DataType.Float;

    par3.DefaultValue = "0.0";

    UserDefinedFunctionParameter par4 = new UserDefinedFunctionParameter ( udf, "@closed" );

    par4.DataType = DataType.Float;

    par4.DefaultValue = "0.0";

    UserDefinedFunctionParameter par5 = new UserDefinedFunctionParameter ( udf, "@volume" );

    par5.DataType = DataType.BigInt;

    par5.DefaultValue = "0";

    UserDefinedFunctionParameter par6 = new UserDefinedFunctionParameter ( udf, "@dateTimed" );

    par6.DataType = DataType.DateTime;

    par6.DefaultValue = "\"" + DateTime.MinValue.ToString ( ) + "\"";

    udf.Parameters.Add ( par1 );

    udf.Parameters.Add ( par2 );

    udf.Parameters.Add ( par3 );

    udf.Parameters.Add ( par4 );

    udf.Parameters.Add ( par5 );

    udf.Parameters.Add ( par6 );

    udf.Columns.Add ( new Column ( udf, "opened", DataType.Float ) );

    udf.Columns.Add ( new Column ( udf, "high", DataType.Float ) );

    udf.Columns.Add ( new Column ( udf, "low", DataType.Float ) );

    udf.Columns.Add ( new Column ( udf, "closed", DataType.Float ) );

    udf.Columns.Add ( new Column ( udf, "volume", DataType.BigInt ) );

    udf.Columns.Add ( new Column ( udf, "dateTimed", DataType.DateTime ) );

    udf.Create ( );

    string[ ] result = readCsvFile ( symbolName );

    char[ ] delim = { ',' };

    Console.SetCursorPosition ( 0, 0 );

    foreach ( string line in result )

    {

    string[ ] values = line.Split ( delim, StringSplitOptions.RemoveEmptyEntries );

    Console.WriteLine ( values[ 0 ].ToString ( ) + " " + values[ 1 ].ToString ( ) + " " + values[ 2 ].ToString ( ) + " " +

    values[ 3 ].ToString ( ) + " " + values[ 4 ].ToString ( ) + " " + values[ 5 ].ToString ( ) );

    try

    {

    string execute = "INSERT dbo." + "tdam_" + symbolName +

    " (opened, high, low, closed, volume, dateTimed) Select opened, high, low, closed, volume, dateTimed " +

    " from dbo.function1(" + values[ 2 ] + "," + values[ 3 ] + "," +

    values[ 4 ] + "," + values[ 5 ] + ", 0, " + values[ 0 ] + " " + values[ 1 ] + " ) ";

    Console.WriteLine ( execute );

    db.ExecuteNonQuery ( execute );

    success++;

    string strSuc = success.ToString ( );

    Console.Write ( "S: " + success.ToString ( ) );

    Console.SetCursorPosition ( 0, 0 );

    }

    catch ( Exception ex )

    {

    failure++;

    string strFail = failure.ToString ( );

    Console.SetCursorPosition ( 30, 0 );

    Console.Write ( "F: " + strFail );

    Console.SetCursorPosition ( 0, 0 );

    if ( failure == 1 )

    {

    Console.WriteLine ( " {0} \r\n {1} ", ex.Message, ex.StackTrace );

    }

    if ( failure > 10 )

    {

    break;

    }

    }

    }

     

    The routine fails at ExecuteNonQuery. The error message says nothing.

     

    ExecuteNonQuery failed for 'Hist_OHLC_Ameritrade'.

    at Microsoft.SqlServer.Management.Smo.Database.ExecuteNonQuery(StringCollection sqlCommands, ExecutionTypes executionType)

    at Microsoft.SqlServer.Management.Smo.Database.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)

    at Microsoft.SqlServer.Management.Smo.Database.ExecuteNonQuery(String sqlCommand)

    at ComeAndGet.SqlTypes.fillAmeritradeTables(String symbolName) in C:\VCsharp_Projects\ComeAndGet\ComeAndGet\SqlTypes.cs:line 1079

     

    I will appreciate your attention

     

    Thanks.

     

    P.S. I changed a block of code because I found a goofy error and thought that that was the problem. I ran it, it failed, I found another one, then another, etc. Finally I exhausted all errors goofy and otherwise and it still fails at the same place.

     

    This is some addiional output. This is how the execute string looks like:

     

    "INSERT dbo.tdam_AAPL (opened, high, low, closed, volume, dateTimed) Select opened, high, low, closed, volume, dateTimed from dbo.function1(2306.28, 2307.79, 2306.28, 2307.79, 0, 4/1/2008 9:31:00 )"

     

    BTW, the values appear to be bizarre. AAPL has never been that high. I probably downloaded another file by mistake but this is irrelevant for the Smo.

     

    Do you see anything wrong with it? For the life of me I cannot.

     

    .

    Tuesday, November 4, 2008 7:12 PM

  • Hi,

    I would like to ask one question is the table
    "dbo.tdam_AAPL" already existing when the query is getting executed.
    Also can you post the inner exception's message as it would help understand the problem better.

    It would be better I think if you check the udf exists before creating new one and creating the table
    "dbo.tdam_AAPL" before inserting data in this program itself if does not exists.

    Also I would like to know one more thing is your naming of the table and table parameter intentionally same.

    Regards,
    Alok Parmesh
    Tuesday, November 4, 2008 8:24 PM
    Answerer
  • Thankyou, Alok.

     

    I cannot create DB the way you suiggest. I can do it the first time but then I will have to add a few records almost daily. Therefore  cannot recreate the DB every time. It is impractical.

     

    Checking for the existance of the Function1 (udf) does not make any sense. I delete it visually. If for some reason I forget I get an exception at the place where udf.Create ( ) is located.

     

    So, the answer to your first questin is: yes the table exists. I created it manually. I set up Primay Key manually.

     

    I don't understand your last question.

     

    I will post the InnerException in a few minutes.

    Tuesday, November 4, 2008 9:13 PM
  • You are very fast.I should monitor the thread every 60 seconds.

     

    This is the printout. Because the cursor returns purposely to the left top position for counting failures and successes the printout is screwed up ever so slightly.

     

    Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception occurred while executing a Transact-SQL statement or batch. ---> System.Data.SqlClient.SqlException: Incorrect syntax near '9'.

       at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
       at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
       at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
       at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
       at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
       at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
       at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
       at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
       --- End of inner exception stack trace ---
       at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
       at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(StringCollection sqlCommands, ExecutionTypes executionType)
       at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(StringCollection queries, ExecutionTypes executionType)
       at Microsoft.SqlServer.Management.Smo.Database.ExecuteNonQuery(StringCollection sqlCommands, ExecutionTypes executionType)
        at Microsoft.SqlServer.Management.Smo.Database.ExecuteNonQuery(StringCollection sqlCommands, ExecutionTypes executionType)
       at Microsoft.SqlServer.Management.Smo.Database.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
       at Microsoft.SqlServer.Management.Smo.Database.ExecuteNonQuery(String sqlCommand)
       at ComeAndGet.SqlTypes.fillAmeritradeTables(String symbolName) in C:\VCsharp_Projects\ComeAndGet\ComeAndGet\SqlTypes.cs:line 1086
    4/1/2008   9:31:00  2306.28  2307.79  2306.28  2307.79
    INSERT dbo.tdam_AAPL (opened, high, low, closed, volume, dateTimed) Select opened, high, low, closed, volume, dateTimed  from dbo.function1(2306.28,2307.79,2306.28,2307.79, 0, 4/1/2008 9:31:00 )

     

    Code Snippet

    if ( failure == 1 )

    {

    Console.WriteLine ( " {0} \r\n {1} \r\n {2}", ex.Message, ex.InnerException, ex.StackTrace );

    }

     

     

    OK, you are correct. Incorrect Syntax. What is wrong with the string? It is a very good news.

     

    Thanks.
     

     

    Tuesday, November 4, 2008 9:21 PM
  • OK, I can see it. Apparently I incorrectly put together Date and Time parts of DateTime. I have no idea how Sql Server wants it. I forgot. I may check that out though.

     

    It actually came as a copmplete surpise to me that DateTime was broken into two parts in csv file.T hat was one of the snafus.

     

    Thanks.

     

    Tuesday, November 4, 2008 9:32 PM
  • I checked how the DateTime values are represented in Sql Server tables and could see that AM or PM are missing. Is this a problem? This is another surprise of this csv arrangement. So, I have to do it programmatically. OMG.

     

    Tuesday, November 4, 2008 9:37 PM

  • Hi,

    I would have to thank windows live alerts for the quick response and difference in the timezone of India and US for the delayed ones.

    You need to enclose your datetime value into quotes to avoid this issue.
    INSERT
    dbo.tdam_AAPL (opened, high, low, closed, volume, dateTimed) Select opened, high, low, closed, volume, dateTimed  from dbo.function1(2306.28,2307.79,2306.28,2307.79, 0, '4/1/2008 8:31:00 PM' )

    INSERT
    dbo.tdam_AAPL (opened, high, low, closed, volume, dateTimed) Select opened, high, low, closed, volume, dateTimed  from dbo.function1(2306.28,2307.79,2306.28,2307.79, 0, '4/1/2008 18:31:00' )


    Sql Server stores datetime in 24 hour format. But you can convert a 24 hour format to 12 hour format by using date formatting options in t-sql.

    Regards,
    Alok Parmesh


    Wednesday, November 5, 2008 9:39 AM
    Answerer
  • Thank you very much Alok. I think that closes the issue. I haven't tried it but I am sure it will  work. It is interesting, I sort of know all this quite well, in fact, I preached the same thing about the Sql Server DateTime srorage format, most likely just ticks, and the visual representation but when I appear in the position of a questioneer I often forget this stuff. Even when you INSERT DateTime values in Sql Server with Trans-Sql command without Smo the single quotes are used and I used them many times. It is weird. Just a lot of stuff going on at the same time.

     

    Thanks again.

    Wednesday, November 5, 2008 5:02 PM
  • Yep, it does work.The process is rather slow.I was surprised. I've got a quad T7400 DELL and it took 40 seconds to load 1000 records. I don't really recall if the other method was faster, perhaps it was..

     

    Thanks again.

     

    P.S: Stand by for some new "stupid" questions. = )

     

    Wednesday, November 5, 2008 5:19 PM
  • Alok, just thinking. Perhaps I could se BulkCopy instead. Is the rotine suitable for my task or is it only for copying from one Table to another?

     

    Wednesday, November 5, 2008 7:56 PM
  • Hi,

     

    I was having exactly same thought. Since you were basically inserting data into a table from a csv file and the udf was not doing any logic. BULK INSERT would completely fit the bill.

     

    Something like this would be best.

    BULK 
    INSERT 
    dbo.tdam_AAPL
            
    FROM 'c:\datacsv.txt'
                
    WITH
        
    (
                    
    FIELDTERMINATOR ',',
                    
    ROWTERMINATOR '\n'
        
    )
    GO

     

    Regards,

    Alok Parmesh

    Wednesday, November 5, 2008 8:29 PM
    Answerer
  • Alok, thank you. Immediately after looking at the code I saw that it woulnd not work. This crazy file is highy irregular. One field (stock volume) is enclosed in double quotes, e.g. "124,657,088" for the reason you understand but if the number is less than 1,000 the quotes are dropped. It is simply 534 with a comma in front of it. I have to do some preliminary surgery on each line depending on the actual formatting.

     

    Your piece of code will be preserved because ther might be simpler situattions.

     

    Now I have a different problem. function1 is NOT deleted by default after the code run to the very end. I have to do it manually. How can I do it programmatically?

     

    Thank you.

     

     

    Thursday, November 6, 2008 6:41 PM

  • Hi,

    You can try something like this to programmatically delete udf after your work is done.

    if ( db.UserDefinedFunctions["function1","dbo"] !=  null )
    db.UserDefinedFunctions["function1","dbo"].Drop();

    Alternatively you can create udf only when it does not exist on db.

    Regards,
    Alok Parmesh
    Thursday, November 6, 2008 7:24 PM
    Answerer
  • Thank you very much Alok. I am downloading massive amounts of data. Many files. Everything runs smoothly. Your code is working great. I think this is it.

    Thursday, November 6, 2008 7:59 PM
  • I  am glad that things worked out.

    Regards,
    Alok Parmesh
    Friday, November 7, 2008 12:16 PM
    Answerer