You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cayenne.apache.org by "giulio.cesare@gmail.com" <gi...@gmail.com> on 2013/05/28 08:52:43 UTC
SQLTemplate returning NULL results
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
Re: SQLTemplate returning NULL results
Posted by Andrus Adamchik <an...@objectstyle.org>.
Take a look at JDBCResultIterator. This is the heart of it.
On May 28, 2013, at 4:00 PM, "giulio.cesare@gmail.com" <gi...@gmail.com> wrote:
> Andrus,
>
> can you point me to the code where Cayenne tries to match what is returned
> by JDBC and tries to match it to DbEntity fields?
> Setting a breakpoint there I may be able to understand what is going wrong.
>
> Thanks,
>
> Giulio Cesare
>
>
>
> On Tue, May 28, 2013 at 2:50 PM, Andrus Adamchik <an...@objectstyle.org>wrote:
>
>> I think the culprit is still what's in the ResultSet - Cayenne does not
>> recognize the column names returned by JDBC. The reasons can be few…
>> Another workaround might be to use #result directive to name each column to
>> match a corresponding DbEntity name.
>>
>> A.
>>
>> On May 28, 2013, at 3:44 PM, giulio.cesare@gmail.com wrote:
>>
>>> Hello Andrus,
>>>
>>> unfortunately your suggestion didn't help. I am now trying to build my
>>> project using Cayenne code base from repository Head, to see if I can
>> spot
>>> what is going wrong.
>>>
>>> Thanks for the help.
>>>
>>> Regards,
>>>
>>> Giulio Cesare
>>>
>>>
>>>
>>> On Tue, May 28, 2013 at 9:24 AM, Andrus Adamchik <andrus@objectstyle.org
>>> wrote:
>>>
>>>> I think I've seen it before. This could the column naming in the result
>>>> set not exactly matching DbEntity. Try this maybe:
>>>>
>>>> query.setColumnNamesCapitalization(CapsStrategy.LOWER);
>>>>
>>>> And yes, we need to improve this in Cayenne - instead of NULLs we prolly
>>>> need to throw an Exception or something.
>>>>
>>>> Andrus
>>>>
>>>>
>>>> On May 28, 2013, at 9:52 AM, giulio.cesare@gmail.com wrote:
>>>>
>>>>> 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
>>>>
>>>>
>>
>>
Re: SQLTemplate returning NULL results
Posted by "giulio.cesare@gmail.com" <gi...@gmail.com>.
Andrus,
can you point me to the code where Cayenne tries to match what is returned
by JDBC and tries to match it to DbEntity fields?
Setting a breakpoint there I may be able to understand what is going wrong.
Thanks,
Giulio Cesare
On Tue, May 28, 2013 at 2:50 PM, Andrus Adamchik <an...@objectstyle.org>wrote:
> I think the culprit is still what's in the ResultSet - Cayenne does not
> recognize the column names returned by JDBC. The reasons can be few…
> Another workaround might be to use #result directive to name each column to
> match a corresponding DbEntity name.
>
> A.
>
> On May 28, 2013, at 3:44 PM, giulio.cesare@gmail.com wrote:
>
> > Hello Andrus,
> >
> > unfortunately your suggestion didn't help. I am now trying to build my
> > project using Cayenne code base from repository Head, to see if I can
> spot
> > what is going wrong.
> >
> > Thanks for the help.
> >
> > Regards,
> >
> > Giulio Cesare
> >
> >
> >
> > On Tue, May 28, 2013 at 9:24 AM, Andrus Adamchik <andrus@objectstyle.org
> >wrote:
> >
> >> I think I've seen it before. This could the column naming in the result
> >> set not exactly matching DbEntity. Try this maybe:
> >>
> >> query.setColumnNamesCapitalization(CapsStrategy.LOWER);
> >>
> >> And yes, we need to improve this in Cayenne - instead of NULLs we prolly
> >> need to throw an Exception or something.
> >>
> >> Andrus
> >>
> >>
> >> On May 28, 2013, at 9:52 AM, giulio.cesare@gmail.com wrote:
> >>
> >>> 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
> >>
> >>
>
>
Re: SQLTemplate returning NULL results
Posted by Andrus Adamchik <an...@objectstyle.org>.
I think the culprit is still what's in the ResultSet - Cayenne does not recognize the column names returned by JDBC. The reasons can be few… Another workaround might be to use #result directive to name each column to match a corresponding DbEntity name.
A.
On May 28, 2013, at 3:44 PM, giulio.cesare@gmail.com wrote:
> Hello Andrus,
>
> unfortunately your suggestion didn't help. I am now trying to build my
> project using Cayenne code base from repository Head, to see if I can spot
> what is going wrong.
>
> Thanks for the help.
>
> Regards,
>
> Giulio Cesare
>
>
>
> On Tue, May 28, 2013 at 9:24 AM, Andrus Adamchik <an...@objectstyle.org>wrote:
>
>> I think I've seen it before. This could the column naming in the result
>> set not exactly matching DbEntity. Try this maybe:
>>
>> query.setColumnNamesCapitalization(CapsStrategy.LOWER);
>>
>> And yes, we need to improve this in Cayenne - instead of NULLs we prolly
>> need to throw an Exception or something.
>>
>> Andrus
>>
>>
>> On May 28, 2013, at 9:52 AM, giulio.cesare@gmail.com wrote:
>>
>>> 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
>>
>>
Re: SQLTemplate returning NULL results
Posted by "giulio.cesare@gmail.com" <gi...@gmail.com>.
Hello Andrus,
unfortunately your suggestion didn't help. I am now trying to build my
project using Cayenne code base from repository Head, to see if I can spot
what is going wrong.
Thanks for the help.
Regards,
Giulio Cesare
On Tue, May 28, 2013 at 9:24 AM, Andrus Adamchik <an...@objectstyle.org>wrote:
> I think I've seen it before. This could the column naming in the result
> set not exactly matching DbEntity. Try this maybe:
>
> query.setColumnNamesCapitalization(CapsStrategy.LOWER);
>
> And yes, we need to improve this in Cayenne - instead of NULLs we prolly
> need to throw an Exception or something.
>
> Andrus
>
>
> On May 28, 2013, at 9:52 AM, giulio.cesare@gmail.com wrote:
>
> > 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
>
>
Re: SQLTemplate returning NULL results
Posted by Andrus Adamchik <an...@objectstyle.org>.
I think I've seen it before. This could the column naming in the result set not exactly matching DbEntity. Try this maybe:
query.setColumnNamesCapitalization(CapsStrategy.LOWER);
And yes, we need to improve this in Cayenne - instead of NULLs we prolly need to throw an Exception or something.
Andrus
On May 28, 2013, at 9:52 AM, giulio.cesare@gmail.com wrote:
> 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