Direct Database Query - tableName as variable

Options
Bas van Ginkel
Bas van Ginkel Member ✭
in ❓Other questions

Hi,

new to Xano but not to building apps. We're porting an existing app to xano so i started with datamigration. Every table (about 70 of them) has a updatedAt field. We use this field to determine what the last point was that the table content was synchronised.

Initially i've build a function that has an enum named table as input. That function returns the max(updatedAt) from a table. The first 5 build by hand using a conditional then, when matched with the input, ran a query all records that returned the value.

Expanding this to 70+ tables sounds like A LOT of work. So in comes the DIRECT DATABASE QUERY. I've buidl a LAMBDA FUNCTION that translates the input (table_name:ENUM) to it's corresponding technical table name (like X3_31).

I want to use the DIRECT DATABASE QUERY "SELECT MAX("updatedAt") FROM ?" and pass the technical table name as variable to the ?.

This however returns:

"SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near "$1" LINE 1: SELECT MAX("updatedAt") FROM $1 ^ (SQL: SELECT MAX("updatedAt") FROM x3_31)"

Who knows a way around this issues?

Tagged:
· Share on Twitter

Best Answer

  • Chris Coleman
    Chris Coleman Administrator

    ADMIN
    Answer ✓
    Options

    Hi, @Bas van Ginkel. It is not currently possible to dynamically specify a table name with the Direct Database Query function. There is a world where we probably want to do this, but the idea has not gone beyond exploration at this time.

    · Share on Twitter

Answers

  • Bas van Ginkel
    Bas van Ginkel Member ✭
    Options

    Hi Chris,

    thanks for your quick anwer. It's always good to have security features in place to prevent incidents. In this case I know i tried to do stuff that I might not be supposed to do.

    I'll work around this issue.

    · Share on Twitter
  • Max
    Max Member ✭
    Options

    is this going to be an available feature? or thats not on roadmap discussion?

    building from scratch - all seems fine in Xano I guess, but as a new user, who came here with some data to keep building on, from other backend platform that did not fit, it is such a painful process honestly when comes to simply rename column, because imported names are just bad, and you hit a 1 day quest to end up here on this page, that you cant even make a function with inputs like "target_table, old_column, new_column" to make this pain bearable, and hit new wall saying to make a function per every of tens of tables statically to simply copy columns.

    Im either really missing on something, or doing something entirely wrong? because I can't really believe that renaming columns with data can be that complicated? please point me to a right direction :(

    · Share on Twitter

Categories