> On Fri, Oct 11, 2013 at 8:03 PM, Art Heimsoth
> <artst...@artheimsoth.com> wrote:
>
>> I have a dumb question..
>>
>
> Hi Art,
>
>
> Not a dumb question, just a question about something you don't yet
> know. ;-)
>
As usual, you have helped me a lot, thanks.
>>
>> 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.
>
I am looking for names of the columns of 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
Thanks for all the examples here. They will be very helpful to me.
--
Art Heimsoth - artst...@artheimsoth.com
--
Art Heimsoth - artst...@artheimsoth.com
------------------------------------------------------------------------------ October Webinars: Code for Performance Free Intel webinars can help you accelerate application performance. Explore tips for MPI, OpenMP, advanced profiling, and more. Get the most from the latest Intel processors and coprocessors. See abstracts and register > http://pubads.g.doubleclick.net/gampad/clk?id=60134071&iu=/4140/ostg.clktrk
_______________________________________________ Oorexx-users mailing list Oorexx-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/oorexx-users
