Problem with Oracle Objects ORA-00936: missing expression

[orsted]

Below is the code I have in my app:

Dim emp As V_PERS_BNFT_DED_PLAN_HIST = New V_PERS_BNFT_DED_PLAN_HIST
emp.DatabaseInstanceName = \"EDW\"
emp.DynamicQueryInstanceName = \"Default Oracle\"
' Limit the columns returned by the SELECT query
' Add an ORDER BY clause
emp.Query.AddOrderBy(V_PERS_BNFT_DED_PLAN_HISTSchema.BNFT_DED_CD)

' Add a WHERE clause
emp.Where.BNFT_DED_CD.Value = \"HH1\"
emp.Where.BNFT_DED_CD.Operator = NCI.EasyObjects.WhereParameter.Operand.Equal

emp.Query.AddConjunction(NCI.EasyObjects.WhereParameter.Conj.And)
emp.Where.EDW_PERS_ID.Value = 4000
emp.Where.EDW_PERS_ID.Operator = NCI.EasyObjects.WhereParameter.Operand.LessThan
If Not emp.Query.Load() Then
Return
End If


as you can tell, i used some of the sample code i found, but added the two item where clause. The output sql query conforms to standards best I can tell (select * from X where X=X and Y<#) is the format... so .. where is this error coming from?

thanks

Marko

[mgnoonan]

Hi Marko,

Try removing the following two lines from your code example above:

Code: Select all

emp.Where.BNFT_DED_CD.Operator = NCI.EasyObjects.WhereParameter.Operand.Equal
emp.Query.AddConjunction(NCI.EasyObjects.WhereParameter.Conj.And)


There's no need to add the Operand.Equal because it is the default operation for Where parameters. Same for the AddConjunction call, which is really only needed in more complex dynamic queries, \"AND\" is automatically used.

If that doesn't work, post the code and the contents of emp.LastQuery and I will help you troubleshoot.

Thanks!

[orsted]

It worked when I removed those two items - my question then is tho, why did that work?

The LastQuery object holds the following with the changes you suggest:

SELECT * FROM EDW.V_PERS_BNFT_DED_PLAN_HIST WHERE BNFT_DED_CD = :BNFT_DED_CD AND EDW_PERS_ID < :EDW_PERS_ID ORDER BY BNFT_DED_CD ASC

With the original code, it contains:

SELECT * FROM EDW.V_PERS_BNFT_DED_PLAN_HIST WHERE BNFT_DED_CD = :BNFT_DED_CD AND AND EDW_PERS_ID < :EDW_PERS_ID ORDER BY BNFT_DED_CD ASC

So I see what EasyObjects is doing - my question is, why isn't the default behaviour \"overridden\" when I do include the

emp.Query.AddConjunction(NCI.EasyObjects.WhereParameter.Conj.And) statement ?


In addition - is there a way to use EasyObjects to return queries based on results from multiple joined tables? The reason I ask is that this \"simple\" query is just from one table, but the eventual need will be to return data from 5 different tables joined together?

Thanks


Marko

[mgnoonan]

So I see what EasyObjects is doing - my question is, why isn't the default behaviour "overridden" when I do include the

emp.Query.AddConjunction(NCI.EasyObjects.WhereParameter.Conj.And) statement ?

The dynamic queries are designed to be as flexible as possible. While your point is well taken, it is still possible for the developer to construct queries which contain errors or return incorrect results.

(As a side point, I'm not real thrilled with Oracle's "helpful" error messages either.)

The automatic injection of the AND conjunction is meant to be a shortcut for developers, so they don't have to specify it every time (you can also override it by calling Query.Load("OR")). The AddConjunction call is really there for constructing complex queries that use a combination of AND and OR (see Mike's excellent post on querying with dOOdads).

There is no "on the fly" query parsing, either. EO basically relies on the db engine to process the query and either throw an error or return the results (why reinvent the wheel?). That's why the LastQuery property is there, so the developer can check how the query was constructed and correct any mistakes.

I will take a look at making the enhancement you suggest, but as I'm close to releasing version 1.0, I don't think this will make it.

In addition - is there a way to use EasyObjects to return queries based on results from multiple joined tables? The reason I ask is that this "simple" query is just from one table, but the eventual need will be to return data from 5 different tables joined together?

You have a few of options open to you.

• You can create a View, join the tables in the View and set the QuerySource property on the EO to the View. The EO can contain any results, even if they bear no relationship to the table the EO was generated from. You can also create an EO based on the View using the Business Entity View template.
• You can join the tables in a proc and use the LoadFromSql functions to load the EO with the results.
• You can write some inline SQL and use the LoadFromSql functions to load the EO with the results.
• You can call the EntLib DAAB directly and return a DataSet.

Let me know if you have any other questions. I'm curious about your experiences with Oracle.

Thanks.

[orsted]

Your suggestions regarding the multi-table thoughts are great - except that for *some inexplicable reason*, they won't let me create views on the server. I am relegated to having to create dynamic SQL (talk about a time waster!), but would love to have an \"object\" to attach to it, as opposed to just returning a dataset.

thanks again


Marko

[mgnoonan]

No stored procs, either? That's inhumane!!!

If those are your options, then I would write as much as you can using the dynamic query mechanism. For the special cases where you need to join tables, create functions in your concrete classes that just use inline SQL statements. Then you can use the LoadFromSQL functions to load your objects.

I can give you some examples if you need them.

[orsted]

examples would be *awesome*.. i'm new to this whole ORM thing, and objects for databases, etc... i have always just applied SQL straight out, got a dataset back, etc.. but then it's not as \"portable\" to other needs down the road.

tahnks

Marko

[mgnoonan]

Well, not being able to create views and stored procedures is going to slow you down, unfortunately.

But you can still do best practices by keeping all the SQL code in the data access layer of your application. I see that one violated all too frequently.

Wait, if you can't create procs, are you overriding the LoadByPrimaryKey and LoadAll methods?

[orsted]

the trial example I've been workign with thus far today is a single view given to me by the DBA. However, other items in my project do not use DAAB at all - they are just straight handwritten SQL queries with joins. So I'm not over-riding anything, because there's nothing to override


Marko