Plex 2E

 View Only
  • 1.  SQLBlockFetch & SQL Literals

    Posted Jul 30, 2015 10:13 AM

    Just getting on with the SQLBlockFetch and everything works great except for the lack of return of SQL literals.

     

    For instance:

     

    Select FNAME as FirstName, LNAME as LastName, 'Manager' as Type, RPRTSTO as ReportsTo

    From EMP WHERE EMPTYPE = 'M'
    UNION ALL
    Select FNAME as FirstName, LNAME as LastName, 'Employee' as Type, RPRTSTO as ReportsTo

    From EMP WHERE EMPTYPE = 'E'
    Order by Type, LastName, FirstName


    The values for all the columns come back except for the literal column (Type).  Anyone ever try something like this with the SQLBlockFetch Pattern?



  • 2.  Re: SQLBlockFetch & SQL Literals

    Posted Aug 07, 2015 01:57 PM

    Anyone able to assist with this question?

     

    Thank you



  • 3.  Re: SQLBlockFetch & SQL Literals
    Best Answer

    Posted Feb 18, 2016 05:26 PM

    Again not sure if anyone has attempted this but either way, we were able to find a solution a few months back and I figure I might as well as update the post with the solution we came up with.

     

    It was discovered that the SQL type of Var Text was being used (values 448 & 449) for the literal.  When the string is returned the first two bytes are the number of characters in the string. 

     

    We added the following source to handle the first two characters for when a Var Text was encountered since we didn't care about the first two bytes:

         c                   Eval      &(1:) = %STR(sqldata+2:%LEN(SqlVar))

     

    Granted if you want to use the first two bytes you could cast them into an integer and then get that many characters from the returned string.