ExecuteSQL Select From $variable

Idea created by Vincent_L on Jan 28, 2016
    Active
    Score14

    Hi,


    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