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.
;-)
> 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
------------------------------------------------------------------------------
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
