What is going on with this data row

Databases
1

I copied code from another section of the project that is working. I have a database opened and am getting a RowSet back. But I need to look over this RowSet and get various columns of data.

So instead of using:
S=rs.Column(“CompanyName”).StringValue
I need to use something like this:
S=“CompanyName”
S=rs.Column(S).StringValue
But this throws an InvalidArgumentException, “no column named CompanyName”
Yet I see the vairables in the debugger, a rowset with the “CompanyName” column.

What am I missing.

Dim I As Integer
Dim S As String
Dim T As Text
Dim Relates As Boolean
Dim TableName As String
Dim ColumnToMatch As String
Dim MatchName As String
Dim ColumnToReturn As String

Dim ErrorMess As String
Dim rs As RowSet

Dim Lb As WebListBox
Lb=ListBoxCompanies

// NO Error checking//
rs=Session.GetAllFromDBRowSet("Company")

If rs Is Nil Then Return

LB.DeleteAllRows

If rs <> Nil Then
  
  ErrorMess=""
  For Each row As DataBaseRow In rs
    LB.AddRow("")
    
    Try
          S="CompanyName" 
        // S=rs.Column(S).StringValue
        S=rs.Column(S).Value  //////  <--------------------- this causes an InvalidArgumentException.
        
        LB.Cell(LB.LastIndex, I) = S
      Next
      
    Catch e As NilObjectException
      MessageBox("Nil Object")
    End Try
  Next
  rs.Close
End
2

some database engines, like postgres, are case sensitive. “Company” is not “company”.
so it’s better to have all table/fields names lowercases.

3

The code you posted says “Company”. Does that matter?

4

Good point, I’ll rename the column names and see. But this is an SqLite database.
Thanks

5

That’s true only if you quote the column names. By default Postgres is NOT case sensitive, so “Select * from Company” is the same as “Select * from company”.

Eric, try S=""“ComapnyName”"". Read this: https://blog.xojo.com/2016/09/28/about-postgresql-case-sensitivity/

6

This is an SQLite database. I tried Chr(34)+S+Chr(34) but I think you may be right about the " " " ComapnyName " " " (without the spaces).

7

Using more quote marks also failed.
I suspect I need to write a routine to list the columns that Xojo is seeing in this routine to learn why there is a mis-match.

The SqlIte database is from the client, so … who knows what corruption there may be. However, DB Browser for SQLite lets me see the data. Not sure why this is happening. May be time to restart the computer …

8

I’d just put a breakpoint in the row where the exception occurs and inspect the rowset closely and make sure the CompanyName column is really there.

1 Like
9

I found the by deleting the “CompanyName” and retyping the line worked.
There must have been some non-printing char in the copy/paste buffer from when I copied the field name from the database.

Working!

1 Like
10

While the result you describe is right, technically PostgreSQL is case-sensitive. It’s just that it internally converts all identifiers to lowercase unless quoted.

See:

11

When copying text out of PgAdmin (if that is what you did) often adds an invisible LineFeed character to the start of the text. If you arrow across the text you can see the cursor not move once on the first character.

1 Like
12

I’d be inclined to argue that the fact that PG turns the names to lowercase internally makes it case-insensitive for the user (also see https://stackoverflow.com/questions/21796446/postgres-case-sensitivity#:~:text=In%20PostgreSQL%20unquoted%20names%20are,quoted%20names%20are%20case-sensitive.&text=Thus%2C%20when%20creating%20entities%20(tables,or%20quoted-but-lowercased.).
Btw, I posted the exact same link in my post above. Anyway, we have a gremlin issue here, not for the first time…

13

it shouldnt as it is a sqlite database… but this trick is nice to know.

1 Like
14

image

2 Likes
15

I think we just agreed on all points. :slight_smile:

1 Like
16

I would have watched with interest a fight between Kem and Maximilian …:upside_down_face:

17

Nobody really wants to see me cry.