none
Procedure variable cannot be assigned when referencing an assembly method

    Question

  • In a U-SQL stored procedure I have a parameter, @outfile, that might be passed in a value like:

    "/output/records/somefile.csv"

    I have registered an assembly that contains a method to simply get the file extension - "csv" in this case - and in my procedure toward the top I can compile the procedure which references my assembly:

    REFERENCE ASSEMBLY [master].[USQLBaseLibrary];
    
    USING baselib = USQLBaseLibrary.Methods;
    
    DECLARE @file_extension string = baselib.GetFileExtension(@outfile);
    
    
    

    However, when I Submit a script to execute the stored procedure, this exception is returned:

    "Expression cannot be constant folded."

    So instead what I have to do is this inline:

    DECLARE @file_extension string = @outfile.Substring(@outfile.LastIndexOf(".") + 1, 3);

    Q:   why can't I reference my assembly to get that string assigned to the U-SQL variable in the procedure?


    Bill Blakey

    Wednesday, October 10, 2018 11:56 PM

Answers

  • Dear Bill

    The function you wrote is a blackbox for the compiler and thus cannot be constant-folded, i.e., evaluated at compile time. Compile time evaluation is needed because the engine needs to know all the file names at compile time.

    You may be able to use packages with parameters to initialize the variable and thus "hide" the code. See CREATE PACKAGE and IMPORT PACKAGE.

    Cheers

    Michael


    Michael Rys

    • Marked as answer by Bill Blakey FA Thursday, October 11, 2018 11:21 PM
    Thursday, October 11, 2018 12:25 AM
    Moderator

All replies

  • Dear Bill

    The function you wrote is a blackbox for the compiler and thus cannot be constant-folded, i.e., evaluated at compile time. Compile time evaluation is needed because the engine needs to know all the file names at compile time.

    You may be able to use packages with parameters to initialize the variable and thus "hide" the code. See CREATE PACKAGE and IMPORT PACKAGE.

    Cheers

    Michael


    Michael Rys

    • Marked as answer by Bill Blakey FA Thursday, October 11, 2018 11:21 PM
    Thursday, October 11, 2018 12:25 AM
    Moderator
  • Michael, thank you - I had seen reference to Packages in prior reading but have not ventured near them yet.  I will look at trying that soon. 

    Bill Blakey

    Thursday, October 11, 2018 11:22 PM
  • I'm just circling-back on this issue of trying to get a scalar value out of a file.  I'm kinda crossing threads here with another I've posted on being able to construct an OUTPUT ... TO filename, and part of that name comes from a value I get out of the file.  I've been trying to get the PACKAGE construct to work but am unable - one issue is that you cannot EXTRACT in a Package, I'm trying to do that because I want to pass (EXPORT) that variable value into the U-SQL procedure as a parameter.  Once I do that I can use it in the FROM or TO clauses.

    I cannot find a concrete example of doing what I'm wanting to do - would you happen to have any references?


    Bill Blakey

    Friday, January 11, 2019 9:26 PM