Generic Insert and Update Queries to work on both Oracle and SQLServer

[Former Member]

xMII12.0.2

I need to write queries which will be able to run on both Oracle and SQLServer database tables without any changes. It needs to be able to handle dates without including Oracle specific date functions (TO_DATE, TO_CHAR, TO_TIMESTAMP, etc.).

I did read a post earlier by Jeremy Good regarding the use of ED and SD which invoke the DatePrefix and DateSuffix in the data server configuration. That seems to work fine in cases of only trying to insert two distinct dates. However, what do you do in the case of having three or more dates to insert. An example query might be:

Insert into ProductionOrder

(ProdOrdNbr, Plant, Material, Quantity, UOM, DeliveryDate, ProdStartDate, ProdFinishDate, CreateDate, LastModifiedDate)

Values

('0100001001', '001', '000000000007887780', 20.0, 'PC', '21-FEB-08 22:01:19','11-FEB-08 00:01:34', '12-FEB-08 02:44:59', '01-FEB-08 12:00:00', '04-FEB-01 13:22:13')

So far I have been using the TO_DATE to convert the dates successfully, but SQLServer does not recognize that function (not surprising since it is an Oracle specific function). So I would have to go through all the transactions/query templates and rebuild them separately to deal with the different database vendors.

Any suggestions?

Thanks,

Mike

[agentry_src]

Thanks everyone for your helpful comments.

[jcgood25]

Mike,

With BLS you'll have more than Date differences between Oracle and SQL. In most cases Oracle will return all upper cased column names, whereas SQL will give you what you ask for. When working with XPATH, etc. this will present additional challenges to keep in mind.

Depending upon the scope of the project, have you considered having double query templates for the problem spots? Session variables would also make the front-end web content quite easy if your concerns are for the roll-out potential for this application.

Regards,

Jeremy

[agentry_src]

I know there are more differences, but we have not run into any major problems working around them except where dates are concerned. But I think that we have gotten enough ideas to solve the problem. Will let you know what our solution was. Right now the [SD] and [ED] will take care of our Select Queries. Our insert queries look like we may have to do some additional work, but at worst, double queries will do the job.

We did find out that the Timestamp datatype for the table column does return the full date and time (in xml format) whereas the Date datatype only returns the date and the time is set to 00:00:00. That by itself resolves a lot of the problems.

Thanks,

Mike

[salvatore_castro]

Keep in mind that the ED parameter is by default the current timestamp....

Sam

[agentry_src]

Thanks for the reminder.

[Former Member]

Mike:

You could create all of your SQL access inside of BLS transactions (wrapped in Xacute queries), and build the SQL based on a Global property flag (e.g. DatabaseType), or something like that.

Rick

[agentry_src]

Hi Rick,

Are you talking about dynamically building each script based on a server setting and datatype? If I understand you correctly, I guess it could be done that way, but it would be a royal pain to maintain. I have done such things before and can see how it could be done.

But is there no way to invoke the DatePrefix and DateSuffix besides the SD and ED parameters? Or did I misunderstand your response?

I would be perfectly happy to build all the queries inside BLS transactions. In the few cases, where they are not already contained in the BLS, we could throw a BLS wrapper around it and not pay much of a penalty in performance.

Thanks,

Mike

[Former Member]

ActiveRecord in the RubyOnRails framework is database agnostic.

[Former Member]

Hmmm. Ruby on Rails. I really, really tried to like it...and I really tried to make it do something useful...and I found it really limiting and awkward in the end...and, as with many early RoR enthusiasts, ended up going another way...

[agentry_src]

I have not seen the RoR application. How would I be able to review it?

[Former Member]

Yeah, I've been wanting to "try" to get to use it for about 2 years now. What I've seen, I've liked. It is a bit different though.