Mailing-List: contact user-help@cayenne.apache.org; run by ezmlm
Precedence: bulk
Reply-To: user@cayenne.apache.org
Received-SPF: pass (athena.apache.org: domain of giulio.cesare@gmail.com
 designates 209.85.214.53 as permitted sender)
MIME-Version: 1.0
In-Reply-To: <BA6F434C-48D7-4DCD-A5B9-58316AB5E563@objectstyle.org>
References: <13085.105.249.62.7.1369748538.squirrel@webmail.xsinet.co.za>
	<CAAOOpAMzJAf7EuYfEgc5JytGPrY87QbfE3v6Zs1h43omqjfUSw@mail.gmail.com>
	<CAAOOpANwOxpZRq6UeD03dwXcWrEjJT=tuR+7x17hcLtAo-wBRQ@mail.gmail.com>
	<58EAFA79-14E8-4923-8502-A2ABD0263C87@objectstyle.org>
	<CAAOOpAOx6XRZBxrGvMHFfC4omWFUYPhoy6sCrEqqkhQG=BgR+w@mail.gmail.com>
	<BA6F434C-48D7-4DCD-A5B9-58316AB5E563@objectstyle.org>
Date: Wed, 29 May 2013 11:01:40 +0200
Message-ID: 
 <CAAOOpAO9ze1AKt3OCZmJpCNt6exCmwUKJWRc19T2ESDT=6diPA@mail.gmail.com>
Subject: Re: SQLTemplate returning NULL results
From: "giulio.cesare@gmail.com" <giulio.cesare@gmail.com>
To: user@cayenne.apache.org
Content-Type: multipart/alternative; boundary=bcaec52bf14b46361c04ddd7a116

--bcaec52bf14b46361c04ddd7a116
Content-Type: text/plain; charset=UTF-8

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
> >>>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>>
> >>
> >>
>
>

--bcaec52bf14b46361c04ddd7a116--