You are viewing a plain text version of this content. The canonical link for it is here.
Posted to ojb-user@db.apache.org by Bartłomiej Knabel <ba...@gmail.com> on 2005/05/14 14:31:02 UTC
Re[2]: sql problems wth JTDS and SQL Server
Hi,
AW> I'm not very familiar with the OJB query generation, but I think you
AW> should post the generated query (using p6spy or any other tool).
"SELECT A0.czz_id,A0.czz_glAtid,A0.czz_dokId FROM
CzescZlozona A0,zp_czznd INNER JOIN ElementProjektowy A1 ON A0.czz_id=A1.ep_id WHERE (zp_czznd.czz_zp_zpIdnd IN (1)) AND A0.czz_id = zp_czznd.czz_zp_czpIdnd;"
Server: Msg 107, Level 16, State 2, Line 1
The column prefix 'A0' does not match with a table name or alias name used in the query.
AW> If you disable the collection proxy in the class causing the exception,
AW> will the exception arise on main object materialization?
AW> By the way why do you use useAutoCommit="2"? In non-managed environments
AW> it's recommended to use "1".
setting proxy to "false" or useAutoCommit to "1" doesn't solve this
problem :(
--
Bartłomiej Knabel
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: sql problems wth JTDS and SQL Server
Posted by Jakob Braeuchi <jb...@gmx.ch>.
hi bartolomiej,
i do not have a ms-sqlserver here. could you please try the sql-select
on a completely up-to-date ms-sqlserver ?
jakob
Bartłomiej Knabel schrieb:
> Hi Jakob,
>
>
> JB> i mean did you enter the select in a gui-application that calls
> JB> ms-sqlserver ?
>
> it was SQL Querry analyzer
>
> JB> i would like to know which column alias caused the
> JB> problem because the query looks ok to me. (except 'zp_czznd', is this an
> JB> indirection table ?)
>
> zp_czznd is an indirection table.
>
> this is full SQL script, whitch generates sql database :
>
> drop table ZestawZlozony;
> drop table CzescZlozona;
> drop table CzescAtomowa;
> drop table ElementProjektowy;
> drop table Dokument;
> drop table Modul;
> drop table Polaczenie;
> drop table Manual;
> drop table ZestawPodstawowy;
> drop table zp_czznd;
> drop table zp_czzd;
> drop table Parametry;
>
> create table Parametry (
> par_id integer not null identity,
> par_IloscCzANaCzZ integer,
> par_IloscPolaczenNaAtom integer,
> par_DlugoscDokumentu integer,
> par_DlugoscManuala integer,
> par_IloscCzZlozNaModul integer,
> par_IloscZestWZestZl integer,
> par_IloscPoziomow integer,
> par_IloscCzZWZestPdst integer,
> par_IloscModulow integer,
> primary key (par_id)
> );
>
> create table Dokument (
> dok_id integer not null identity,
> dok_czzId integer,
> dok_tekst text null,
> dok_tytul varchar(40),
> primary key (dok_id)
> );
>
> create table Manual (
> man_id integer not null identity,
> man_tytul varchar(40),
> man_tekst text null,
> man_dlTekstu integer not null,
> man_modId integer,
> primary key (man_id)
> );
> create table Polaczenie (
> pol_id integer not null identity,
> pol_dlugosc integer not null,
> pol_typ varchar(10),
> polZAtomowej integer,
> polDoAtomowej integer,
> primary key (pol_id)
> );
> create table ElementProjektowy (
> ep_id integer not null identity,
> ep_typ varchar(10),
> ep_data datetime not null,
> primary key (ep_id)
> );
>
> create table CzescAtomowa (
> cza_id integer not null ,
> cza_x integer not null,
> cza_y integer not null,
> cza_dokId integer not null,
> cza_czId integer,
> primary key (cza_id)
> );
> create table CzescZlozona (
> czz_id integer not null ,
> czz_glAtid integer not null,
> czz_dokId integer not null,
> primary key (czz_id)
> );
> create table Modul (
> mod_id integer not null ,
> mod_manId integer,
> mod_zzId integer,
> primary key (mod_id)
> );
>
> create table ZestawPodstawowy (
> zp_id integer not null,
> zp_zz_id integer,
> primary key (zp_id)
> );
>
> create table ZestawZlozony (
> zz_id integer not null,
> zz_modId integer,
> zz_zz_id integer,
> primary key (zz_id)
> );
>
> create table zp_czzd (
> czz_zp_zpIdd integer not null,
> czz_zp_czpIdd integer not null
> );
>
> create table zp_czznd (
> czz_zp_zpIdnd integer not null,
> czz_zp_czpIdnd integer not null
> );
>
> create index IndexDokumentTytul on Dokument (dok_tytul);
> create index IndexCzaDokId on CzescAtomowa (cza_dokId);
>
>
> now You can generate my database and chceck it.
>
> Maybye I dont have some SP? I use just SP3 for SQL Server 200, but I
> didin't install SP1 and SP2...
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re[2]: sql problems wth JTDS and SQL Server
Posted by Bartłomiej Knabel <ba...@gmail.com>.
Hi Jakob,
JB> i mean did you enter the select in a gui-application that calls
JB> ms-sqlserver ?
it was SQL Querry analyzer
JB> i would like to know which column alias caused the
JB> problem because the query looks ok to me. (except 'zp_czznd', is this an
JB> indirection table ?)
zp_czznd is an indirection table.
this is full SQL script, whitch generates sql database :
drop table ZestawZlozony;
drop table CzescZlozona;
drop table CzescAtomowa;
drop table ElementProjektowy;
drop table Dokument;
drop table Modul;
drop table Polaczenie;
drop table Manual;
drop table ZestawPodstawowy;
drop table zp_czznd;
drop table zp_czzd;
drop table Parametry;
create table Parametry (
par_id integer not null identity,
par_IloscCzANaCzZ integer,
par_IloscPolaczenNaAtom integer,
par_DlugoscDokumentu integer,
par_DlugoscManuala integer,
par_IloscCzZlozNaModul integer,
par_IloscZestWZestZl integer,
par_IloscPoziomow integer,
par_IloscCzZWZestPdst integer,
par_IloscModulow integer,
primary key (par_id)
);
create table Dokument (
dok_id integer not null identity,
dok_czzId integer,
dok_tekst text null,
dok_tytul varchar(40),
primary key (dok_id)
);
create table Manual (
man_id integer not null identity,
man_tytul varchar(40),
man_tekst text null,
man_dlTekstu integer not null,
man_modId integer,
primary key (man_id)
);
create table Polaczenie (
pol_id integer not null identity,
pol_dlugosc integer not null,
pol_typ varchar(10),
polZAtomowej integer,
polDoAtomowej integer,
primary key (pol_id)
);
create table ElementProjektowy (
ep_id integer not null identity,
ep_typ varchar(10),
ep_data datetime not null,
primary key (ep_id)
);
create table CzescAtomowa (
cza_id integer not null ,
cza_x integer not null,
cza_y integer not null,
cza_dokId integer not null,
cza_czId integer,
primary key (cza_id)
);
create table CzescZlozona (
czz_id integer not null ,
czz_glAtid integer not null,
czz_dokId integer not null,
primary key (czz_id)
);
create table Modul (
mod_id integer not null ,
mod_manId integer,
mod_zzId integer,
primary key (mod_id)
);
create table ZestawPodstawowy (
zp_id integer not null,
zp_zz_id integer,
primary key (zp_id)
);
create table ZestawZlozony (
zz_id integer not null,
zz_modId integer,
zz_zz_id integer,
primary key (zz_id)
);
create table zp_czzd (
czz_zp_zpIdd integer not null,
czz_zp_czpIdd integer not null
);
create table zp_czznd (
czz_zp_zpIdnd integer not null,
czz_zp_czpIdnd integer not null
);
create index IndexDokumentTytul on Dokument (dok_tytul);
create index IndexCzaDokId on CzescAtomowa (cza_dokId);
now You can generate my database and chceck it.
Maybye I dont have some SP? I use just SP3 for SQL Server 200, but I
didin't install SP1 and SP2...
--
Bartłomiej Knabel
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: sql problems wth JTDS and SQL Server
Posted by Jakob Braeuchi <jb...@gmx.ch>.
hi bartlomiej,
i mean did you enter the select in a gui-application that calls
ms-sqlserver ? i would like to know which column alias caused the
problem because the query looks ok to me. (except 'zp_czznd', is this an
indirection table ?)
jakob
Bartłomiej Knabel schrieb:
> Hi Jakob,
>
> JB> please try to execute the sql in mssqlserver directly, we'll probably
> JB> get a more detailed error message.
>
> this is direct from sql server :
>
>
>>>Server: Msg 107, Level 16, State 2, Line 1
>>>The column prefix 'A0' does not match with a table name or alias name used in the query.
>
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re[2]: sql problems wth JTDS and SQL Server
Posted by Bartłomiej Knabel <ba...@gmail.com>.
Hi Jakob,
JB> please try to execute the sql in mssqlserver directly, we'll probably
JB> get a more detailed error message.
this is direct from sql server :
>> Server: Msg 107, Level 16, State 2, Line 1
>> The column prefix 'A0' does not match with a table name or alias name used in the query.
--
Regards,
Bartłomiej Knabel
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: sql problems wth JTDS and SQL Server
Posted by Jakob Braeuchi <jb...@gmx.ch>.
hi bartlomiej,
please try to execute the sql in mssqlserver directly, we'll probably
get a more detailed error message.
jakob
Bartłomiej Knabel schrieb:
> Hi,
>
> AW> I'm not very familiar with the OJB query generation, but I think you
> AW> should post the generated query (using p6spy or any other tool).
>
> "SELECT A0.czz_id,A0.czz_glAtid,A0.czz_dokId FROM
> CzescZlozona A0,zp_czznd INNER JOIN ElementProjektowy A1 ON A0.czz_id=A1.ep_id WHERE (zp_czznd.czz_zp_zpIdnd IN (1)) AND A0.czz_id = zp_czznd.czz_zp_czpIdnd;"
>
> Server: Msg 107, Level 16, State 2, Line 1
> The column prefix 'A0' does not match with a table name or alias name used in the query.
>
> AW> If you disable the collection proxy in the class causing the exception,
> AW> will the exception arise on main object materialization?
> AW> By the way why do you use useAutoCommit="2"? In non-managed environments
> AW> it's recommended to use "1".
>
> setting proxy to "false" or useAutoCommit to "1" doesn't solve this
> problem :(
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re[2]: sql problems wth JTDS and SQL Server
Posted by Bartłomiej Knabel <ba...@gmail.com>.
Witaj Armin,
W Twoim liście datowanym 14 maja 2005 (14:54:24) można przeczytać:
AW> Bartłomiej Knabel wrote:
>> Hi,
>>
>> AW> I'm not very familiar with the OJB query generation, but I think you
>> AW> should post the generated query (using p6spy or any other tool).
>>
>> "SELECT A0.czz_id,A0.czz_glAtid,A0.czz_dokId FROM
>> CzescZlozona A0,zp_czznd INNER JOIN ElementProjektowy A1 ON
>> A0.czz_id=A1.ep_id WHERE (zp_czznd.czz_zp_zpIdnd IN (1)) AND
>> A0.czz_id = zp_czznd.czz_zp_czpIdnd;"
>>
>> Server: Msg 107, Level 16, State 2, Line 1
>> The column prefix 'A0' does not match with a table name or alias name used in the query.
>>
AW> If the query is auto-generated by OJB I think it's a dialect bug. In
AW> this case please make a detailed jira-bug report with stack trace,
AW> generated query and if possible with a fixed query statement (tested
AW> against your query browser).
http://issues.apache.org/jira/browse/OJB-42
for me - it looks like SQL Server Issue :(
--
Bartłomiej Knabel
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: sql problems wth JTDS and SQL Server
Posted by Armin Waibel <ar...@apache.org>.
Bartłomiej Knabel wrote:
> Hi,
>
> AW> I'm not very familiar with the OJB query generation, but I think you
> AW> should post the generated query (using p6spy or any other tool).
>
> "SELECT A0.czz_id,A0.czz_glAtid,A0.czz_dokId FROM
> CzescZlozona A0,zp_czznd INNER JOIN ElementProjektowy A1 ON A0.czz_id=A1.ep_id WHERE (zp_czznd.czz_zp_zpIdnd IN (1)) AND A0.czz_id = zp_czznd.czz_zp_czpIdnd;"
>
> Server: Msg 107, Level 16, State 2, Line 1
> The column prefix 'A0' does not match with a table name or alias name used in the query.
>
If the query is auto-generated by OJB I think it's a dialect bug. In
this case please make a detailed jira-bug report with stack trace,
generated query and if possible with a fixed query statement (tested
against your query browser).
> AW> If you disable the collection proxy in the class causing the exception,
> AW> will the exception arise on main object materialization?
> AW> By the way why do you use useAutoCommit="2"? In non-managed environments
> AW> it's recommended to use "1".
>
> setting proxy to "false" or useAutoCommit to "1" doesn't solve this
> problem :(
And you got the same stack trace? If yes, we can exclude problems with
prefetching/proxy stuff.
regards,
Armin
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org