How should I obtain the field names of SQL?

[xiusin]

Describe the bug

like :

mut db := sqlite.connect(':memory:')!
result := db.exec('select max(id) as max_id from db')!

How do I know the max_id in the field list?

[xiusin]

How do I know the max_id in the field list?

[shove70]

V don't have that yet, I think the field names are already known, and for some aggregate functions we should use "as" to determine the field names

[xiusin]

@shove70 If you want to write a custom SQL statement, it is almost impossible to get the field list directly, unless you use some analytical SQL statements to disassemble the field list

[shove70]

hmm, It's already marked as a feature request

[xiusin]

@shove70 MySQL can use the following function to get the list of fields:

result := db.query('SELECT max(id) as max_id FROM jobs')!
dump(result.fields())

result:

[db.v:163] result.fields(): [
{
        name: "max_id"
        org_name: ""
        table: ""
        org_table: ""
        db: ""
        catalog: "def"
        def: ""
        length: 11
        max_length: 0
        name_length: 6
        org_name_length: 0
        table_length: 0
        org_table_length: 0
        db_length: 0
        catalog_length: 3
        def_length: 0
        flags: 32896
        decimals: 0
        charsetnr: 63
        type: long
}
]

[JalonSolov]

Is that a list of fields, or a description of the max_id field?

The proper way to show fields in a table is SHOW COLUMNS FROM <table name>

[shove70]

result := db.query('SELECT max(id) as max_id FROM jobs')!
dump(result.fields())

Yes, currently mysql, sqlite, mssql, pg several databases have different wrappe ways for query results, sqlite query results only contain []row data, no column information.

This feature request may be scheduled for processing in due course

[xiusin]

@shove70 C.sqlite3_column_name I think this should be a function to get the field name, but it's not a public function

[xiusin]

@JalonSolov Most of our usage scenarios are not simply viewing the structure of a data table, but complex queries, which cannot be applied in this case.

[shove70]

@shove70 C.sqlite3_column_name I think this should be a function to get the field name, but it's not a public function

Yes, it is a field name query against the query result set. If you changed it to the pub method, you would also have to manually query the data using c.qlite3_prepare_v2, which is still inconvenient.

The best way to do this is to optimize the query result wrapper to include row and column informations.

[JalonSolov]

Or, perhaps better yet, make it support the SHOW statement so normal SQL can be used (and changed under the covers, if necessary, for the supported databases).

[shove70]

Or, perhaps better yet, make it support the SHOW statement so normal SQL can be used (and changed under the covers, if necessary, for the supported databases).

Well, this can be very useful in some situations.