OLE DB woes

Hi Informixers, thanks to a helpful hint from this group, I can now connect to my IDS 7.30 server from VB6.0 using the Informix OLE DB Provider. Now I'm running across problems with the following rather simple SQL statement: SELECT attribut, wert, von, bis FROM personal p, pers_zusatz z WHERE p.persnr = z.persnr AND p.persnr = 235 AND (z.bis IS NULL OR z.bis >= '05.07.1999') ORDER BY attribut The "setnet32" settings seem to be ignored, since I've set DBDATE to "DMY4.", but I'm getting error -1218: String to date conversion error. When I look at the statement on the server with "onstat -g sql <sessionid>, the statement looks exactly as above. Very well, I changed the statement to format the date in the default American style: AND (z.bis IS NULL OR z.bis >= '1999-07-05') I _only_ changed this line in the SQL statement, the rest is exactly as above. Now I get error -324: Ambiguous column (rowid). Since column "rowid" appears nowhere in the statement, I again looked at the statement on the server, and there the column "rowid" indeed appears as the first column to be selected! Where does this come from? There are other queries in my program before the one in question which work just fine, but don't contain dates. In these queries, "rowid" is not included. I found nothing in the OLE DB 2.0 manual or the release notes. Can anybody help me? Regards, Richard -- +--------------------------+------------------------------------------+ | Dr. Richard Spitz | INTERNET: spitz@ana.med.uni-muenchen.de | | EDV-Gruppe Anaesthesie | Tel : +49-89-7095-6110 | | Klinikum Grosshadern | FAX : +49-89-7095-6420 <-- NEW!!! | | 81366 Munich, Germany | GSM : +49-172-8933578 | +--------------------------+------------------------------------------+

Date: OLE DB spec says DBDATE - "yyyy-mm-dd". Great that it worked for you. Rowid: OLE DB provider needs rowids if you asked for bookmarks or updatable rowset. If you have a join, it is not smart enough to figure out what table to get rowid from. The small consolation is that you would not be able to use SELECT ... FOR UPDATE either. If you need scrollable cursor but not absolute positioning or updatability, use adOpenDynamic - it will not require rowids. Richard Spitz <Richard.Spitz@ana.med.uni-muenchen.de> wrote in message news:3780BF45.B0D6F74F@ana.med.uni-muenchen.de... > Hi Informixers, > > thanks to a helpful hint from this group, I can now connect to my IDS 7.30 > server from VB6.0 using the Informix OLE DB Provider. > > Now I'm running across problems with the following rather simple SQL > statement: > > SELECT attribut, wert, von, bis > FROM personal p, pers_zusatz z > WHERE p.persnr = z.persnr AND p.persnr = 235 > AND (z.bis IS NULL OR z.bis >= '05.07.1999') > ORDER BY attribut> > The "setnet32" settings seem to be ignored, since I've set DBDATE to "DMY4.", > but I'm getting error -1218: String to date conversion error. > > When I look at the statement on the server with "onstat -g sql <sessionid>, > the statement looks exactly as above. > > Very well, I changed the statement to format the date in the default > American style: > > AND (z.bis IS NULL OR z.bis >= '1999-07-05') > > I _only_ changed this line in the SQL statement, the rest is exactly as > above. Now I get error -324: Ambiguous column (rowid). > > Since column "rowid" appears nowhere in the statement, I again looked at > the statement on the server, and there the column "rowid" indeed appears > as the first column to be selected! > > Where does this come from? There are other queries in my program before > the one in question which work just fine, but don't contain dates. In these > queries, "rowid" is not included. > > I found nothing in the OLE DB 2.0 manual or the release notes. Can anybody > help me? > > Regards, Richard > -- > +--------------------------+------------------------------------------+ > | Dr. Richard Spitz | INTERNET: spitz@ana.med.uni-muenchen.de | > | EDV-Gruppe Anaesthesie | Tel : +49-89-7095-6110 | > | Klinikum Grosshadern | FAX : +49-89-7095-6420 <-- NEW!!! | > | 81366 Munich, Germany | GSM : +49-172-8933578 | > +--------------------------+------------------------------------------+