SQLite Forum

Feature Request : Get table name alias from prepared statement
Login

Feature Request : Get table name alias from prepared statement

(1) By Decker (d3x0r) on 2020-03-12 21:46:04 [source]

I was going to track down sqlite mailing list messages that relate; so I wouldn't have to repeat myself, but found that the mailing list archives aren't public, so, that's not really viable.

Please do consider extending the family of functions to get statement names to include a method to get the alias of a table name from a query. The only thing one can get now is the original table name.

const char *sqlite3_column_database_name(sqlite3_stmt*,int);
const void *sqlite3_column_database_name16(sqlite3_stmt*,int);
const char *sqlite3_column_table_name(sqlite3_stmt*,int);
const void *sqlite3_column_table_name16(sqlite3_stmt*,int);
const char *sqlite3_column_origin_name(sqlite3_stmt*,int);
const void *sqlite3_column_origin_name16(sqlite3_stmt*,int);

really needs to include

const char *sqlite3_column_table_alias(sqlite3_stmt*,int);
const void *sqlite3_column_table_alias16(sqlite3_stmt*,int);

(2) By Decker (d3x0r) on 2021-01-12 17:39:11 in reply to 1 [link] [source]

updated github sqlite3_column_table_alias branch patch against official sqlite/sqlite github mirror.

noticed it is currently lacking additional test scripts.

This request goes back much further, which even included patch code... Nov 01, 2014; sqlite-column-table-name-and-table-alias-name (nabble)


So 3 yeas ago(nearly) there was this first message I saw...

http://sqlite.1065341.n5.nabble.com/Re-sqlite-column-table-name-and-table-alias-name-td100016.html (Jan 14, 2018)

Original message with patched code

http://sqlite.1065341.n5.nabble.com/Feat-Req-Get-Aliased-Table-Name-td101023.html#a105525

Mar 18, 2018 suggested a patch Jan 24, 2019 a year later... suggested a patch... 2020-03-12 another year I started this thread when the forum started

2021-01-12 maybe this is the year this feature can get implemented?

It's not like this would make sqlite be the only one in the world doing a thing, in fact it makes it so sqlite ISN'T the only one NOT able to return specified table alias.

			SQLColAttribute( pCollect->hstmt, col+1, SQL_DESC_BASE_TABLE_NAME, colname, 256, &len, NULL );

vs

			SQLColAttribute( pCollect->hstmt, col+1, SQL_DESC_TABLE_NAME, colname, 256, &len, NULL );

The first code is the only mode Sqlite is able to return... the second mode is the more basic table name, which would be the alias, if specified in the query.

