databaseLayer : Full Field Names in sqlite View Topic is solved

[gururamnath]

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]

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]

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]

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

[jb_coder]

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]

Thanks for investigating the problem.

-regards,
Guru Kathiresan