Unable to use Views and Joins

[Carsten Sørensen]

Hi Greg,

I have fixed the issue - created a new specific INGRES type connection.

You can get the join-builder views to work if you use e.g. the Informix Connection type in Dynamic AI.

The ODBC specific one will strip the prefixes that causes problems for INGRES, the Informix type doesn't.

The problem is now that the Informix connection type doesn't use the specific MOVE method that we created for INGRES compatibility... but you can test the JOINBUILDER... therefore I created the new INGRES type.

The funny thing is that I can't reproduce the crash at the moment... at least not with your testdb..

I apologize for not catching the error better in Dynamic AI! It is always an issue to get the right balance of things in the error reporting - and at which level we catch it.



Best regards,
Carsten

[Carsten Sørensen]

Hi Greg and Bob,

To avoid any misunderstandings!

Bob is 100% correct - that Dynamic AI SQL Views are encapsulated in a sub select when used on reports.

However - when using a JOIN-BUILDER data-source - Dynamic AI isn't using sub selects, and Dynamic AI relations are not related to sub-selects either. Dynamic AI relations are simply conditions for the data-source - eventually such conditions can be cross-db etc.

Best regards,
Carsten

[Carsten Sørensen]

Hi Greg,

I managed to get catch the error in the Dynamic AI error message dialog now.

Strange enough I can only get to this error with a special (0) cursor type... didn't know that the cursor type could have anything to do with messages coming back from the driver... but one learns every day

WMXPDB-2.jpg (72.19 KiB) Viewed 21678 times

I have hard-coded the cursor-type to 0 - I think it means "default" - for the new Ingres connection type - then we might get better information going forward.

BTW - Carsten never sleeps! - we are 22 people sitting around the globe answering under that alias name

Best regards,
Carsten

[shearerg]

Hi Carsten(s),

The detail you've provided on JOIN-BUILDER data sources and Dynamic relations sounds encouraging, so it will be very interesting to see how I go connecting to my older releases. Unfortunately I'll be at a Honeywell user group conference until Wednesday, looking at SCADA software etc, so I probably won't be able to do any further testing until Thursday. If I have time today I'll give the Informix connection type a try ... but I think I might be a bit too busy working around the house!

I'm really hoping the work you're doing here results in a few more Ingres users trying out Dynamic AI. From the little issues a relative newbie like myself has found ... I assume there can't me too many DAI Ingres users around yet. I know there is still quite a healthy Ingres community around ... so it surprises me a bit that no one else has been investigating DAI. Anyway, perhaps that will change in the future.

Greg

[Carsten Sørensen]

Hi Greg(s) ,

Sounds good!

I think you are right about being the first Ingres user - but - what a first user to get in the Dynamic AI community!!

Will have updated versions available for you during the week. I will be on vacation for a few days from Wednesday.

I wish you good fun on the conference!

Best regards,
Carsten

[shearerg]

Hi Carsten,

Just letting you know that I've done my own preliminary checks of the updates you've made and all looks very good! As anticipated, Dynamic JOIN data source definitions seem to work fine on all of my Ingres releases, while Dynamic SQL Views only function correctly on my latest release. In summary, for any Ingres developers who are interested:

ScreenShot084.jpg (22.52 KiB) Viewed 21674 times

Combined with the anticipated use of Dynamic AI relations between all database releases, this means that my tool set is now looking pretty healthy! Over the next couple of weeks I'll be trying to put together a 'convincing' demonstration of DAI capabilities to show our production managers. As part of this I may look at arranging a trial server license. I'll let you know how it goes.

Thanks for all your work,
Greg

[Carsten Sørensen]

Hi Greg,

That's great - thanks for the feedback!!

Best regards,
Carsten

[shearerg]

Hi again Carsten,

I've found that there is a formatting error in the last version you prepared, when entering dates from a calendar selection. From the odbc log, the formatted string looks like it is a little confused:

Code: Select all

!IIapi_query: queryText = Select count(*) AS DAICountSta FROM catdba.log DAI_ WHERE ((DAI_.machine='NM14') AND ((DAI_.timestamp Between '7/13-2010' AND '7/14-2010'))) AND (mod(date_part('min', timestamp),5) = 0) for readonly

!DBMS Error: (0x000010CE) '7/14-2010' is not a valid date/time value.


The above example is for selecting run time logging data between 13-Jul-2010 and 14-Jul-2010. It looks like it is being transformed to US mm/dd/yyyy format, but is actually coming out mm/dd-yyyy. I'm wondering if this is just an error in the 'Ingres special' version you prepared? If so, and it can easily be corrected, it would be good if the final format used could be one that is not easily misinterpreted.

My first preference would definitely be for dd-mmm-yyyy (14-Jul-2010) as it is unambiguous and is recognised by all of the database releases I'm using, regardless of the configured display format for the database. I believe this format would suit any potential Ingres users. Next for my purposes would be what Ingres describes as MULTINATIONAL dd/mm/yyyy (14/07/2010) which is also recognised by all of my database configurations .... but which is unlikely to suit all Ingres users. The yyyy-mm-dd format, which would seem quite logical, is only correctly recognised by my most recent installation.

