ExecuteSQL Select From $variable

Idea created by Vincent_L on Jan 28, 2016


    Since the birth of ExecuteSQL, and even before with the virtual list technique, we need to be able to manipulate lots of values quickly.

    Such value list returned by ExecuteSQL or virtual list are in fact arrays and there's equal to in memory (hence very fast) tables.

    We should be able to extract values, columns from them, or even update change some values. There's lots of custom function out there, but they're not very fast (native code is better) and they're limited.


    So I realized that the best way to manipulate them is with a SQL syntax.

    Therefore I propose that ExecuteSQL would allow us to have the "FROM" being a variable


    So we may write


    ExecuteSQL ("SELECT col1,col3 FROM $(myArray;sep;line,hastitle) WHERE col2>?";char(9);"¶";5)


    This would return the column 1 and the column 3, where the column 2 is > 5


    Rather than creating an ExecuteSQLonVariables function, I think it's best to modify the current one so it can recognize a $ or $$ (or $$$ :-)) variable as if it where a real table, so we could do queries that mixes real tables and variable arrays that would be tremendous offering lots of speed and flexibility.


    so the synth would be something like that


    $(myArray;sep;line,hastitle) for $variables

    $$(myArray;sep;line,hastitle) for $$variables

    $$$(myArray;sep;line,hastitle) for $$$variables (they don't exist yet but solution wide variable are long overdue)


    obviosuly myArray= name of the variable

    sep = the column separator of the array

    line = the record separator in the array

    hastitle = true or false, indicate if the first row is in fact the header of the array, if that's the case, true, then ExecuteSQL would interpret that header as the name of the columns, that could be used as the field names in the SQL query.

    Otherwise if false, the columns would be addressable with col1 for column 1, col2 for col 2, col9999 for column 9999


    Finally, the field type could be very well guessed by the ExecuteSQL function. It would check each column, if all the column is integer then it would be integer, if there's floating number then flots, if there's some text, then text (of course date and timestamp are even easier to spot)


    obviously the exact syntax make be different, but you get the idea