Hi,
There is a DataSet1.xsd dataset in a web service project developed in
Visual Studio .NET environment. A fill method in the table adapter of
this dataset has below connection string:
<add
name=3D"ConnectionString"
connectionString=3D"Data Source=3Dxxx.xxx.xx.xx;Initial
Catalog=3Dxxx;Us=
er ID=3Dxxx;Password=3Dxxx"
providerName=3D"System.Data.SqlClient" />
Then, I define a system DSN using ODBC data source manager. In the
dialog, I type related PostgreSQL database connection properties (host,
dbname, etc.).
After that, I introduce a new dataset (DataSet2.xsd) to the same
project. While adding a table adapter to this dataset, I select freshly
introduced DSN using "New Connection" option.
Now, I copy the SQL query string -- displayed in Query Builder -- used
in DataSet1 to DataSet2. The query string working without a problem in
the DataSet1, gets transformed into a new scheme when we try to execute
it in DataSet2:
DATASET1 QUERY
--------------
select yyy.muid
from xxx inner join
yyy inner join
mmm on yyy.muid =3D mmm.muid inner join
eee on yyy.eventlogid =3D eee.eventlogid and=20
mmm.muid =3D eee.muid inner join
ddd on mmm.muid =3D ddd.muid and=20
eee.eventlogid =3D ddd.eventlogid inner join
ccc on eee.eventcode =3D ccc.eventcode on=20
xxx.groupid =3D mmm.groupid inner join
fff on xxx.fleetid =3D fff.fleetid inner join
kkk on fff.kkkid =3D kkk.kkkid inner join
ggg on=20
kkk.kkkid =3D ggg.kkkid=20
left outer join
vvv inner join
vvvlog on vvv.driverid =3D vvvlog.driverid on=20
mmm.muid =3D vvvlog.muid and=20
eee.eventlogid =3D vvvlog.eventlogid
where (eee.=E2=80=9Dtime=E2=80=9D >?) and=20
(mmm.networkid =3D ?) and (eee.=E2=80=9Dtime=E2=
=80=9D < ?) and
(ggg.username =3D ?)
DATASET2 QUERY (AFTER TRANSFORMATION)
--------------
SELECT yyy.muid
FROM emove.yyy, emove.mmm, emove.eee, emove.ddd, emove.ccc,
emove=
..fff,=20
emove.kkk, emove.ggg, emove.vvvlog,=20
{ oj emove.xxx LEFT OUTER JOIN
emove.vvv ON mmm.muid =3D vvvlog.muid AND=20
eee.eventlogid =3D vvvlog.eventlogid }
WHERE xxx.groupid =3D mmm.groupid AND=20
yyy.muid =3D mmm.muid AND=20
yyy.eventlogid =3D eee.eventlogid AND=20
mmm.muid =3D eee.muid AND=20
mmm.muid =3D ddd.muid AND=20
eee.eventlogid =3D ddd.eventlogid AND=20
eee.eventcode =3D ccc.eventcode AND=20
xxx.fleetid =3D fff.fleetid AND=20
fff.kkkid =3D kkk.kkkid AND=20
kkk.kkkid =3D ggg.kkkid AND=20
vvv.driverid =3D vvvlog.driverid AND=20
(eee."time" > ?) AND=20
(mmm.networkid =3D ?) AND=20
(eee."time" < ?) AND=20
(ggg.username =3D ?)
When we execute the transformed query, Query Builder complains that:
Error Source: PSQLODBC.DLL
Error Message: ERROR [42P01] ERROR: invalid reference to FROM-clause
entr=
y for table =E2=80=9Cmmm=E2=80=9D
If we'd directly issue the query using New Table Adapter Wizard (without
touching Query Builder interface), in the last step the wizard complains
that
No Mapping exists from DbType Object to a known OdbcType.
Moreover, if we'd comment out the WHERE clause used in the above query
(typed in New Table Adapter Wizard), query gets executed without a
problem.
We'll be really appreciated for any kind of help and suggestion. Even
alternative methods are welcome.
Regards.
P.S. PostgreSQL ODBC driver is installed via PostgreSQL 8.3.1 windows
binary package.
--=20
Sent via pgsql-odbc mailing list (pgsql-odbc@[EMAIL PROTECTED]
)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc
|
