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
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.
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…
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!
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.
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?
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