Trust me to find another problem!! A screen image of the date selection arrangement follows.

ScreenShot085.jpg (22.39 KiB) Viewed 21672 times

[Carsten Sørensen]

Hi Greg,

Thanks for reporting this.

The mm/dd-yyyy format - should be - SQL ANSI standard or something like that... at least that has been working for other ODBC databases since long time. Whether that is a standard or not - we apparently need special date handling for Ingres. I think it should be irrelevant to the end-users which format we use in the SQL as long as the display on the reports and in filters is correct or?

I will investigate implementation of a special Ingres date handling.

Best regards,
Carsten

[shearerg]

Hi Carsten,

I was afraid it might have actually been a standard of some sort ... although it is certainly not familiar to me. I'll ask about this on the Ingres forum.

Actually, I've just remembered ....

Until recently Ingres had only its own date format, which is referred to as 'INGRESDATE', however 'ANSIDATE' is now also supported. The choice of using INGRESDATE or ANSIDATE is normally made at installation time, although I gather it can be changed afterwards. Even so, I don't think it is normally changed and is intended as an installation wide setting. Being a long term Ingres user, I've retained the INGRESDATE format in my current installations as it offers some nice features.

Despite this, the listed Ingres format for ANSIDATE is simply yyyy-mm-dd. Ingres lists some mm/dd formats with assumed year, but I haven't seen any reference to a mm/dd-yyyy format.

So, whilst my earlier comment regarding the dd-mmm-yyy format stands for installations using the INGRESDATE format ... this may not be the case for newer installations which may have adopted ANSIDATE formats. I'll definitely ask for some advice on the Ingres forum. I know date management can easily become an issue, and having 2 possibilities for the database doesn't make things any easier!

A bit later ....
I've been Googling ANSI date format, and all references seem to be to yyyy-mm-dd, so this would seem a reasonable format to put in place for Ingres and other databases. The downside for me at the moment would be that only my most recent INGRESDATE installation correctly recognises this format. If you ever feel like a real challenge in the future, it could be to allow for optional date formats to be selected.

Greg

[Carsten Sørensen]

Hi Greg,

Well - we are aware of the date-format-nightmare and that's one of the reasons that we have specific database connection types in Dynamic AI. With the new Ingres specific connection type we'll "just" have to add a handling for that. I think we should go for the one that is best compatible for all your Ingres installations - I'll await your response from the Ingres forum to learn what might be the best decision.

Best regards,
Carsten

[shearerg]

Hi Carsten,

I don't have a response from the Ingres forum yet ... but it turns out I don't need to wait for one!

In the back of my mind I had a memory that my 'problem' report used to work ok using the generic ODBC driver. I've now tested that and found it is the case. This suggests that the unusual mm/dd-yyyy format in your current Ingres driver was probably picked up during construction.

The other REALLY good news for me, is that I've also tested the generic ODBC option doing date selection against my earlier Ingres releases ... and they all work fine!! I've also checked the Ingres ODBC traces and found that the sql date string shown in the trace is constructed by Ingres to suit the target server version.

So, this means that in order to correct the current problem with your Ingres driver, the current date string formatting code just needs to be replaced by whatever is being done within the generic ODBC option. My expectation is that it will be providing an ANSI compliant string which is being adjusted as appropriate by the Ingres/NET ODBC driver to suit the target server.

Hope I've explained this all clearly enough, and that this means the Ingres connection definition can be corrected fairly easily

Regards,
Greg

[Carsten Sørensen]

Hi Greg,

Thanks for the feedback!

I can confirm your observations regarding the date conditions working fine with the generic ODBC connection type!

I will adjust the INGRES connection type in Dynamic AI to use the generic ODBC date handling.

Will let you know when you can pick-up the modified version!

Best regards,
Carsten

[shearerg]

Hi Carsten,

Just thought I'd post a brief update on my Dynamic Join versus Dynamic SQL situation. The main short coming I've found from Dynamic SQL not being available on my earlier releases, is that the complexity of the data behind the data source is limited, as you're undoubtedly aware.

In particular, some of my data sources would ideally make use of 'union' select statements. These can be easily constructed within Dynamic SQL, but I can't see how this can be achieved using Dynamic Joins. If there is a trick for doing this please let me know! Of course, with some restrictions, appropriate views can be constructed within the database ... but is a nuisance.

Anyway, all of the above really gives me incentive to upgrade the associated systems! Unfortunately these upgrades won't happen overnight ...

Hope you've been getting more sleep since my last post

Regards,
Greg

[Carsten Sørensen]

Hi Greg,

Thanks for the feedback!

A union builder has been discussed a few times - we'll make it sometime!

The issue that it would need to address in your case however would be that it can't handle the union'ed data-sources as 1 source (encapsulated in a surrounding SELECT) - as that is the limitation of the older driver. I.e. filters and conditions would have to be "injected" into each step of the union - and eventually each union data-source could have different column names - all in all - something that a union builder could handle but we haven't got it yet!

Best regards,
Carsten