How to develop with databases

[Francisco Gomez Balastegui]

Hi, i'm working with AO but we are getting issues when we have differents databases.

[Nir Haimov]

Hi,

Please explain further, what do you mean "getting issues", what issues?

what do you mean by different databse? different between where to where? which databases type?

[Francisco Gomez Balastegui]

Hi @Nir Haimov Thanks for the response.

For example, this line:

MyDataAO[] mydata = ao.find(MyDataAO.class, Query.select().where("Name = ?", campo)); 

this on jira H2 databases works correctly when i'm developing, but when i install the plugin on a Jira with Postgresql Database i get this error:

org.postgresql.util.PSQLException: ERROR: doesn't exist the colum «Name»

So is solve this on Postgresql with this:

MyDataAO[] mydata = ao.find(MyDataAO.class, Query.select().where("\"NAME\" = ?", campo)); 

This is gona work on MySQL, H2 and postgres or i need to diferientate the SQL pending on the database installed?

Thanks

[Nir Haimov]

It's pretty weird that you need to change it to "\"NAME\" to work on postgress, i don't remember i had to do it in the past...

But ("Name = ?", campo)) will work on MySQL, so you don't need to do another change for MySQL

Hope my answer help

[Francisco Gomez Balastegui]

yes but for postgres it only work on me with this "\"NAME\"" so if this doesn't work for mysql and in mysql work with ("Name = ?", campo)) how i can know with database is selected to execute the one or the other?

Thanks

[Nir Haimov]

it's hard for me to tell without seeing the situation in my own eyes.

But the AO queries should be the same for every database, otherwise it will extremely hard to develop plugins to support all databases types.

I think you need to figure out how to make sure that

MyDataAO[] mydata = ao.find(MyDataAO.class, Query.select().where("Name = ?", campo)); 

works in all databses, and not change to 

MyDataAO[] mydata = ao.find(MyDataAO.class, Query.select().where("\"NAME\" = ?", campo)); 

in some of them.

I don't have postgress installed so i can't try to find a solution for you or to see if i have the same issue.

[Francisco Gomez Balastegui]

@Nir Haimov  Hi, thanks for your help, maybe someone have installed the both databases and can help me, but i'm sure that the firts option didn't work on postgres 9.6 for me, and i didn't know why. But it said that the Name table didn't exist and it exist for me.

Thanks

[Nic Brough -Adaptavist-]

I think the trick here is to stop thinking about the database.  Active objects give you a layer of code that means you should never have to worry about the database.

Ask Jira what the data is, it will tell you what is in the database, irrespective of what the database really is.

As @Nir Haimov says - the AO queries should be the same for every database

If you're getting different results from an h2 backed system than you get from one backed by a real database, my guess would be your h2 database is failing (which is not surprising)