Support variables in the place of table names in standard SQL queries

[ghost]

I'm trying to export some data to text files:

Declare $TablesCount = (select count(name) from sqlite_master where type='table')

Declare $TableName
Declare $FileName

For $counter = 0 To $TablesCount-1
Begin

    Set $TableName = (Select name From sqlite_master Where type='table' Limit 1 Offset $Counter)
    Set $FileName = printf(D:\Data\Clientlists\SW - %s.txt', $TableName)
    
    Print printf('Exporting names from %s to %s', $TableName, $FileName)
    EXPORT TEXT $FileName FROM ( SELECT Name FROM $TableName )

End

Some hints to get it working? Is there a better way to do so?

[electroly]

Hi Churanos, I believe the issue here is that there's no printf function, but you can concatenate strings together using the || operator. For instance:

SET $FileName = 'D:\Data\Clientlists\SW - ' || $TableName || '.txt';
PRINT 'Exporting names from ' || $TableName || ' to ' || $FileName;

Let me know if that works for you.

In the future I hope to add some kind of string formatting function like printf but it isn't available currently. I also hope to add some kind of FOR EACH structure to make it easier to iterate over rows like you're doing here.

[electroly]

Ah, I didn't read far enough into the code. I see that the SQLite engine doesn't like using a variable as the table name in SELECT Name FROM $TableName. Hmm. I think this is something I could add support for, but I don't think there's a way to accomplish it in the current version.

[ghost]

The printf function is a core function of sqlite, no need to reimplement something like it.
For a For Each statement it would be great if variables can handle table data, not only single statement values.
Might it be a good idea to serialize ARRAY values and, if possible, something like Maps as simple json data?

For the moment i can use temp tables to overcome with the variables in Select-Statements thing.

[electroly]

I see, thanks for the info! I can pre-process function arguments before they hit SQLite, so it would be possible to convert an array blob into a readable string, etc. I'm open to suggestion on how you might want the API for this to look.