It is currently Tue Feb 20, 2018 8:09 pm

All times are UTC + 1 hour




Post new topic Reply to topic  [ 11 posts ] 
Author Message
 Post subject: [Done] DatabaseColumnIndex case-sensitive
PostPosted: Mon Dec 11, 2017 12:53 pm 
Offline
Enthusiast
Enthusiast
User avatar

Joined: Tue Jun 08, 2004 10:29 am
Posts: 139
Location: Zurich, Switzerland
Why is DatabaseColumnIndex() case-sensitive? This looks like a real annoying bug to me.

The SQL statement itself is case-insensitive, it doesn't matter if I write

SELECT TEST.NAME ...
or
select test.name ...
or
SELECT test.NAME ...

All of the above work, without knowing how the field is exactly written in the database.

But when extracting the data with GetDatabaseString(DatabaseColumnIndex(...)) I have to use the exact case of the Field? Why???

I should be able to use

DatabaseColumnIndex(0, "NAME")
or
DatabaseColumnIndex(0, "name")
or even
DatabaseColumnIndex(0, "naME")

without knowing how the field is declared in the database!

What do you think? Or am I doing something wrong? Are there any workarounds?

_________________
As you walk on by, Will you call my name? Or will you walk away?


Top
 Profile  
Reply with quote  
 Post subject: Re: DatabaseColumnIndex case-sensitive
PostPosted: Wed Dec 13, 2017 1:59 am 
Offline
Enthusiast
Enthusiast
User avatar

Joined: Sun Feb 12, 2006 10:06 pm
Posts: 628
untested, but perhaps this?

Code:
Macro DatabaseUpdate(db,query)
  DatabaseUpdate(db,LCase(query))
EndMacro
Macro DatabaseColumnIndex(db,ColumnName)
  DatabaseColumnIndex(db,LCase(ColumnName))
EndMacro

_________________
there is no sig, only zuul (and the following disclaimer)

WARNING: may be talking out of his hat


Top
 Profile  
Reply with quote  
 Post subject: Re: DatabaseColumnIndex case-sensitive
PostPosted: Wed Dec 13, 2017 7:40 am 
Offline
Enthusiast
Enthusiast
User avatar

