On Fri, Oct 11, 2013 at 8:03 PM, Art Heimsoth <artstore@artheimsoth.com> wrote:
 
I have a dumb question..

Hi Art,

Not a dumb question, just a question about something you don't yet know.  ;-)

 
I am trying to get the column names
in an ooSQLite database.  I think I need to use the pragma(table_info,tablename)
in an ooSQLiteConnection statement, which returns an Array of Directories
where each Array entry is one column and the Directory contains the column
attributes.  Where I am having trouble is figuring out how to retrieve the
individual directory entries.  Do you have an example or something that
will show how this is done where the names of the column are not known?

There are several ways of doing this.  And, I'm not 100% sure if you mean the names of the columns returned by the pragma statement, or the names of the columns in the table in the database.

First off, I'd use the record format of an array of arrays.  Getting the column names is straight forward then.

Here is an example of using the pragma table_info.  It also has a sort of generic routine, printResult() which can be used to print the result set when it is in the format of an array of array:

  dbFile = 'ooFoods.rdbx'

  db = .ooSQLiteConnection~new(dbFile,                   -
                               .ooSQLite~OPEN_READWRITE, -
                               .ooSQLite~OO_ARRAY_OF_ARRAYS)

  ret = db~pragma('table_info', 'foods')
  z = printResult(ret)

  db~close

::requires 'ooSQLite.cls'

::routine printResult
  use arg ret

  if ret~items == 0 then do
    say 'Result: No data'
    say; say; say
    return 0
  end

  say 'Result:'

  colCount = ret[1]~items
  rowCount = ret~items

  width   = 11
  heading = ''
  record  = ret[1]

  do j = 1 to colCount
    heading ||= record[j]~left(width)
  end

  say heading
  say '='~copies(59)

  do i = 2 to rowCount
    line = ''
    record = ret[i]

    do j = 1 to colCount
      col = record[j]
      if col == .nil then col = 'NULL'
      line ||= col~left(width)
    end

    say line
  end
  say; say; say

  return 0

If you run that, then you see this output:

Result:
cid        name       type       notnull    dflt_value pk
==========================================================
0          id         integer    0          NULL       1
1          type_id    integer    0          NULL       0
2          name       text       0          NULL       0

  
(I actually fudged and first ran it with width 15 and copies(80).  Then changed the numbers so the table output would not be so wide.)

Now you know 2 things.  You know that the 'name' column lists the names of the columns in the table 'foods'.  You also know that in the format array of arrays it is the second column you are interested in.

SQLite 3 doesn't actually document that explicitly, but you know it won't change.  You could then write this generic code to get a list of column names from any database table:

 /* columnNames.rex */

  dbFile = 'ooFoods.rdbx'
  table  = 'foods'

  colNames = getColumnNames(dbFile, table)

  -- Check for .nil and handle it ...
  say 'Column names:' colNames

::requires 'ooSQLite.cls'

::routine getColumnNames
  use strict arg dbFile, table

  .ooSQLite~recordFormat = .ooSQLite~OO_ARRAY_OF_ARRAYS
  dbConn = .ooSQLiteConnection~new(dbFile, .ooSQLite~OPEN_READWRITE)
  -- Check for error and handle it

  rs = dbConn~pragma('table_info', table)
  dbConn~close

  if rs~items = 0 then return .nil

  colNamesStr = ''
  do i = 2 to rs~items
    row = rs[i]
    colNamesStr ||= row[2]', '
  end
  colNamesStr = colNamesStr~left(colNamesStr~length - 2)

  return colNamesStr

Then of course you could return the column names as an array of names, or some other format.

If you start out knowing the table_info result set has the 'name' column, you can use the format array of directories like this:

::routine getColumnNames
  use strict arg dbFile, table

  dbConn = .ooSQLiteConnection~new(dbFile, .ooSQLite~OPEN_READWRITE)
  -- Check for error and handle it

  rs = dbConn~pragma('table_info', table)
  dbConn~close

  if rs~items = 0 then return .nil

  colNamesStr = ''
  do i = 1 to rs~items
    colNamesStr ||= rs[i]~name', '
  end
  colNamesStr = colNamesStr~left(colNamesStr~length - 2)

  return colNamesStr

You don't have to use the pragma.  Another approach would be to just pull one row from the table you are interested in and grab the column names:

  dbFile = 'ooFoods.rdbx'

  db = .ooSQLiteConnection~new(dbFile,                   -
                               .ooSQLite~OPEN_READWRITE, -
                               .ooSQLite~OO_ARRAY_OF_ARRAYS)

  sql = 'SELECT * from foods where rowid = 1'
  rs = db~exec(sql, .true)

  colNamesStr = ''
  heading = rs[1]
  do i = 1 to heading~items
    colNamesStr ||= heading[i]', '
  end
  colNamesStr = colNamesStr~left(colNamesStr~length - 2)
  say 'Column Names:' colNamesStr

  db~close

::requires 'ooSQLite.cls'


And finally if you do have an array of directories, you can get the column names like this:

  dbFile = 'ooFoods.rdbx'

  db = .ooSQLiteConnection~new(dbFile, .ooSQLite~OPEN_READWRITE)

  rs = db~pragma('table_info', 'foods')
  row = rs[1]

  do colName over row
    say 'column name:' colName
  end

  db~close

::requires 'ooSQLite.cls'


Or this:

  dbFile = 'ooFoods.rdbx'

  db = .ooSQLiteConnection~new(dbFile, .ooSQLite~OPEN_READWRITE)

  rs = db~pragma('table_info', 'foods')
  indexes = rs[1]~allIndexes

  do colName over indexes
    say 'column name:' colName
  end

  db~close

::requires 'ooSQLite.cls'

--
Mark Miesfeld