Just getting on with the SQLBlockFetch and everything works great except for the lack of return of SQL literals.
Select FNAME as FirstName, LNAME as LastName, 'Manager' as Type, RPRTSTO as ReportsTo
From EMP WHERE EMPTYPE = 'M'UNION ALLSelect 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?
Anyone able to assist with this question?
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.