2

Rewritten Question:

I need to look through an entire database, 210 tables for a unique ID that is a number.

I have limited access to my database through things called nodes (Visual programming.)

These nodes allow for simple querys like SELECT x FROM x WHERE x I believe I can use most commands here.

I'm unfamilar with sql and thought I might be able to get some help here.

Below you can see an example of a node where I can type in commands.

and below that my database structure.

What i'm looking to do is look through the entire database for a unique number, this number changes dynamically but can always be found in the "Id" column of every table. This column is also a Primary Key.

By searching through the database and finding this dynamic unique number in one of the 210 tables I then need to be able to get the table name that the data is located in and the row information ie; all the different column entries for that row.

Example: Execute a prepared query

more info: enter image description here

9
  • Why doesn't your code know which table the data is in? If you want to search in all 210 tables, then this is an indication that maybe all data should be stored in a single table. But it's hard to say anything without examples. – CL. Jul 24 '18 at 7:33
  • So my databasr is automatically created by another program and for my whole idea to work as much of the process needs to be as automated as possible. the database is exported via one click from the other program which I would like to leave if possible and do the heavy lifting in the program I create with unreal again if possible. Just to clarify, I have a variable that changes depending on whats being looked at, the only identifier I have on that object is the name which has the unique ID number in it, which becomes the varible i use to search the tables. – xodust1 Jul 24 '18 at 7:35
  • I'm quite sure this is a lot off topic here. You want to either ask on gamedev, or directly on Epic Games web site, because the thing you are trying to do is strictly related to how the Unreal Engine works. – motoDrizzt Jul 24 '18 at 7:55
  • No it's not, I'm asking what the query is to search through a postgresql database for a specific string. The string being the unique id. For example: SELECT "Comments" FROM "Windows" WHERE "Id" = :uniqueid ; But I want to be able to select the table name of the table where the uniqueid is found. – xodust1 Jul 24 '18 at 8:06
  • SELECT table_name FROM information_schema.columns WHERE (:uniqueid ) IN (SELECT 'Id' FROM information_schema.columns WHERE 'Id' = (:uniqueid )); Gives me: ERROR: failed to find conversion function from unknown to text. Am i even close? – xodust1 Jul 24 '18 at 8:45
2

In your query:

SELECT table_name 
FROM information_schema.columns 
WHERE (:uniqueid ) IN (SELECT 'Id' 
                       FROM information_schema.columns 
                       WHERE 'Id' = (:uniqueid ))

you are trying to mix data and meta-data. information_schema contains only meta-data so you won't find :uniqueid in there. You will have to extract meta-data first, and then iterate over that some how. Assuming all columns are named id (which is a bad choice, but nothing you can avoid if I get it right), something like the following pseudo-code:

c1.execute("SELECT table_name FROM information_schema.columns WHERE column_name = id")
for row in c1.fetchall():
    t = row[0]
    c2.execute("SELECT 1 FROM %s WHERE id = :uniqueid" % (t) )
    if c2.fetchone():
        print "Found %s in %s" %  (:uniqueid, t)
        sys.exit(0)

will print the first table where there is an id = :uniqueid

Another option is to generate a union all for all tables:

SELECT 't1' from t1 where id = :uniqueid
UNION ALL
SELECT 't2' from t2 where id = :uniqueid
UNION ALL
...
UNION ALL 
SELECT 't210' from t210 where id = :uniqueid
3
  • The ":uniqueid " is a varible I create by getting the name of the object I look at in VR and filter out the unique ID number, Fixed 36" x 48" [256252] so the :uniqueid in this case becomes 256252. This number relates to 1 entry in the 210 tables in my Database, specifically an entry in the table "Windows". I have variables for the connection itself, all table names in database, all column names in database and the element I'm looking at, the ":uniqueid " 256252 not all columns are labeled "Id" but the primary key column of all 210 is and contains those unique identifier numbers – xodust1 Jul 24 '18 at 11:21
  • If I understand you correctly, 1 row in the table named Windows contains an id with value 256252, correct? – Lennart Jul 24 '18 at 11:23
  • Yups (had to fill out the minimum characters..) – xodust1 Jul 24 '18 at 11:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.