This functionality has nothing to do with automatic names in queries from calculations or other nested select information...but returning the information as specified from the query. (as http://sqlite.1065341.n5.nabble.com/Re-sqlite-column-table-name-and-table-alias-name-tp100016p100033.html this part of the thread got off into)

(3.2) By Decker (d3x0r) on 2021-01-23 22:19:06 edited from 3.1 in reply to 2 [link] [source]

Fixed test for additional sqlite3_column_table_alias and sqlite3_column_table_alias16

Single change diff

Where the information comes from

which after fetched from the query is set in the meta data...

sqlite3VdbeSetColName( COLNAME_TABLE_ALIAS,... )

COLNAME_TABLE_ALIAS is an additional symbol here.

(6) By Decker (d3x0r) on 2021-02-23 04:59:56 in reply to 3.2 [link] [source]

I was working on some tutorial type information for my Sqlite(ODBC)->JS interface node.js addon... So... this is an example application that can benefit from this additional information.

https://github.com/d3x0r/sack.vfs/blob/master/tutorial/tutorial-sqlite.md#sqlite

in the case of name conflicts, the table name results in the query object, rather than just the column name (so one can distinguish one name from another name)

Section on overlapped names...

console.table( db.do( "select fruit.name,color.name from fruit as f \
       join fruit_color fc on fruit_color.fruit_id=fruit.fruit_id \
       join color c on fruit_color.color_id=color.color_id" ) );

┌─────────┬────────────────────┬────────────────────┬──────────────────────────────────────────────────┐
│ (index) │         f          │         c          │                       name                       │
├─────────┼────────────────────┼────────────────────┼──────────────────────────────────────────────────┤
│    0    │ { name: 'apple' }  │  { name: 'red' }   │     [ 'apple', 'red', f: 'apple', c: 'red' ]     │
│    1    │ { name: 'orange' } │ { name: 'orange' } │ [ 'orange', 'orange', f: 'orange', c: 'orange' ] │
│    2    │ { name: 'banana' } │ { name: 'yellow' } │ [ 'banana', 'yellow', f: 'banana', c: 'yellow' ] │
└─────────┴────────────────────┴────────────────────┴──────────────────────────────────────────────────┘

And it is handy, if such a query is composed, to be able to rename some other query which uses a different table, but provides an alias for the table name to match what other code expects.

No Parsing required... I know there's some pragma I can issue, but I'm not really in charge of what the application wants to issue for pragmas so that's not really up to me. They could indeed get back long names and work that way also... but this is just a SQLite ->JS interface, not an ORM.

and mind you this already works as expected through ODBC to MySQL/Postgres/mssql,.... probably mostly everything other than sqlite (as it is)

(7) By Decker (d3x0r) on 2021-11-16 20:52:54 in reply to 6 [link] [source]

really kinda silly - someone spent some time making this super easy to extend, and noone actually wants to use that ability :)

This is just for reference, I understand there's apparently a difference between open source and open contribution; I don't care if you don't use my exact bytes to provide the functionality; be contrarian.

https://github.com/d3x0r/sqlite/tree/sqlite3_column_table_alias (which has a git PR representing the diff between master and it; not this is relevant information)

xx.diff

 src/loadext.c    |   6 ++
 src/select.c     |  25 +++++---
 src/sqlite.h.in  |   7 ++-
 src/sqlite3ext.h |   6 ++
 src/test1.c      |   2 +
 src/vdbe.h       |  13 ++--
 src/vdbeapi.c    |  17 +++++
 test/capi3f.test | 188 +++++++++++++++++++++++++++++++++++++++++++++++++++++++
 8 files changed, 249 insertions(+), 15 deletions(-)

diff --git a/src/loadext.c b/src/loadext.c
index 4edefec0c..0fb2c41e5 100644
--- a/src/loadext.c
+++ b/src/loadext.c
@@ -30,6 +30,8 @@
 # define sqlite3_column_database_name16 0
 # define sqlite3_column_table_name      0
 # define sqlite3_column_table_name16    0
+# define sqlite3_column_table_alias     0
+# define sqlite3_column_table_alias16   0
 # define sqlite3_column_origin_name     0
 # define sqlite3_column_origin_name16   0
 #endif
@@ -61,6 +63,7 @@
 # define sqlite3_value_text16le         0
 # define sqlite3_column_database_name16 0
 # define sqlite3_column_table_name16    0
+# define sqlite3_column_table_alias16   0
 # define sqlite3_column_origin_name16   0
 #endif
 
@@ -485,6 +488,9 @@ static const sqlite3_api_routines sqlite3Apis = {
   sqlite3_total_changes64,
   /* Version 3.37.0 and later */
   sqlite3_autovacuum_pages,
+  /* Version 3.38.0 and later */
+  sqlite3_column_table_alias,
+  sqlite3_column_table_alias16,
 };
 
 /* True if x is the directory separator character
diff --git a/src/select.c b/src/select.c
index ebb746467..e50b42c49 100644
--- a/src/select.c
+++ b/src/select.c
@@ -1751,9 +1751,9 @@ static void generateSortTail(
 ** the SQLITE_ENABLE_COLUMN_METADATA compile-time option is used.
 */
 #ifdef SQLITE_ENABLE_COLUMN_METADATA
