cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "giulio.cesare@gmail.com" <giulio.ces...@gmail.com>
Subject Re: SQLTemplate returning NULL results
Date Wed, 29 May 2013 10:09:41 GMT
Andrus,

the weird thing is that, even if the SQL query had the column with the
correct identifier (id_saleSummary), ObjectResolver.createObjectId would
get a lowercase only value (id_salesummary). Even with no explicit
setColumnNamesCapitalization
option.

I suppose that non PK field keys (to be matched against DataRow items) are
computed in a slightly different way that preservers original letter case.

The tricky part was realizing that no matter how consistent I was in using
camelCase identifiers, if I want to use SQLTemplate, I need to put PK
identifiers all lowercase.

Regards,

Giulio Cesare




On Wed, May 29, 2013 at 11:56 AM, Andrus Adamchik <andrus@objectstyle.org>wrote:

> 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
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message