RecordSet Count and Schema Count are different !

Databases
1

In the code below (I stripped the Nil checks, etc.), I get the MsgBox error that told me one have 6 fileds and the other have 11 fields !!!

[code] Heading_Data_RS = Caritas_db.SQLSelect(“SELECT * FROM Heading”)

Heading_Schema_RS = Caritas_db.FieldSchema(“Heading”)

// Get the number of Fields in the Heading RecordSet
Record_Schema_Cnt = Heading_Schema_RS.FieldCount
Record_Data_Cnt = Heading_Data_RS.FieldCount

If Record_Schema_Cnt <> Record_Data_Cnt Then
MsgBox “An error occured” + EndOfLine + EndOfLine +_
“Record_Schema_Cnt <> Record_Data_Cnt !” + EndOfLine +_
"Record_Schema_Cnt: " + Str(Record_Schema_Cnt) + EndOfLine +_
"Record_Data_Cnt: " + Str(Record_Data_Cnt)
End If[/code]

Now I understand why I get OutOfBoundsExceptions…

2

what is the sql definition of the table ?

3

which one is 6 and which one is 11?

4

What is the database-engine ? Recordcount is not supported by every database-type.

5

Heading_Schema_RS = Caritas_db.FieldSchema("Heading") ... Record_Data_Cnt = Heading_Data_RS.FieldCount // this is wrong, you need to use RecordCount here

6

Makes sense

FieldSchema gives you one row with 5 columns for EACH column in the database - see the doc

FieldCount from a select statement simply tells you how many columns there are

So FieldSchema.rowcount SHOULD equal recordset.fieldcount

7

So if you have a database with 10 records and 5 fields

FieldSchema will return 5 records… one each describing a field… where the fieldcount would be one for each ATTRIBUTE of the fields, and the count would be dependent on what information your database engine could return about each field

The other would be 10 indicating you had 10 data records

8

Hi all, thank you for your answers.

the table have 11 fields / 1 (one) record.

The DB Engine:
That db was created using SQLite. That db file is used in production / works fine in both read (Listbox / series of TextFields) / write.

The Table definition
That db is in use in another project (application) and works nice.
Also: I used a tool to watch the SQLExecute string, and read the whole (all) records: works nice.

I checked that SQLite db file and, yes, the Table exists, have one Record (only, as needed) and it have 11 fields. It is used to store / restore the Listbox header.

So if you have a database with 10 records and 5 fields
No, in that particular case / Table, the Table have only one Record.

Recordcount
No, I use FieldCount to get the information on that Tabl;e both information: Field Name (As found in the Table definition) and Field Data (the data stored in the Table).

I added the If block with Cnt lines because I had troubles with the reading of the two RecordSets. Since they are equal (this is two ways to get the informations about the same Table: one RecordSet is meant to get the Table Schema, the other one: the Data in the unique RecordSet of this Table).

BTW: this is symptomatic to what I have nearly everyday, so I quit Xojo, clears the Xojo Cache, left Firefox, SimpleText (yes, OS X), shutdown. Then boot, lauch Xojo (and only it), and check my code.

In the debugger, I saw the RecordSet contents (the data). I forgot if I was able to read the Schema names (in the debugger).

At last, that database file have three Tables (plus the automatically created sqlite_master). At this stage, I only read the Heading Table. Next, I will read the core data (data that is usually stored in Listbox Rows (not here, this is a conversion method I build from scratch).

I have a JSON export button that export this SQLIte db into a JSON (.json) file. I do not took this code as sample to export to xml (for some unknow reason, I failed to adapt that code, so I wrote it from scratch for security).

Last: once I sent the question, I get a nap that lasted… around 4 hours ! What a nap !!! but I will have troubles tonight…

Addition (the Edit):
The code after the MsgBox lines at first, extracted all entries of the RecordSet in a Loop using the FieldCount as found above. Once I was unable to understand / correct the OutofBounds error, I finally put code to get the 11 fields and I fall into the same trap. That is why I added the MsgBox RecordSet.FieldCount tests.

9

I think you’re still missing what FieldSchema actually does and the difference between that and SQLSelect.

With SQLSelect(“select * from Heading”), you will get a recordset with 11 columns.

With FieldSchema(“Heading”), you get a recordset with 11 rows.

Each Row from FieldSchema will correspond to one Column from your SQLSelect.

10

Hi Tim,

no, you wrote exactly what I want to do.(what I have in mind).

The idea is to wrote the Column Field Name and the Column Field Data in the same row like in the following fake xml:

<Column_Field_Name>Column Field Data</Column_Field_Name>

Column_Field_Name is the Column Name, (the SQLite Table field name)

Column Field Data is the Data (the Heading String).

11

I commented the lines that deals with the schema (to know what can happens) and I still get a NilObjectException in the Data RecordSet.

What is more strange is that the Data RecordSet is not Nil in the debugger, worst: it holds the stuff I want (The Table Field Name and its contents for 11 Fields of the Record).

The loop index value starts at 0 (but I add 1 like in the Docs example, and I tried 0 and 1 without difference). The RecordSet is checked against Nil at creation time.

Of course, I do not use RecordSet.MoveNext since there is only one Record in this table.

BTW: this Table does not have a Unique ID (useless for a one Record only Table).

Doing the reverse (Comment out non schema RecordSet lines related):

I find something:
It seems that a RecordSet.Schema have one Record for each Field, and 6 Fields in each RecordSet. So, one part of my coding design is wrong and the test based on the FieldCount is wrong too.

Also: the documentation is wrong: it tells that a FieldSchema RecordSet hold five Fields, but it forgot one.

FieldCount in the debugger tells 6 and the sixth field name is: cid.

OK: I have work on my plate. I will rework the whole stuff with the Target been a Listbox (I will change that later to generate the XML I want). I will wait two or three days (time to forget most of how I wrote the code and start with fresh ideas).

12

The errors comes from:

0-based and 1-based arrays in the same For … Next loop,
6 Fields in a FieldSchema,
MoveNext needed to be used for FieldSchema (and not for reading one RecordSet in the specific case).

The time is 45 minutes past 12, I am at my favorite McDonald’s and I am developing code with Xojo. Maybe this helped me ? Who knows ?
(versus doing that at the cool, noiseless home).