databaseLayer : Full Field Names in sqlite View  [SOLVED]

Talk here about issues with one of the components hosted at wxCode, or suggest features for it.
gururamnath
Moderator
Moderator
Posts: 466
Joined: Sat Sep 18, 2004 2:49 am
Location: California, USA

databaseLayer : Full Field Names in sqlite View

Postby gururamnath » Sat Mar 11, 2006 12:40 am

I'm having trouble using the alias name of a field in a view. if we have a View with the following Select

Code: Select all

SELECT
   Members.ID AS 'MemberID'
FROM
   Members   


The m_FieldLookupMap variable is populated with a value "Members.ID" and not wit the alias "MemberID" . It seems the function sqlite3_column_name is returing this value.

When I tried the same db with CppSqlite3, It was returning the alias Field Name (it also uses the same function call).

Can you please let me know how to get the alias instead of the full field Name ?

Another Request : Can you please give a small template of how the Field Descriptor class should look it ? I'll try to add the feature to the databaselayer. Since dblayer doesnt support Field description I was forced to use CppSqlite3 along with this library.


Thanks,
Guru Kathiresan

jb_coder
Super wx Problem Solver
Super wx Problem Solver
Posts: 267
Joined: Mon Oct 18, 2004 10:55 am

Postby jb_coder » Sun Mar 12, 2006 7:50 am

I'm not able to reproduce the issue on my system. Here is what I"m trying n a unit test:

Code: Select all

m_pDatabaseLayer->RunQuery(_("CREATE TABLE table1 (column1 VARCHAR(50), column2 VARCHAR(50));"));
m_pDatabaseLayer->RunQuery(_("INSERT INTO table1 (column1, column2) VALUES ('ONE', 'TWO');"));
DatabaseResultSet* pResultSet = m_pDatabaseLayer->RunQueryWithResults(_("SELECT table1.column1 AS aliasColumn FROM table1;"));
wxString strValue = pResultSet->GetResultString(_("aliasColumn"));


I've also tried this using a View

Code: Select all

m_pDatabaseLayer->RunQuery(_("CREATE TABLE table1 (column1 VARCHAR(50), column2 VARCHAR(50));"));
m_pDatabaseLayer->RunQuery(_("INSERT INTO table1 (column1, column2) VALUES ('ONE', 'TWO');"));
m_pDatabaseLayer->RunQuery(_("CREATE VIEW Table1View AS SELECT table1.column1 AS col1 FROM table1;"));
DatabaseResultSet* pResultSet = DatabaseResultSet* pResultSet = m_pDatabaseLayer->RunQueryWithResults(_("SELECT col1 FROM Table1View;"));
wxString strValue = pResultSet->GetResultString(_("aliasColumn"));


But in both cases the code seems to work with SQLite (3.3.3) against the latest databaselayer code in CVS.

I'm not sure that I understand what you mean by "Field Descriptor". I just checked in some code to retrieve something similar to the JDBC Interface "ResultSetMetaData" which should allow you to get the column count in a result set as well as the column names, types, and size (although size varies quite a bit and doesn't seem to be reliable).

gururamnath
Moderator
Moderator
Posts: 466
Joined: Sat Sep 18, 2004 2:49 am
Location: California, USA

Postby gururamnath » Sun Mar 12, 2006 10:37 am

Hi,
I tried specifying all the fields in the view and had this problem. Just now I tried "select * from view;" and it seems to work fine. I'm using sqlite3.3.4. I'll do some more investigation and let you know.

The MetaData class was the one I was asking as Field Descriptor. I'll have a look at them and if it needs any fix, I'll try to work on it.

Thank you so much for your support.

regards,
-Guru Kathiresan

jb_coder
Super wx Problem Solver
Super wx Problem Solver
Posts: 267
Joined: Mon Oct 18, 2004 10:55 am

Postby jb_coder » Sun Mar 12, 2006 4:42 pm

Okay, I was able to recreate the problem by upgrading from SQLite 3.3.3 to SQLite 3.3.4 .

jb_coder
Super wx Problem Solver
Super wx Problem Solver
Posts: 267
Joined: Mon Oct 18, 2004 10:55 am

  [SOLVED]

Postby jb_coder » Sun Mar 12, 2006 4:52 pm

I think that it may be a SQLite 3.3.4 issue. Here's the CVSTrac issue writeup: http://www.sqlite.org/cvstrac/tktview?tn=1709

I'll pull from SQLite CVS and see if the issue still exists.

gururamnath
Moderator
Moderator
Posts: 466
Joined: Sat Sep 18, 2004 2:49 am
Location: California, USA

Postby gururamnath » Sun Mar 12, 2006 11:35 pm

Thanks for investigating the problem.

-regards,
Guru Kathiresan


Return to “wxCode”

Who is online

Users browsing this forum: No registered users and 1 guest