How to get SQLite column IdxField using Pragma statement?

Hello all,
Using the following code I can get much of the structural information about a column. However, I cannot find what the correct key words are to use to get the field index. Can anyone tell me what it is?

Thanks,
Tim

  dim rs as recordSet
  
  dim col, columns(-1) as dictionary
  //Dim table as string = "database_version"
  
  Dim table as string = Tbl
  
  sql = "pragma table_info( '"+ table +"' )"
  rs = db.sqlSelect( sql )
  
  If rs = Nil Then 
    Return 0
    
  ElseIf db.Error = True Then
    dbErrorNo = db.ErrorCode
    Return  0
  End If
  
  while rs.eof = false
    col = new dictionary
    col.value( "column" ) = rs.field( "name" ).stringValue
    col.value( "type" ) = rs.field( "type" ).stringValue
    col.value( "notnull" ) = (rs.field( "notnull" ).integerValue = 1)
    col.value( "primary" ) = (rs.field( "pk" ).integerValue = 1)
    col.value( "default" ) = rs.field( "dflt_value" ).stringValue
    
    columns.append col
    rs.moveNext
  wend
  rs.close
  
  return Columns.Ubound

Not sure what you mean by the “field index” ?
You might also look at http://documentation.xojo.com/index.php/Database_Class and check out FieldSchema & TableSchema which already do this :stuck_out_tongue:

For SQLite, you can get the indexes from the sqlite_master table.

select name, sql from sqlite_master where type='index' and tbl_name='mytable'

The sql column will be the command that created the index, eg. “create index name on tablename (column)”

There’s also index schema on the DB class that gets whatever indexes for the table

Thanks for the responses guys. What I need is the index for the field, not the table. From the help:
RecordSet.IdxField

link text

It looks like SQLite has both an index for the table, as well as for each field in each row. idxField works with the index for the field. So, instead of naming the field, you provide an index number. I believe this can be had by using the Pragma commands. I just cannot find any reference to any of them - even the ones I used!, anywhere! The code I used, I found when I started working with RS 2 years ago. I still cannot find any document reference to the field/table defs as presented from the use of Pragma/Table.

I may not be stating this clearly enough, but hopefully, looking at the xojo doc article I gave a link to, will help clarify my needs.

Thanks again all!
Tim

OH OH OH … IDXField is not an index
It lets you go through fields in a record set using a column number instead of the name

Say you did something like

select sum(field), sum(someotherField) from table

then you would not have NAMES for the two columns so idxfield would let you get “the value from column 1” or “the value in column 2” of the result set

Hi Norman,

Right, exactly. But I need to know, what column name is what column number. Can I not get that through a Pragma SQL request?
What I am doing, is creating a report writer. During user creation, I save the column name. But for me to create generic code to obtain the data from a table/column/field has been elusive (see below for one set of trys). Using the IdxField removes this problem. So what I have to do now, is to get and save the IdxField of the table. Before I obtained and saved the ‘real field name’.

    // Dim t As String =  App.ReportDetails.RealDataType
    // Dim f As String = App.ReportDetails.RealFieldName
    // //Dim d as Variant = rs.Field(""" + f + """) + "." + t
    // 
    // Dim ss As String = ".Field(' "+ f + "')." + t

I spent a couple of hours messing with this today, then came across the IdxField. Tried it once, and perfect! But now need to get the idx field for each table…

Thanks again for the feed back!
Tim

A quick look at this site SQLite supported pragmas suggests not.

I may be wrong about this, but it seems that when one does a “SELECT * FROM…” query, the returned recordset will include the fields in the order you are looking for. Can anyone confirm or deny?

Hmmm… I cannot confirm or deny, but I have to say, I had not thought of that! In my case, I am choosing ‘*’ all fields…
Great point Peter - thanks for your response!

Tim

Also, Peter, what I cannot find, and did not see either in the SQLite docs are the specific ‘field’ names I used, as originally posted above, in the Pragma SQL Querie. I found that code, somewhere… sorry - I cannot give the original credit, sure wish I could! I had actually used those field names as search instruments on the SQLite website - but never found any reference to them. I also could not find any reference to them in the RS or Xojo docs.

Can anyone show where these are referenced???

Thanks again all!
Tim

Those “field names” are available by inspection. Open up an sqlite browser and issue the command “pragma table_info(sometable)” and pick the column names out of the output. That’s about the only way.

FYI, what I do in my report writer (that I will be presenting at the XDC) is I take the column names that I have stored and construct a string of column names for the SELECT statement. I also construct and specify an AS name for each of them. That way, I know the order (and therefore the IDXField) as well as a premade Field(name) to get them by. I can go either way. A simplistic example would be constructing a query like

SELECT table1.field1 AS column1, table2.field1 AS column2, etc

Then column2 can be retrieved as either IDXField(2) or Field(“column2”).

In general, with a report writer, you should specify the columns you want and avoid “select " at all costs. You just don’t know how much overhead you’ll incur with "”. You might only need 2 small bits of data out of a megabyte-size record. “*” gives you the whole thing, while “select column1, column2” might be just a few bytes.

Hi Tim,
Thank you for your input! I am in the early stages, having never done this before at all, so I kept it easy/simple with the wildcard. Having been fumbling through this, and no experience, I have no idea what kind of roadblocks or detours will be presented. This is not the first, and not the last I am sure! Using your ‘AS’ example is really cool and interesting!

I will not be at the XDC, I am sorry to say, but will your presentation be available elsewhere?

Thanks again Tim!
Tim

[quote=46275:@Tim Seyfarth]Hi Norman,

Right, exactly. But I need to know, what column name is what column number. Can I not get that through a Pragma SQL request?

[/quote]
No because the column NAME and index are related to the position in the record set returned and could change if you reorder the columns in the SELECT
They’re NOT fixed permanently so I think what your after makes no sense

For instance if I have a table with 3 columns (column1, column2, column3 ) then this query

select column1, column2, column3 from table

then the field names work regardless of order but idxfield 1 is column1, idxfield 2 is column 2, idxfield 3 is column3

If I then write this query as

select column3, column1, column2 from table

then the field names work regardless of order but idxfield 1 is column3, idxfield 2 is column 1, idxfield 3 is column2

Thanks Norman.

Tim got around that by using the ‘AS’ command [quote]SELECT table1.field1 AS column1, table2.field1 AS column2, etc[/quote]

I understand how to use this now.
Thanks again everyone!
Tim