Joined: Tue Jun 08, 2004 10:29 am
Posts: 139
Location: Zurich, Switzerland
Unfortunately no, this does not solve the problem :-(

You execute the query "SELECT * FROM xyz". If you have not built the database by yourself, you have no idea if the fields are upper- lower- or even mixed-case...

IMHO DatabaseColumnIndex should be case-INSENSITIVE; that would solve the problem...

maybe @Fred ?

_________________
As you walk on by, Will you call my name? Or will you walk away?


Top
 Profile  
Reply with quote  
 Post subject: Re: DatabaseColumnIndex case-sensitive
PostPosted: Wed Dec 27, 2017 1:56 pm 
Offline
Enthusiast
Enthusiast
User avatar

Joined: Tue Jun 08, 2004 10:29 am
Posts: 139
Location: Zurich, Switzerland
Could someone from the PB development team please take a look at this issue, please?

Thank you in advance,
Best regards

_________________
As you walk on by, Will you call my name? Or will you walk away?


Top
 Profile  
Reply with quote  
 Post subject: Re: DatabaseColumnIndex case-sensitive
PostPosted: Wed Dec 27, 2017 3:26 pm 
Offline
Addict
Addict
User avatar

Joined: Wed Dec 23, 2009 10:14 pm
Posts: 2576
Location: Boston, MA
Just read back the column names when you open the db. I'm not sure, but columns created with quotes become case sensitive.

_________________
The nice thing about standards is there are so many to choose from. ~ Andrew Tanenbaum


Top
 Profile  
Reply with quote  
 Post subject: Re: DatabaseColumnIndex case-sensitive
PostPosted: Wed Dec 27, 2017 6:57 pm 
Offline
Enthusiast
Enthusiast
User avatar

Joined: Tue Jun 08, 2004 10:29 am
Posts: 139
Location: Zurich, Switzerland
skywalk wrote:
Just read back the column names when you open the db. I'm not sure, but columns created with quotes become case sensitive.

@skywalk thank you for your answer, but I do not see how this could solve my problem...

To clarify the problem see the following use case:

1. my App has sort of a template engine implemented
2. the customer configures a SQL-Statement (e.g. SELECT * FROM contacts)
3. and the fields he wants to display (e.g. FIRSTNAME, LASTNAME, BIRTHDATE, ...)

The problem now is that he must - because DatabaseColumnIndex() is case SENSITIVE - write the field names in the exact way they were created in the (Sqlite-)database.

Since the SQL Query is case INSENSITIVE (e.g. SELECT FirstName, LASTNAME, birthDATE FROM CONtacts) it is not very clear why the retrieval of the field values should be case SENSITIVE... And the customer normally does not now :-(

Hope this helps

Best regards

_________________
As you walk on by, Will you call my name? Or will you walk away?


Top
 Profile  
Reply with quote  
 Post subject: Re: DatabaseColumnIndex case-sensitive
PostPosted: Wed Dec 27, 2017 7:14 pm 
Offline
Addict
Addict
User avatar

Joined: Wed Dec 23, 2009 10:14 pm
Posts: 2576
Location: Boston, MA
Ok, I can confirm the behavior.
DatabaseColumnIndex() returns the case sensitive column position in a result set.
Not the underlying table.
This is most likely a wrapper for Column names in a result set. You could use that function directly and see if it operates differently? Or create a list of all possible column names from the db and maintain their case.

_________________
The nice thing about standards is there are so many to choose from. ~ Andrew Tanenbaum


Top
 Profile  
Reply with quote  
 Post subject: Re: DatabaseColumnIndex case-sensitive
PostPosted: Thu Dec 28, 2017 9:45 am 
Offline
Administrator
Administrator

Joined: Fri May 17, 2002 4:39 pm
Posts: 13212
Location: France
sounds like a bug if it's not case sensitive in the SQL statement. Moved to bug forums.


Top
 Profile  
Reply with quote  
 Post subject: Re: DatabaseColumnIndex case-sensitive
PostPosted: Wed Jan 10, 2018 11:05 am 
Offline
Enthusiast
Enthusiast
User avatar

Joined: Tue Jun 08, 2004 10:29 am
Posts: 139
Location: Zurich, Switzerland
Any news on this issue?

Again, it would be nice to have the following:
Code:
DatabaseColumnIndex("LASTNAME")
is equal to
DatabaseColumnIndex("LastName")
is equal to
DatabaseColumnIndex("lastname")


Thank you!

_________________
As you walk on by, Will you call my name? Or will you walk away?


Top
 Profile  
Reply with quote  
 Post subject: Re: DatabaseColumnIndex case-sensitive
PostPosted: Mon Jan 15, 2018 9:52 pm 
Offline
Administrator
Administrator

Joined: Fri May 17, 2002 4:39 pm
Posts: 13212
Location: France
Fixed.


Top
 Profile  
Reply with quote  
 Post subject: Re: [Done] DatabaseColumnIndex case-sensitive
PostPosted: Tue Jan 16, 2018 8:19 am 
Offline
Enthusiast
Enthusiast
User avatar

Joined: Tue Jun 08, 2004 10:29 am
Posts: 139
Location: Zurich, Switzerland
@Fred Thank you!

_________________
As you walk on by, Will you call my name? Or will you walk away?


Top
 Profile  
Reply with quote  
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 11 posts ] 

All times are UTC + 1 hour


Who is online

Users browsing this forum: No registered users and 2 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
Jump to:  

 


Powered by phpBB © 2008 phpBB Group
subSilver+ theme by Canver Software, sponsor Sanal Modifiye