cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Andrus Adamchik <and...@objectstyle.org>
Subject Re: SQLTemplate returning NULL results
Date Wed, 29 May 2013 09:56:31 GMT
Cool. Yeah, we need better diagnostics here. We can't tell people to use all lowercase column
names (as we do support mixed case column names), but maybe we can have some intelligence
detecting that we are not dealing with an outer join here, and throwing instead of returning
null.

BTW a usual workaround is to wrap each column in SQLTemplate sql string with #result(..) directive,
which contains the label that we want to see in Cayenne. But that of course makes SQL even
less readable and precludes "SELECT *".

A.

On May 29, 2013, at 12:01 PM, giulio.cesare@gmail.com wrote:
> Andrus,
> 
> I have managed to sort out what was the problem: in my model I have Primary
> Keys of DBEntities defined using camelCase (eg: "id_saleSummary").
> 
> With this setup, SelectQuery works fine, but I have found no combination of
> the different settings to have SQLTemplate work right; the problem springs
> out in ObjectResolver.createObjectId method, where no matching key is found
> in the provided DataRow instance. Other fields (non PK ones) don't seem to
> be affected by this issue.
> 
> In the code there is a comment when the PK field is not found -and null is
> returned instead of throwing an exception- saying that "this is possible
> when processing left outer joint prefetches".
> 
> As soon as I changed the primary key name to "id_salesummary" (without
> changing the actual DB schema or anything else), SQLTemplate started
> working immediately.
> 
> I would consider this a very minor issue, if it was not for the subtle
> consequences it caused, and the huge amount of time it took me to isolate
> it. Not sure what the best way to "fix" it, but I would be also happy with
> a Modeler warning saying to use only lowercase letters for PK definitions.
> :)
> 
> Thanks again for the excellent tool, and awesome support.
> 
> Best regards,
> 
> Giulio Cesare
> 
> 
> 
> 
> 
> On Wed, May 29, 2013 at 8:41 AM, Andrus Adamchik <andrus@objectstyle.org>wrote:
> 
>>> But I am still missing the point where the List<DataRow> is converted
>> into
>>> actual objects of the Class defined into the ObjEntity associated with
>> the
>>> `root` class specified when creating the new SQLTemplate instance.
>> 
>> DataDomainQueryAction.interceptObjectConversion(..)
>> 
>> On May 29, 2013, at 2:33 AM, giulio.cesare@gmail.com wrote:
>> 
>>> Andrus,
>>> 
>>> I have looked closer into RowDescriptorBuilder.getDesciptor method. The
>>> returned RowDescriptor has the right (at least to my tired eyes) list of
>>> ColumnDescriptor.
>>> 
>>> The only weird part I have seen, is that each ColumnDescriptor has the
>>> field `tableName` set to null (together with `namePrefix` and
>>> `procedureName` fields; but I am less concerned about these later two).
>>> 
>>> But I am still missing the point where the List<DataRow> is converted
>> into
>>> actual objects of the Class defined into the ObjEntity associated with
>> the
>>> `root` class specified when creating the new SQLTemplate instance.
>>> 
>>> I will keep digging. :)
>>> 
>>> Thanks,
>>> 
>>> Giulio Cesare
>>> 
>>> 
>>> 
>>> 
>>> On Tue, May 28, 2013 at 6:08 PM, Andrus Adamchik <andrus@objectstyle.org
>>> wrote:
>>> 
>>>> This is correct, but there's lots of obscure cases here that can be
>>>> ignored. Take a look at:
>>>> 
>>>> 1. RowDescriptorBuilder.getDescriptor(..)
>>>> 2. JDBCResultIterator.createFullRowReader(..)
>>>> 3. FullRowReader.readRow(..)
>>>> 
>>>> RowDescriptor returned from 1 is fed to 2, that creates FullRowReader. 3
>>>> would actually create a DataRow (which is probably empty or contains
>>>> incorrect keys). And somewhere downstream this DataRow is turned to NULL
>>>> object (and this is where we should throw an exception IMO).
>>>> 
>>>> That is if I am correct about the flow in your case :)
>>>> 
>>>> Andrus
>>>> 
>>>> 
>>>> On May 28, 2013, at 5:35 PM, giulio.cesare@gmail.com wrote:
>>>> 
>>>>> Hello Andrus,
>>>>> 
>>>>> I have dug through the code, and I have found a point where something
I
>>>>> don't understand happens.
>>>>> 
>>>>> When `SQLTemplateMetadata.resolve` method is invoked, `query.result()`
>>>>> return null, and this means that `resultSetMapping` is set to null.
>>>>> 
>>>>> But the query is actually returning three results; it looks like
>>>>> SQLTemplateMetadata is accessing query.result before the query is
>>>> actually
>>>>> executed. Does this analysis make any sense?
>>>>> 
>>>>> Regards,
>>>>> 
>>>>> Giulio Cesare
>>>>> 
>>>>> 
>>>>> 
>>>>> On Tue, May 28, 2013 at 4:22 PM, giulio.cesare@gmail.com <
>>>>> giulio.cesare@gmail.com> wrote:
>>>>> 
>>>>>> Jurgen,
>>>>>> 
>>>>>> thanks for the tip; but I am either completely blind (as I can see
no
>>>>>> differences in the query compared to the structure of the DB), or
>> there
>>>> is
>>>>>> something else going on in my case.
>>>>>> 
>>>>>> As stated in the initial message, I have also tried using the same
>> exact
>>>>>> SQL statement logged by Cayenne when using SelectQuery instead of
>>>>>> SQLTemplate; but to no avail.
>>>>>> 
>>>>>> I will post here any findings.
>>>>>> 
>>>>>> Giulio Cesare
>>>>>> 
>>>>>> 
>>>>>> 
>>>>>> On Tue, May 28, 2013 at 3:42 PM, <dollj@xsinet.co.za> wrote:
>>>>>> 
>>>>>>> Hi Giulio,
>>>>>>> 
>>>>>>> I've had this problem before. Try the following: goto SaleSummary
in
>>>>>>> Cayenne Modeler and click on the attributes tab.
>>>>>>> 
>>>>>>> Make sure that your SQLTemplate query contains all the DbAttributes
>>>>>>> listed.
>>>>>>> They must be spelt exactly the same way (CaPs SenSitiTive), order
>> isn't
>>>>>>> important.
>>>>>>> 
>>>>>>> Also the table's Primary key MUST be included as indicated in
the
>>>> related
>>>>>>> table/view dbEntity for SaleSummary. Also CaPs SenSitiTive !
>>>>>>> 
>>>>>>> Hope this helps
>>>>>>> regards
>>>>>>> Jurgen
>>>>>>> 
>>>>>>> 
>>>>>>>> Hello everybody,
>>>>>>>> 
>>>>>>>> I am getting lost in trying to use an SQLTemplate query.
>>>>>>>> 
>>>>>>>> I have tried to reduce the problem to the core, and this
is the
>> point
>>>>>>>> where
>>>>>>>> I am stuck.
>>>>>>>> 
>>>>>>>> The following two lines of code do what I was expecting them
to do:
>>>>>>>>  Query query = new SelectQuery(SaleSummary.class);
>>>>>>>> List<SaleSummary> saleSummariesToProcess =
>>>>>>>> objectContext.performQuery(query);
>>>>>>>> 
>>>>>>>> Looking into the logs, this is the SQL statement generated
>>>> automatically
>>>>>>>> by
>>>>>>>> Cayenne:
>>>>>>>>> SELECT t0.amount, t0.unique_id, t0.period, t0.registrationDate,
>>>>>>>>> t0.id_contract, t0.id_currency, t0.id_customer,
>> t0.id_frontendSystem,
>>>>>>>>> t0.id_saleSummary, t0.id_store FROM wolf.SaleSummary
t0
>>>>>>>> 
>>>>>>>> So far, so good. But as soon as I replace the SelectQuery
with a
>>>>>>>> SQLTemplate, I can no longer get any result. Actually, I
get the
>>>>>>> expected
>>>>>>>> results, but instead of being instances of SaleSummary, the
list
>>>>>>> contains
>>>>>>>> all nulls.
>>>>>>>> 
>>>>>>>> I need to run a fairly complex query that I would like to
store into
>>>> the
>>>>>>>> model, but also the most trivial tests are failing.
>>>>>>>> 
>>>>>>>> I have tried all the following options, all with the same
bad
>> result:
>>>>>>>> - query = new SQLTemplate(SaleSummary.class, "select * from
>>>>>>> salesummary");
>>>>>>>> - query = new SQLTemplate(SaleSummary.class, "SELECT * FROM
>>>>>>> SALESUMMARY");
>>>>>>>> - query = new SQLTemplate(SaleSummary.class, "SELECT t0.amount,
>>>>>>>> t0.unique_id, t0.period, t0.registrationDate, t0.id_contract,
>>>>>>>> t0.id_currency, t0.id_customer, t0.id_frontendSystem,
>>>> t0.id_saleSummary,
>>>>>>>> t0.id_store FROM wolf.SaleSummary t0");
>>>>>>>> 
>>>>>>>> This latest option includes the same exact SQL query logged
by the
>>>>>>> initial
>>>>>>>> working code (that was using the SelectQuery class instead
of
>>>>>>>> SQLTemplate).
>>>>>>>> 
>>>>>>>> Am I doing something blatantly wrong, or is there something
weird
>>>> going
>>>>>>> on
>>>>>>>> here?
>>>>>>>> 
>>>>>>>> BTW, I am using Cayenne 3.1B2. I was tempted to go back to
3.0.2,
>> but
>>>>>>>> being
>>>>>>>> the model files not compatible, I was trying to understand
why it is
>>>> not
>>>>>>>> working on 3.1B2 first.
>>>>>>>> 
>>>>>>>> Any hints?
>>>>>>>> 
>>>>>>>> Best regards,
>>>>>>>> 
>>>>>>>> Giulio Cesare
>>>>>>>> 
>>>>>>> 
>>>>>>> 
>>>>>>> 
>>>>>> 
>>>> 
>>>> 
>> 
>> 


Mime
View raw message