On Thursday 10 July 2008 15:17:50 D. Richard Hipp wrote:
> On Jul 10, 2008, at 7:57 AM, Evgeni Alesinskyy wrote:
> > Hi all,
> >
> > I develop application in C# that has to run with MySQL and SQLite.
> > At some point I need to join 2 tables and bind them resp. dataset
> > containing the union to ListBox.
> >
> > To do this I use aliasing for table names. My SQL-statement looks
> > like this:
> > SELECT distinct(m.name), wp.MaterialSN from WorkDefaultsMaterial wp,
> > Material m WHERE m.ShortName = wp.MaterialSN AND wp.WorkSN='bla-bla'
> > order by m.name
> >
> > If I use MySQL, then the column names are just names of columns in
> > each table with out alias, e.g. MaterialSN. If I use SQLite, then
> > the column names starts with alias, e.g. wp.MaterialSN.
> >
> > So that if I bind the dataset to ListBox, in case where I use
> > SQLite, I get an error :
> >
> > "Cannot bind to the new display member.
> > Parameter name: newDisplayMember"
> >
> > The reason for this error is following:
> >
> > the column name ist wp.MaterialSN. In C# , if one binds a field/
> > column the dot means that the part before it is a table name and
> > part after column name. So c# looks for the table wp in dataset.
> > This is however not present :-).
> >
> > If I use MySQL, my application works fine, as the column names do
> > not contain alias.
> >
> > I can surely rewrite my SQL-statement to SELECT distinct(m.name) as
> > name, wp.MaterialSN as MaterialSN ... to obtain column names in
> > needed format. However in my opinion it is not a good solution to do
> > it.
> >
> > Is there a possibility to configure SQLite so that aliases are
> > omitted and only column names are used?
> >
> > The strange thing is, that my application worked already with
> > SQLite. And now as I had to make some changes it become this strange
> > behavior.
> >
> > Any idea why?
>
> SQLite does not (currently) make any promises about column names on
> queries that omit the AS clause. If you use an AS clause, then the
> column name is guaranteed to be the label to the right of AS. If you
> omit the AS clause, then the column names might change from one
> release of SQLite to the next.
>
> At some point in the future, it would be nice to document exactly how
> column names are computed and freeze the implementation accordingly.
> But we are not there yet.
>
> Please note that there is a subtle change in column naming that will
> appear in version 3.6.0 - a change which I believe will make SQLite
> behave more like MySQL and omit the table names in cases where they
> are not needed. So version 3.6.0 might work for your situation above
> without the use of AS. But for portability, it is best to use AS.
>
> D. Richard Hipp
> [EMAIL PROTECTED]
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
I would like to point out that when selecting without distinct, sqlite returns
the real tablename in a column name formatted as tablename.columnname, even
if the tables are aliased in the select statement.
With select distinct, the tablenames that are returned are the alias if
specified in the statement.
At least this is how the sqlite3_get_table function returns.
Kris
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