-# define columnType(A,B,C,D,E) columnTypeImpl(A,B,C,D,E)
+# define columnType(A,B,C,D,E,F) columnTypeImpl(A,B,C,D,E,F)
 #else /* if !defined(SQLITE_ENABLE_COLUMN_METADATA) */
-# define columnType(A,B,C,D,E) columnTypeImpl(A,B)
+# define columnType(A,B,C,D,E,F) columnTypeImpl(A,B)
 #endif
 static const char *columnTypeImpl(
   NameContext *pNC, 
@@ -1763,7 +1763,8 @@ static const char *columnTypeImpl(
   Expr *pExpr,
   const char **pzOrigDb,
   const char **pzOrigTab,
-  const char **pzOrigCol
+  const char **pzOrigCol,
+  const char **pzOrigTabAlias
 #endif
 ){
   char const *zType = 0;
@@ -1772,6 +1773,7 @@ static const char *columnTypeImpl(
   char const *zOrigDb = 0;
   char const *zOrigTab = 0;
   char const *zOrigCol = 0;
+  char const *zOrigTabAlias = 0;
 #endif
 
   assert( pExpr!=0 );
@@ -1791,6 +1793,10 @@ static const char *columnTypeImpl(
         if( j<pTabList->nSrc ){
           pTab = pTabList->a[j].pTab;
           pS = pTabList->a[j].pSelect;
+#ifdef SQLITE_ENABLE_COLUMN_METADATA
+          zOrigTabAlias =
+              pTabList->a[j].zAlias?pTabList->a[j].zAlias:pTabList->a[j].zName;
+#endif
         }else{
           pNC = pNC->pNext;
         }
@@ -1839,7 +1845,7 @@ static const char *columnTypeImpl(
           sNC.pSrcList = pS->pSrc;
           sNC.pNext = pNC;
           sNC.pParse = pNC->pParse;
-          zType = columnType(&sNC, p,&zOrigDb,&zOrigTab,&zOrigCol); 
+          zType = columnType(&sNC, p,&zOrigDb,&zOrigTab,&zOrigCol,&zOrigTabAlias);
         }
       }else{
         /* A real table or a CTE table */
@@ -1885,7 +1891,7 @@ static const char *columnTypeImpl(
       sNC.pSrcList = pS->pSrc;
       sNC.pNext = pNC;
       sNC.pParse = pNC->pParse;
-      zType = columnType(&sNC, p, &zOrigDb, &zOrigTab, &zOrigCol); 
+      zType = columnType(&sNC, p, &zOrigDb, &zOrigTab, &zOrigCol, &zOrigTabAlias);
       break;
     }
 #endif
@@ -1897,6 +1903,7 @@ static const char *columnTypeImpl(
     *pzOrigDb = zOrigDb;
     *pzOrigTab = zOrigTab;
     *pzOrigCol = zOrigCol;
+    *pzOrigTabAlias = zOrigTabAlias;
   }
 #endif
   return zType;
@@ -1925,7 +1932,8 @@ static void generateColumnTypes(
     const char *zOrigDb = 0;
     const char *zOrigTab = 0;
     const char *zOrigCol = 0;
-    zType = columnType(&sNC, p, &zOrigDb, &zOrigTab, &zOrigCol);
+    const char *zOrigTabAlias = 0;
+    zType = columnType(&sNC, p, &zOrigDb, &zOrigTab, &zOrigCol, &zOrigTabAlias);
 
     /* The vdbe must make its own copy of the column-type and other 
     ** column specific strings, in case the schema is reset before this
@@ -1934,8 +1942,9 @@ static void generateColumnTypes(
     sqlite3VdbeSetColName(v, i, COLNAME_DATABASE, zOrigDb, SQLITE_TRANSIENT);
     sqlite3VdbeSetColName(v, i, COLNAME_TABLE, zOrigTab, SQLITE_TRANSIENT);
     sqlite3VdbeSetColName(v, i, COLNAME_COLUMN, zOrigCol, SQLITE_TRANSIENT);
+    sqlite3VdbeSetColName(v, i, COLNAME_TABLE_ALIAS, zOrigTabAlias, SQLITE_TRANSIENT);
 #else
-    zType = columnType(&sNC, p, 0, 0, 0);
+    zType = columnType(&sNC, p, 0, 0, 0, 0);
 #endif
     sqlite3VdbeSetColName(v, i, COLNAME_DECLTYPE, zType, SQLITE_TRANSIENT);
   }
@@ -2199,7 +2208,7 @@ void sqlite3SelectAddColumnTypeAndCollation(
     int n, m;
     pTab->tabFlags |= (pCol->colFlags & COLFLAG_NOINSERT);
     p = a[i].pExpr;
-    zType = columnType(&sNC, p, 0, 0, 0);
+    zType = columnType(&sNC, p, 0, 0, 0, 0);
     /* pCol->szEst = ... // Column size est for SELECT tables never used */
     pCol->affinity = sqlite3ExprAffinity(p);
     if( zType ){
diff --git a/src/sqlite.h.in b/src/sqlite.h.in
index 98a028b0b..d86391bce 100644
--- a/src/sqlite.h.in
+++ b/src/sqlite.h.in
@@ -4656,7 +4656,10 @@ const void *sqlite3_column_name16(sqlite3_stmt*, int N);
 ** ^The name of the database or table or column can be returned as
 ** either a UTF-8 or UTF-16 string.  ^The _database_ routines return
 ** the database name, the _table_ routines return the table name, and
-** the origin_ routines return the column name.
+** the origin_ routines return the column name.  _table_alias_ results
+** with the alias(if any) of the table associated with the column;
+** _table_ always returns the source table name, even if it has been
+** aliased, this returns the original table name if there is no alias.
 ** ^The returned string is valid until the [prepared statement] is destroyed
 ** using [sqlite3_finalize()] or until the statement is automatically
 ** reprepared by the first call to [sqlite3_step()] for a particular run
@@ -4694,6 +4697,8 @@ const char *sqlite3_column_table_name(sqlite3_stmt*,int);
 const void *sqlite3_column_table_name16(sqlite3_stmt*,int);
 const char *sqlite3_column_origin_name(sqlite3_stmt*,int);
 const void *sqlite3_column_origin_name16(sqlite3_stmt*,int);
+const char *sqlite3_column_table_alias(sqlite3_stmt*,int);
+const void *sqlite3_column_table_alias16(sqlite3_stmt*,int);
 
 /*
 ** CAPI3REF: Declared Datatype Of A Query Result
diff --git a/src/sqlite3ext.h b/src/sqlite3ext.h
index 9767daa01..cecb80691 100644
--- a/src/sqlite3ext.h
+++ b/src/sqlite3ext.h
@@ -344,6 +344,9 @@ struct sqlite3_api_routines {
   int (*autovacuum_pages)(sqlite3*,
      unsigned int(*)(void*,const char*,unsigned int,unsigned int,unsigned int),
      void*, void(*)(void*));
+  /* Version 3.38.0 and later */
+  const char * (*column_table_alias)(sqlite3_stmt*,int);
+  const void * (*column_table_alias16)(sqlite3_stmt*,int);
 };
 
 /*
@@ -655,6 +658,9 @@ typedef int (*sqlite3_loadext_entry)(
 #define sqlite3_total_changes64        sqlite3_api->total_changes64
 /* Version 3.37.0 and later */
 #define sqlite3_autovacuum_pages       sqlite3_api->autovacuum_pages
+/* Version 3.38.0 and later */
+#define sqlite3_column_table_alias     sqlite3_api->column_table_alias
+#define sqlite3_column_table_alias16   sqlite3_api->column_table_alias16
 #endif /* !defined(SQLITE_CORE) && !defined(SQLITE_OMIT_LOAD_EXTENSION) */
 
 #if !defined(SQLITE_CORE) && !defined(SQLITE_OMIT_LOAD_EXTENSION)
diff --git a/src/test1.c b/src/test1.c
index 6060290b9..c0d8a4682 100644
--- a/src/test1.c
+++ b/src/test1.c
@@ -8506,6 +8506,7 @@ int Sqlitetest1_Init(Tcl_Interp *interp){
 #ifdef SQLITE_ENABLE_COLUMN_METADATA
 { "sqlite3_column_database_name",test_stmt_utf8,(void*)sqlite3_column_database_name},
 { "sqlite3_column_table_name",test_stmt_utf8,(void*)sqlite3_column_table_name},
+{ "sqlite3_column_table_alias",test_stmt_utf8,(void*)sqlite3_column_table_alias},
 { "sqlite3_column_origin_name",test_stmt_utf8,(void*)sqlite3_column_origin_name},
 #endif
 
@@ -8521,6 +8522,7 @@ int Sqlitetest1_Init(Tcl_Interp *interp){
 {"sqlite3_column_database_name16",
   test_stmt_utf16, (void*)sqlite3_column_database_name16},
 {"sqlite3_column_table_name16", test_stmt_utf16, (void*)sqlite3_column_table_name16},
+{"sqlite3_column_table_alias16", test_stmt_utf16, (void*)sqlite3_column_table_alias16},
 {"sqlite3_column_origin_name16", test_stmt_utf16, (void*)sqlite3_column_origin_name16},
 #endif
 #endif
diff --git a/src/vdbe.h b/src/vdbe.h
index 3257ff68a..1ee2b66e3 100644
--- a/src/vdbe.h
+++ b/src/vdbe.h
@@ -140,13 +140,14 @@ typedef struct VdbeOpList VdbeOpList;
 ** The Vdbe.aColName array contains 5n Mem structures, where n is the 
 ** number of columns of data returned by the statement.
 */
-#define COLNAME_NAME     0
-#define COLNAME_DECLTYPE 1
-#define COLNAME_DATABASE 2
-#define COLNAME_TABLE    3
-#define COLNAME_COLUMN   4
+#define COLNAME_NAME        0
+#define COLNAME_DECLTYPE    1
+#define COLNAME_DATABASE    2
+#define COLNAME_TABLE       3
+#define COLNAME_COLUMN      4
+#define COLNAME_TABLE_ALIAS 5
 #ifdef SQLITE_ENABLE_COLUMN_METADATA
-# define COLNAME_N        5      /* Number of COLNAME_xxx symbols */
+# define COLNAME_N          6      /* Number of COLNAME_xxx symbols */
 #else
 # ifdef SQLITE_OMIT_DECLTYPE
 #   define COLNAME_N      1      /* Store only the name */
diff --git a/src/vdbeapi.c b/src/vdbeapi.c
index 5eeb5d1c0..6548b6a85 100644
--- a/src/vdbeapi.c
+++ b/src/vdbeapi.c
@@ -1321,6 +1321,23 @@ const void *sqlite3_column_origin_name16(sqlite3_stmt *pStmt, int N){
   return columnName(pStmt, N, 1, COLNAME_COLUMN);
 }
 #endif /* SQLITE_OMIT_UTF16 */
+
+/*
+** Return the alias or, if no alias specified, the name of the table from
+** which a result column derives. NULL is returned if the result name is
+** an expression or constant or anything else which is not an unambiguous
+** reference to a database table.
+*/
+const char *sqlite3_column_table_alias(sqlite3_stmt *pStmt, int N){
+  return columnName(
+      pStmt, N, 0, COLNAME_TABLE_ALIAS);
+}
+#ifndef SQLITE_OMIT_UTF16
+const void *sqlite3_column_table_alias16(sqlite3_stmt *pStmt, int N){
+  return columnName(
+      pStmt, N, 1, COLNAME_TABLE_ALIAS);
+}
+#endif /* SQLITE_OMIT_UTF16 */
 #endif /* SQLITE_ENABLE_COLUMN_METADATA */
 
 
diff --git a/test/capi3f.test b/test/capi3f.test
new file mode 100644
index 000000000..ca4efab4c
--- /dev/null
+++ b/test/capi3f.test
@@ -0,0 +1,188 @@
+# 2021 January 22
+#
+# The author disclaims copyright to this source code.  In place of
+# a legal notice, here is a blessing:
+#
+#    May you do good and not evil.
+#    May you find forgiveness for yourself and forgive others.
+#    May you share freely, never taking more than you give.
+#
+#***********************************************************************
+# This file implements regression tests for SQLite library.  The
+# focus of this script testing the callback-free C/C++ API.
+#
+# $Id: capi3f.test,v 1.70 2009/01/09 02:49:32 d3x0r Exp $
+#
+
+set testdir [file dirname $argv0]
+source $testdir/tester.tcl
+set ::testprefix capi3f
+
+# Do not use a codec for tests in this file, as the database file is
+# manipulated directly using tcl scripts (using the [hexio_write] command).
+#
+do_not_use_codec
+
+# Return the UTF-16 representation of the supplied UTF-8 string $str.
+# If $nt is true, append two 0x00 bytes as a nul terminator.
+proc utf16 {str {nt 1}} {
+  set r [encoding convertto unicode $str]
+  if {$nt} {
+    append r "\x00\x00"
+  }
+  return $r
+}
+
+# Return the UTF-8 representation of the supplied UTF-16 string $str. 
+proc utf8 {str} {
+  # If $str ends in two 0x00 0x00 bytes, knock these off before
+  # converting to UTF-8 using TCL.
+  binary scan $str \c* vals
+  if {[lindex $vals end]==0 && [lindex $vals end-1]==0} {
+    set str [binary format \c* [lrange $vals 0 end-2]]
+  }
+
+  set r [encoding convertfrom unicode $str]
+  return $r
+}
+
+
+# This proc is used to test the following API calls:
+#
+# sqlite3_column_origin_name
+# sqlite3_column_origin_name16
+# sqlite3_column_table_name
+# sqlite3_column_table_name16
+# sqlite3_column_database_name
+# sqlite3_column_database_name16
+# sqlite3_column_table_alias
+# sqlite3_column_table_alias16
+
+#
+# $STMT is a compiled SQL statement. $test is a prefix
+# to use for test names within this proc. $names is a list
+# of the column names that should be returned by $STMT.
+# $decltypes is a list of column declaration types for $STMT.
+#
+# Example:
+#
+# set STMT [sqlite3_prepare "SELECT 1, 2, 2;" -1 DUMMY]
+# check_header test1.1 {1 2 3} {"" "" ""}
+#
+proc check_origin_header2 {STMT test dbs tables als cols} {
+  # If sqlite3_column_origin_name() and friends are not compiled into
+  # this build, this proc is a no-op.
+  ifcapable columnmetadata {
+    # Use the return value of sqlite3_column_count() to build
+    # a list of column indexes. i.e. If sqlite3_column_count
+    # is 3, build the list {0 1 2}.
+    set ::idxlist [list]
+    set ::numcols [sqlite3_column_count $STMT]
+    for {set i 0} {$i < $::numcols} {incr i} {lappend ::idxlist $i}
+  
+    # Database names in UTF-8
+    do_test $test.1 {
+      set cnamelist [list]
+      foreach i $idxlist {
+        lappend cnamelist [sqlite3_column_database_name $STMT $i]
+      } 
+      set cnamelist
+    } $dbs
+  
+    # Database names in UTF-16
+    ifcapable {utf16} {
+      do_test $test.2 {
+        set cnamelist [list]
+        foreach i $idxlist {
+          lappend cnamelist [utf8 [sqlite3_column_database_name16 $STMT $i]]
+        }
+        set cnamelist
+      } $dbs
+    }
+  
+    # Table alias in UTF-8
+    do_test $test.3 {
+      set caliaslist [list]
+      foreach i $idxlist {
+        lappend caliaslist [sqlite3_column_table_alias $STMT $i]
+      } 
+      set caliaslist
+    } $als
+  
+    # Table alias in UTF-16
+    ifcapable {utf16} {
+      do_test $test.4 {
+        set caliaslist [list]
+        foreach i $idxlist {
+          lappend caliaslist [utf8 [sqlite3_column_table_alias16 $STMT $i]]
+        }
+        set caliaslist
+      } $als
+    }
+
+    # Table names in UTF-8
+    do_test $test.5 {
+      set cnamelist [list]
+      foreach i $idxlist {
+        lappend cnamelist [sqlite3_column_table_name $STMT $i]
+      } 
+      set cnamelist
+    } $tables
+  
+    # Table names in UTF-16
+    ifcapable {utf16} {
+      do_test $test.6 {
+        set cnamelist [list]
+        foreach i $idxlist {
+          lappend cnamelist [utf8 [sqlite3_column_table_name16 $STMT $i]]
+        }
+        set cnamelist
+      } $tables
+    }
+  
+    # Origin names in UTF-8
+    do_test $test.7 {
+      set cnamelist [list]
+      foreach i $idxlist {
+        lappend cnamelist [sqlite3_column_origin_name $STMT $i]
+      } 
+      set cnamelist
+    } $cols
+  
+    # Origin declaration types in UTF-16
+    ifcapable {utf16} {
+      do_test $test.8 {
+        set cnamelist [list]
+        foreach i $idxlist {
+          lappend cnamelist [utf8 [sqlite3_column_origin_name16 $STMT $i]]
+        }
+        set cnamelist
+      } $cols
+    }
+  }
+}
+
+do_test capi3f-1 {
+  execsql {
+    CREATE TABLE t1(a VARINT, b BLOB, c VARCHAR(16));
+    INSERT INTO t1 VALUES(1, 2, 3);
+    INSERT INTO t1 VALUES('one', 'two', NULL);
+    INSERT INTO t1 VALUES(1.2, 1.3, 1.4);
+  }
+  set sql "SELECT * FROM t1 tz"
+  set STMT [sqlite3_prepare_v2 $DB $sql -1 TAIL]
+  sqlite3_column_count $STMT
+} 3
+
+check_origin_header2 $STMT capi3f-1 {main main main} {t1 t1 t1} {tz tz tz} {a b c}
+
+do_test capi3f-2 {
+  set sql2 "SELECT * FROM t1"
+  set STMT [sqlite3_prepare_v2 $DB $sql2 -1 TAIL]
+  sqlite3_column_count $STMT
+} 3
+
+check_origin_header2 $STMT capi3f-2 {main main main} {t1 t1 t1} {t1 t1 t1} {a b c}
+
+
+finish_test

(4) By Stephan Beal (stephan) on 2021-01-23 08:48:36 in reply to 1 [link] [source]

The only thing one can get now is the original table name.

Are you sure?

https://www.sqlite.org/capi3ref.html#sqlite3_column_name

These routines return the name assigned to a particular column in the result set of a SELECT statement. ... The name of a result column is the value of the "AS" clause for that column, if there is an AS clause. If there is no AS clause then the name of the column is unspecified and may change from one release of SQLite to the next.

(5) By Decker (d3x0r) on 2021-01-23 09:28:07 in reply to 4 [link] [source]

Table name/alias, not column name/alias... or the alias/name of the table the column is from...

(8) By benstd on 2022-06-18 08:45:10 in reply to 5 [link] [source]

I was looking for this functionality for an application where I need to output data to an external stream (Kafka). I however need the actual (not alias) table name. Is that possible as well?

This functionality looks quite generic, it would be nice if it makes it into the original sources.

(15) By Simon Slavin (slavin) on 2024-01-30 12:12:44 in reply to 8 [link] [source]

Would you want from the following ?

SELECT x FROM XList UNION y FROM YList

(9) By Decker (d3x0r) on 2023-02-23 15:34:26 in reply to 1 [link] [source]

Can this feature be considered this year maybe?

(10) By vincent on 2023-10-08 20:31:19 in reply to 1 [link] [source]

sqlite3_column_table_alias() is dearly needed.

It lets us to differentiate columns from joined tables without having to manually alias each column in the SELECT statement.

Currently, this simple statement is practically out of hands:

SELECT * FROM person AS p LEFT JOIN person AS f ON p.fatherId = f.id;

The only option now:

SELECT p.id AS id, p.name AS name, f.id AS fatherId, f.name AS fatherName
FROM person AS p
LEFT JOIN person AS f ON p.fatherId = f.id

(11) By Decker (d3x0r) on 2024-01-27 19:24:12 in reply to 1 [link] [source]

Maybe the 10th year is the charm? Please, pretty please with sugar on top can this be implemented?

(12) By Gunter Hick (gunter_hick) on 2024-01-29 12:33:09 in reply to 11 [link] [source]

SELECT * FROM ... is supposed to be used for ad-hoc queries only, because this means that you are relying on neither generated column names nor the data model changing.

You should at least be using an explicit result column list so that you can access the proper column by numeric index in your application. Or, if you must rely on column names, give them explicitly in your AS clauses.

(13) By Tim Streater (Clothears) on 2024-01-29 13:05:16 in reply to 12 [link] [source]

I consider both of these suggestions to be unsatisfactory.

I have several tables with between 30 and 50 columns, others a lot shorter. Using names assists in making the code using them be self-documenting. Using the numeric index will make the code less readable and increase the chance of overlooking a counting error.

(14) By Kees Nuyt (knu) on 2024-01-29 14:54:42 in reply to 13 [link] [source]

You don't have to type all of those names by hand:

#!/bin/bash
#
# shcol.bash 2024-01 by Kees Nuyt
#
db="$1"
if [ -z "$db" ]
then
   printf '*** Missing parameter: [/path/to/]databasefile\nAbend.\n'
   exit 1
fi
for t in $(sqlite3 "$db" "SELECT name FROM sqlite_master WHERE type=='table' ORDER BY name")
do
   sqlite3 "$db" "SELECT '$t' AS tname,cid,name,type FROM pragma_table_info('$t')"
done | sort -t '|' -k1,1 -k2,2n | gawk -F '|' 'BEGIN{
   prvtbl = ""
   qt     ="\x27"
   inittbl()
}
function inittbl(){
   collst = vallst = ""
}
function flushtbl(tbl){
   printf "%s (%s) VALUES (%s);\n",tbl,collst,vallst
   inittbl()
}
($1 != prvtbl){
   if (prvtbl > "") flushtbl(prvtbl)
   prvtbl = $1
}
{
   cid  = 0 + $2
   coln = $3
   colt = toupper($4)
   switch (colt) {
   case /(TEXT|CHAR)/:
      vals = sprintf("%s%%s%s",qt,qt)
      break
   case /(INTEGER|BIGINT)/:
      vals = "%d"
      break
   case /(DOUBLE|FLOAT|REAL)/:
      vals = "%18.16g"
      break
   default:
      vals = "%s"
   }
   collst = collst (cid>0?",":"") coln
   vallst = vallst (cid>0?",":"") vals
}
END{
   flushtbl(prvtbl)
}'
-- 
enjoy
Kees Nuyt