HomeActive Query BuilderDate & Time dialects between different database types

Date & Time dialects between different database types


I've downloaded the free & trial version of your product and must admit, I'm very impressed. It's ideal when creating standard queries, however, I'm having problem with the syntax when applying a WHERE condition using a date. For example, MS-Access likes to format it as "myDate <= #01-01-2009#" where as SQLServer can be implemented as "myDate <= '2009-01-01' ", I'm sure if I used a connection sting to an Excel file, it'll probably require a different style again.

Can we get AQB to format date checking it correctly depending upon the database type? Can it format it corrcetly? or is this a feature of the full version?

Regards

Andrew
Andrew Watts | email
September 26 2009, 03:15 AM
Dear Andrew,

Unfortunately, Active Query Builder is not able to translate date constants between different database types.
Sergey Smagin | email
3 days, 1 hour since original post
Actually what I'm asking is if we can tell the control the type of notation to use based on the database being queried.

At the momement when building connected to an MS-Access database, when entering a condition against a Date field, on leaving the cell it encloses the value with single quotes. Which is fine for SQLServer, but for MS-Access it needs to be enclosed with #.
As a result, if we access the generated SQL text and manually change the Where condition and enclose the a date with #, the control then has problems trying to parse the query.

Andrew
Andrew Watts | email
2 weeks, 6 days since original post
I understand the problem. We should significantly improve the expression type calculation in our component to make such conversion possible in 100% cases. For now we can only make conversion of rather simple expressions (datefield = #dateconstant#) in one of the next minor versions. Would it be sufficient in your case?
Sergey Smagin | email
3 weeks, 2 days since original post
Thanks for the reply.

I'm evaluating the free version with the intension of purchasing the full product. I'm quickly assessing how easy it would be to offer the end user such a tool allowing them to connect to different database types and how easy it would be to create basic statements. My test so far are simple apps, similar to the examples provided with the product, allowing you to connect SQLServer, Access and OLEDB etc... As you can see I came accross the problem with the date fields where the criteria grid automatically encloses any entered dates within quotes.

Can this easily be coded around in the full version? How do your partners such as Code Gear get around this type of issue of different dialects between database when coded within their product?

Andrew
Andrew Watts | email
3 weeks, 2 days since original post
Dear Andrew,

Using Active Query Builder API you may iterate through all criteria items and change them manually. In case of simple expressions (when expression is a simple database field), you may check it's data type and replace quotation symbols according to your database.

Have a look at the Non-visual demo (it's included in the Trial version) and find the AnalyzeQuery and LoadUnionSubQuery procedures. It has example of retrieval of all criteria item strings (starting from the "collect conditions" comment).

In case of working with .NET Edition, you should check the
criteriaItem.ExpressionField.FieldType property to detect data type. Please note that ExpressionField property could be Null in case of complex expressions.

Criteria item strings can also be complex expressions, such as "= #date1# or between #date2# and #date3#".

Also please note that there are many other database server dependent clauses such as TOP, LIMIT, etc. They will be cutted off by Active Query Builder on changing of the syntax provider.

I don't think that CodeGear ever thought about this problem.
Sergey Smagin | email
3 weeks, 2 days since original post
Many thanks for your reply. I'll look into your suggestions.

Andrew
Andrew Watts | email
3 weeks, 3 days since original post
This topic is closed