Issues faced during migration from OR4.1SP3 to OR2006

[Ramke]

Hi All,

We are currently on OR4.1SP3 and soon will be moving to OR2006 with Ingres 9.2. I would appreciate feed back from those who have already moved or in the process of move can share their view on the following:

a) Any easy steps followed during the move

b) Any issues (export/import apps or UI appearance, 3pty dlls usage in the new version etc) during the move

c) Additionally, We are using MS Word, MS Excel, and MS Email (version 2003) with OR4.1. IF there is any potential problem already faced by those who moved or in the process of moving. Generally we had too much problem in this area with previous moves.

Any help on the above is much appreciated.
Regards
Ramke

[Bodo]

Have a look at the Migration Guide: http://downloads.ingres.com/online/c...enROAD2006.pdf

I'm not aware of any new problems with 3GL procedures or External Class Libraries, but a few enhancements (e.g. error checking).
Some of them might cause a change in behavior, e.g. for processing external events (listed in the migration guide).

[Ramke]

Hi ,
We just changed the database version of ingres to 9.2. Now, We have problem with talking to MS Access 2003 from OR4.1 itself. It seems the problem with Date parameter sent from OR4.1 to MS Access 2003. Can anybody indicatoe which version Ingres ODBC driver (windows version) resolved the date format issues. We earlier had Money field problem between Ingres 2.6 and OR4.1. Now it is date problem. ....?

regards
ramke

[Bodo]

Have you used the ODBC driver which comes with Ingres9.2?
It has an "advanced" ODBC DSN configuration option called "Coerce to
Ingres date syntax" which coerces date/time syntax to ingresdate
syntax.
Otherwise, the ODBC driver uses ANSI syntax if the connection partner supports ANSI syntax.

You also said that you sent Date parameter from "OR4.1 itself" to MS Access 2003.
As MS Access 2003 is not having an Ingres Date datatype I'm wondering how you transfer them?

[Ramke]

Hi Bodo,

Just Now (17/03?2009 4.00PM singapore time, We installed the new odbc driver and then the OR 2006 patch P13623. We will test the date problem and put the info here in this forum for the benefit of others.

As far passing date from Ingres to MS Access concerned, we transfer them as a string and later convert the same in ms acces to date using cdate. But when you link an ingres table, MS access is smart enough, to identify the date filed correctly.

regards
Ramke

[Ramke]

Hi All,

Passing date from OR2006 to MS Access is still a problem. can anybody share their views on this or for anyone it works correctly? We have the latest odbc driver (3.50.9x) and the workbench patches (P13623)

[Bodo]

As you said, you are passing the dates as string from OR2006 (so I assume you do a varchar() conversion) I don't see what this has to do with date passing at all?

[Ramke]

Hi Bodo ,
The date is passed as a string from OR to MSA later checked against the linked tables of ingres within MS Access.

Say a table called Tinvoice is in the ingres db. It is linked to MS Access db using the odbc datasource. (not imported into MSA,but merely a link only here). The OR application has a frame on which the user input the date and other params and the date other params is transmitted to MS Access (using Access class library -external application within OR).

In MS access, a query is used to search the linked table for the given date and the report is printed.

We have load of reports like this. In the old version, we compare the passed parameter, by converting it using "cdate(<para>) = Tinvoice.invdate", now this portion is not working inspite of invdate of ingres linked table is the datetime datatype.

One way of resolving or workaround would be use the cdate function on both the side like ""cdate(<para>) = cdate(Tinvoice.invdate)", it might work ( need to try this, just occured, but looks ?).

Can anybody enlighten me about what actually the "ansidate" and "ingresdate" in the catalog.dat? In our installation, it is set to ingresdate as instructed by support.? is ansidate is iso format?

regards
ramke

[Bodo]

Hi Ramke,

I think I now understand what happens.
You send the date string from OpenROAD into access using a given format.
I assume it is formatted in a way the CDate function expects.

Then you try to compare this date with the date column in the database,
which is an ingresdate formatted date.

The ODBC interface is handling dates always as ansidates if the server is capable of handling ansidates.
Ansidate is not a date in iso format, but an own datatype with format "yyyy-mm-dd".
It can't contain time information or date/time ranges like an Ingresdate.
The "date_alias" setting in config.dat determines what the datatype "date" is mapped to.
So, the support was right to recommend using "ingresdate" as your date_alias setting in config.dat.
Otherwise you might encounter problems with existing applications (e.g. your OpenROAD applications), as your existing "date" data might not just contain "date only" information.

But there are known problems in the server when using/comparing ansidates and ingresdates.

Therefore you should ask the support team to provide a (server) patch for bugs 121297 and 120004.

There has also been an intermediate fix for the ODBC interface (SIR 121228) which introduces the "Coerce ingresdate syntax" option in the "Advanced" sheet of the ODBC Data Source Definition page - so the ODBC driver would just coerce all ansidates into ingresdates.
If you apply the server patch this would not be required.

[Ramke]

Hi Bodo,

The patches you referring at the end of your message is for Windows version or Linux. We are on Windows version of ingres and OR2006.

Thanks
Ramke