You are viewing a plain text version of this content. The canonical link for it is here.
Posted to solr-user@lucene.apache.org by Juan Manuel Alvarez <na...@gmail.com> on 2010/12/30 03:58:29 UTC
UPDATE query in deltaquery
Hi! I would like to ask you a question about using a deltaQuery in DIH.
I am syncing with a PostgreSQL database.
At first I was calling a function that made two queries: an UPDATE and a SELECT.
The select result was properly returned, but the UPDATE query did not
made any changes,
so I tried calling the same function from a PostgreSQL client and
everything went OK.
So I tried calling a simple UPDATE query directly in the deltaQuery
and I receive a
NullPointerException that I traced to the line 251 of the JdbcDataSource.java
colNames = readFieldNames(resultSet.getMetaData());
The question is: is there a way I can make the update query work in
the deltaQuery
or am I doing something wrong?
Happy new year!!!!
Cheers!
Juan M.
Re: UPDATE query in deltaquery
Posted by Juan Manuel Alvarez <na...@gmail.com>.
Hi Travis!
I am executing a function in de DB that has two queries: an UPDATE and
a SELECT, and I am getting the select results ok, but the update has
no effects, so it seems that that approach is not working.
Cheers!
Juan M.
On Thu, Dec 30, 2010 at 11:26 AM, Travis Low <tl...@4centurion.com> wrote:
> If you are getting a null pointer exception here:
>
> colNames = readFieldNames(resultSet.getMetaData());
>
> Then that implies the DIH code is written to expect a select statement. You
> might be able to fool it with some SQL injection:
>
> update blah set foo=bar where id=1234; select id from blah
>
> But if that doesn't work then you may be out of luck.
>
> cheers,
>
> Travis
>
> On Thu, Dec 30, 2010 at 8:26 AM, Juan Manuel Alvarez <na...@gmail.com>wrote:
>
>> Erick:
>>
>> Thanks for the quick response.
>>
>> I can't use the timestamp for doing DIH, so I need to use a custom
>> field that I need to update one for each delta-import, so that is why
>> I need to execute an UPDATE on the deltaQuery.
>>
>> Cheers!
>> Juan M.
>>
>> On Thu, Dec 30, 2010 at 10:07 AM, Erick Erickson
>> <er...@gmail.com> wrote:
>> > WARNING: DIH isn't my strong suit, I generally prefer doing things
>> > in SolrJ. Mostly I asked for clarification so someone #else# who
>> > actually knows DIH details could chime in...
>> >
>> > That said, I'm a bit confused. As I understand it, you shouldn't
>> > be UPDATEing anything in DIH, it's a select where documents
>> > then get added to Solr "by magic". Your post leads me to believe
>> > that you're trying to change the database via DIH, is that at
>> > all true?
>> >
>> > This is based in part on
>> > "The ids are returned ok, but the UPDATE has no effect on the database"
>> > Or do you mean "effect on the index"? If the latter, then the select
>> > would only have a chance of updating the IDs of the Solr documents...
>> >
>> > At least I think that's close to reality...
>> >
>> > Best
>> > Erick
>> >
>> > On Thu, Dec 30, 2010 at 7:52 AM, Juan Manuel Alvarez <naicigam@gmail.com
>> >wrote:
>> >
>> >> Hi Erick!
>> >>
>> >> Here is my DIH configuration:
>> >>
>> >> <dataConfig>
>> >> <dataSource name="jdbc" driver="org.postgresql.Driver"
>> >>
>> >>
>> url="jdbc:postgresql://${dataimporter.request.dbHost}:${dataimporter.request.dbPort}/${dataimporter.request.dbName}"
>> >> user="${dataimporter.request.dbUser}"
>> >> password="${dataimporter.request.dbPassword}" autoCommit="false"
>> >> transactionIsolation="TRANSACTION_READ_UNCOMMITTED"
>> >> holdability="CLOSE_CURSORS_AT_COMMIT"/>
>> >> <document>
>> >> <entity name='myEntity' dataSource='jdbc' pk='id'
>> >> query=' ..... '
>> >> deltaImportQuery=' ..... '
>> >> deltaQuery=' ..... '
>> >> >
>> >> </entity>
>> >> </document>
>> >> </dataConfig>
>> >>
>> >> I have tried two options for the deltaQuery:
>> >> UPDATE "Global"."Projects" SET "prj_lastSync" = now() WHERE "prj_id" =
>> >> '2'; <---- Throws a null pointer exception as described in the
>> >> previous email
>> >>
>> >> The second option is a DB function that I am calling this way:
>> >> SELECT "get_deltaimport_items" AS "id" FROM
>> >> project.get_deltaimport_items(2, 'project');
>> >>
>> >> The function inside executes the UPDATE query shown above and a SELECT
>> >> query for the ids.
>> >> The ids are returned ok, but the UPDATE has no effect on the database.
>> >>
>> >> Cheers!
>> >> Juan M.
>> >>
>> >>
>> >> On Thu, Dec 30, 2010 at 1:32 AM, Erick Erickson <
>> erickerickson@gmail.com>
>> >> wrote:
>> >> > Well, let's see the queries you're sending, and your DIH
>> configuration.
>> >> >
>> >> > Otherwise, we're just guessing...
>> >> >
>> >> > Best
>> >> > Erick
>> >> >
>> >> > On Wed, Dec 29, 2010 at 9:58 PM, Juan Manuel Alvarez <
>> naicigam@gmail.com
>> >> >wrote:
>> >> >
>> >> >> Hi! I would like to ask you a question about using a deltaQuery in
>> DIH.
>> >> >> I am syncing with a PostgreSQL database.
>> >> >>
>> >> >> At first I was calling a function that made two queries: an UPDATE
>> and a
>> >> >> SELECT.
>> >> >> The select result was properly returned, but the UPDATE query did not
>> >> >> made any changes,
>> >> >> so I tried calling the same function from a PostgreSQL client and
>> >> >> everything went OK.
>> >> >>
>> >> >> So I tried calling a simple UPDATE query directly in the deltaQuery
>> >> >> and I receive a
>> >> >> NullPointerException that I traced to the line 251 of the
>> >> >> JdbcDataSource.java
>> >> >> colNames = readFieldNames(resultSet.getMetaData());
>> >> >>
>> >> >> The question is: is there a way I can make the update query work in
>> >> >> the deltaQuery
>> >> >> or am I doing something wrong?
>> >> >>
>> >> >> Happy new year!!!!
>> >> >> Cheers!
>> >> >> Juan M.
>> >> >>
>> >> >
>> >>
>> >
>>
>
>
>
> --
>
> **
>
> *Travis Low, Director of Development*
>
>
> ** <tl...@4centurion.com>* *
>
> *Centurion Research Solutions, LLC*
>
> *14048 ParkEast Circle *•* Suite 100 *•* Chantilly, VA 20151*
>
> *703-956-6276 *•* 703-378-4474 (fax)*
>
> *http://www.centurionresearch.com* <http://www.centurionresearch.com>
>
> **The information contained in this email message is confidential and
> protected from disclosure. If you are not the intended recipient, any use
> or dissemination of this communication, including attachments, is strictly
> prohibited. If you received this email message in error, please delete it
> and immediately notify the sender.
>
> This email message and any attachments have been scanned and are believed to
> be free of malicious software and defects that might affect any computer
> system in which they are received and opened. No responsibility is accepted
> by Centurion Research Solutions, LLC for any loss or damage arising from the
> content of this email.
>
Re: UPDATE query in deltaquery
Posted by Travis Low <tl...@4centurion.com>.
If you are getting a null pointer exception here:
colNames = readFieldNames(resultSet.getMetaData());
Then that implies the DIH code is written to expect a select statement. You
might be able to fool it with some SQL injection:
update blah set foo=bar where id=1234; select id from blah
But if that doesn't work then you may be out of luck.
cheers,
Travis
On Thu, Dec 30, 2010 at 8:26 AM, Juan Manuel Alvarez <na...@gmail.com>wrote:
> Erick:
>
> Thanks for the quick response.
>
> I can't use the timestamp for doing DIH, so I need to use a custom
> field that I need to update one for each delta-import, so that is why
> I need to execute an UPDATE on the deltaQuery.
>
> Cheers!
> Juan M.
>
> On Thu, Dec 30, 2010 at 10:07 AM, Erick Erickson
> <er...@gmail.com> wrote:
> > WARNING: DIH isn't my strong suit, I generally prefer doing things
> > in SolrJ. Mostly I asked for clarification so someone #else# who
> > actually knows DIH details could chime in...
> >
> > That said, I'm a bit confused. As I understand it, you shouldn't
> > be UPDATEing anything in DIH, it's a select where documents
> > then get added to Solr "by magic". Your post leads me to believe
> > that you're trying to change the database via DIH, is that at
> > all true?
> >
> > This is based in part on
> > "The ids are returned ok, but the UPDATE has no effect on the database"
> > Or do you mean "effect on the index"? If the latter, then the select
> > would only have a chance of updating the IDs of the Solr documents...
> >
> > At least I think that's close to reality...
> >
> > Best
> > Erick
> >
> > On Thu, Dec 30, 2010 at 7:52 AM, Juan Manuel Alvarez <naicigam@gmail.com
> >wrote:
> >
> >> Hi Erick!
> >>
> >> Here is my DIH configuration:
> >>
> >> <dataConfig>
> >> <dataSource name="jdbc" driver="org.postgresql.Driver"
> >>
> >>
> url="jdbc:postgresql://${dataimporter.request.dbHost}:${dataimporter.request.dbPort}/${dataimporter.request.dbName}"
> >> user="${dataimporter.request.dbUser}"
> >> password="${dataimporter.request.dbPassword}" autoCommit="false"
> >> transactionIsolation="TRANSACTION_READ_UNCOMMITTED"
> >> holdability="CLOSE_CURSORS_AT_COMMIT"/>
> >> <document>
> >> <entity name='myEntity' dataSource='jdbc' pk='id'
> >> query=' ..... '
> >> deltaImportQuery=' ..... '
> >> deltaQuery=' ..... '
> >> >
> >> </entity>
> >> </document>
> >> </dataConfig>
> >>
> >> I have tried two options for the deltaQuery:
> >> UPDATE "Global"."Projects" SET "prj_lastSync" = now() WHERE "prj_id" =
> >> '2'; <---- Throws a null pointer exception as described in the
> >> previous email
> >>
> >> The second option is a DB function that I am calling this way:
> >> SELECT "get_deltaimport_items" AS "id" FROM
> >> project.get_deltaimport_items(2, 'project');
> >>
> >> The function inside executes the UPDATE query shown above and a SELECT
> >> query for the ids.
> >> The ids are returned ok, but the UPDATE has no effect on the database.
> >>
> >> Cheers!
> >> Juan M.
> >>
> >>
> >> On Thu, Dec 30, 2010 at 1:32 AM, Erick Erickson <
> erickerickson@gmail.com>
> >> wrote:
> >> > Well, let's see the queries you're sending, and your DIH
> configuration.
> >> >
> >> > Otherwise, we're just guessing...
> >> >
> >> > Best
> >> > Erick
> >> >
> >> > On Wed, Dec 29, 2010 at 9:58 PM, Juan Manuel Alvarez <
> naicigam@gmail.com
> >> >wrote:
> >> >
> >> >> Hi! I would like to ask you a question about using a deltaQuery in
> DIH.
> >> >> I am syncing with a PostgreSQL database.
> >> >>
> >> >> At first I was calling a function that made two queries: an UPDATE
> and a
> >> >> SELECT.
> >> >> The select result was properly returned, but the UPDATE query did not
> >> >> made any changes,
> >> >> so I tried calling the same function from a PostgreSQL client and
> >> >> everything went OK.
> >> >>
> >> >> So I tried calling a simple UPDATE query directly in the deltaQuery
> >> >> and I receive a
> >> >> NullPointerException that I traced to the line 251 of the
> >> >> JdbcDataSource.java
> >> >> colNames = readFieldNames(resultSet.getMetaData());
> >> >>
> >> >> The question is: is there a way I can make the update query work in
> >> >> the deltaQuery
> >> >> or am I doing something wrong?
> >> >>
> >> >> Happy new year!!!!
> >> >> Cheers!
> >> >> Juan M.
> >> >>
> >> >
> >>
> >
>
--
**
*Travis Low, Director of Development*
** <tl...@4centurion.com>* *
*Centurion Research Solutions, LLC*
*14048 ParkEast Circle *•* Suite 100 *•* Chantilly, VA 20151*
*703-956-6276 *•* 703-378-4474 (fax)*
*http://www.centurionresearch.com* <http://www.centurionresearch.com>
**The information contained in this email message is confidential and
protected from disclosure. If you are not the intended recipient, any use
or dissemination of this communication, including attachments, is strictly
prohibited. If you received this email message in error, please delete it
and immediately notify the sender.
This email message and any attachments have been scanned and are believed to
be free of malicious software and defects that might affect any computer
system in which they are received and opened. No responsibility is accepted
by Centurion Research Solutions, LLC for any loss or damage arising from the
content of this email.
Re: UPDATE query in deltaquery
Posted by Juan Manuel Alvarez <na...@gmail.com>.
Erick:
Thanks for the quick response.
I can't use the timestamp for doing DIH, so I need to use a custom
field that I need to update one for each delta-import, so that is why
I need to execute an UPDATE on the deltaQuery.
Cheers!
Juan M.
On Thu, Dec 30, 2010 at 10:07 AM, Erick Erickson
<er...@gmail.com> wrote:
> WARNING: DIH isn't my strong suit, I generally prefer doing things
> in SolrJ. Mostly I asked for clarification so someone #else# who
> actually knows DIH details could chime in...
>
> That said, I'm a bit confused. As I understand it, you shouldn't
> be UPDATEing anything in DIH, it's a select where documents
> then get added to Solr "by magic". Your post leads me to believe
> that you're trying to change the database via DIH, is that at
> all true?
>
> This is based in part on
> "The ids are returned ok, but the UPDATE has no effect on the database"
> Or do you mean "effect on the index"? If the latter, then the select
> would only have a chance of updating the IDs of the Solr documents...
>
> At least I think that's close to reality...
>
> Best
> Erick
>
> On Thu, Dec 30, 2010 at 7:52 AM, Juan Manuel Alvarez <na...@gmail.com>wrote:
>
>> Hi Erick!
>>
>> Here is my DIH configuration:
>>
>> <dataConfig>
>> <dataSource name="jdbc" driver="org.postgresql.Driver"
>>
>> url="jdbc:postgresql://${dataimporter.request.dbHost}:${dataimporter.request.dbPort}/${dataimporter.request.dbName}"
>> user="${dataimporter.request.dbUser}"
>> password="${dataimporter.request.dbPassword}" autoCommit="false"
>> transactionIsolation="TRANSACTION_READ_UNCOMMITTED"
>> holdability="CLOSE_CURSORS_AT_COMMIT"/>
>> <document>
>> <entity name='myEntity' dataSource='jdbc' pk='id'
>> query=' ..... '
>> deltaImportQuery=' ..... '
>> deltaQuery=' ..... '
>> >
>> </entity>
>> </document>
>> </dataConfig>
>>
>> I have tried two options for the deltaQuery:
>> UPDATE "Global"."Projects" SET "prj_lastSync" = now() WHERE "prj_id" =
>> '2'; <---- Throws a null pointer exception as described in the
>> previous email
>>
>> The second option is a DB function that I am calling this way:
>> SELECT "get_deltaimport_items" AS "id" FROM
>> project.get_deltaimport_items(2, 'project');
>>
>> The function inside executes the UPDATE query shown above and a SELECT
>> query for the ids.
>> The ids are returned ok, but the UPDATE has no effect on the database.
>>
>> Cheers!
>> Juan M.
>>
>>
>> On Thu, Dec 30, 2010 at 1:32 AM, Erick Erickson <er...@gmail.com>
>> wrote:
>> > Well, let's see the queries you're sending, and your DIH configuration.
>> >
>> > Otherwise, we're just guessing...
>> >
>> > Best
>> > Erick
>> >
>> > On Wed, Dec 29, 2010 at 9:58 PM, Juan Manuel Alvarez <naicigam@gmail.com
>> >wrote:
>> >
>> >> Hi! I would like to ask you a question about using a deltaQuery in DIH.
>> >> I am syncing with a PostgreSQL database.
>> >>
>> >> At first I was calling a function that made two queries: an UPDATE and a
>> >> SELECT.
>> >> The select result was properly returned, but the UPDATE query did not
>> >> made any changes,
>> >> so I tried calling the same function from a PostgreSQL client and
>> >> everything went OK.
>> >>
>> >> So I tried calling a simple UPDATE query directly in the deltaQuery
>> >> and I receive a
>> >> NullPointerException that I traced to the line 251 of the
>> >> JdbcDataSource.java
>> >> colNames = readFieldNames(resultSet.getMetaData());
>> >>
>> >> The question is: is there a way I can make the update query work in
>> >> the deltaQuery
>> >> or am I doing something wrong?
>> >>
>> >> Happy new year!!!!
>> >> Cheers!
>> >> Juan M.
>> >>
>> >
>>
>
Re: UPDATE query in deltaquery
Posted by Erick Erickson <er...@gmail.com>.
WARNING: DIH isn't my strong suit, I generally prefer doing things
in SolrJ. Mostly I asked for clarification so someone #else# who
actually knows DIH details could chime in...
That said, I'm a bit confused. As I understand it, you shouldn't
be UPDATEing anything in DIH, it's a select where documents
then get added to Solr "by magic". Your post leads me to believe
that you're trying to change the database via DIH, is that at
all true?
This is based in part on
"The ids are returned ok, but the UPDATE has no effect on the database"
Or do you mean "effect on the index"? If the latter, then the select
would only have a chance of updating the IDs of the Solr documents...
At least I think that's close to reality...
Best
Erick
On Thu, Dec 30, 2010 at 7:52 AM, Juan Manuel Alvarez <na...@gmail.com>wrote:
> Hi Erick!
>
> Here is my DIH configuration:
>
> <dataConfig>
> <dataSource name="jdbc" driver="org.postgresql.Driver"
>
> url="jdbc:postgresql://${dataimporter.request.dbHost}:${dataimporter.request.dbPort}/${dataimporter.request.dbName}"
> user="${dataimporter.request.dbUser}"
> password="${dataimporter.request.dbPassword}" autoCommit="false"
> transactionIsolation="TRANSACTION_READ_UNCOMMITTED"
> holdability="CLOSE_CURSORS_AT_COMMIT"/>
> <document>
> <entity name='myEntity' dataSource='jdbc' pk='id'
> query=' ..... '
> deltaImportQuery=' ..... '
> deltaQuery=' ..... '
> >
> </entity>
> </document>
> </dataConfig>
>
> I have tried two options for the deltaQuery:
> UPDATE "Global"."Projects" SET "prj_lastSync" = now() WHERE "prj_id" =
> '2'; <---- Throws a null pointer exception as described in the
> previous email
>
> The second option is a DB function that I am calling this way:
> SELECT "get_deltaimport_items" AS "id" FROM
> project.get_deltaimport_items(2, 'project');
>
> The function inside executes the UPDATE query shown above and a SELECT
> query for the ids.
> The ids are returned ok, but the UPDATE has no effect on the database.
>
> Cheers!
> Juan M.
>
>
> On Thu, Dec 30, 2010 at 1:32 AM, Erick Erickson <er...@gmail.com>
> wrote:
> > Well, let's see the queries you're sending, and your DIH configuration.
> >
> > Otherwise, we're just guessing...
> >
> > Best
> > Erick
> >
> > On Wed, Dec 29, 2010 at 9:58 PM, Juan Manuel Alvarez <naicigam@gmail.com
> >wrote:
> >
> >> Hi! I would like to ask you a question about using a deltaQuery in DIH.
> >> I am syncing with a PostgreSQL database.
> >>
> >> At first I was calling a function that made two queries: an UPDATE and a
> >> SELECT.
> >> The select result was properly returned, but the UPDATE query did not
> >> made any changes,
> >> so I tried calling the same function from a PostgreSQL client and
> >> everything went OK.
> >>
> >> So I tried calling a simple UPDATE query directly in the deltaQuery
> >> and I receive a
> >> NullPointerException that I traced to the line 251 of the
> >> JdbcDataSource.java
> >> colNames = readFieldNames(resultSet.getMetaData());
> >>
> >> The question is: is there a way I can make the update query work in
> >> the deltaQuery
> >> or am I doing something wrong?
> >>
> >> Happy new year!!!!
> >> Cheers!
> >> Juan M.
> >>
> >
>
Re: UPDATE query in deltaquery
Posted by Juan Manuel Alvarez <na...@gmail.com>.
I finally worked it out!
The setting that was giving me trouble was autocommit. I set it to
true and now the UPDATE queries are reflected in the database.
Cheers!
Juan M.
On Thu, Dec 30, 2010 at 3:19 PM, Juan Manuel Alvarez <na...@gmail.com> wrote:
> Hi Ephraim! Thanks again for taking the time to help me. Really appreciated =o)
>
> The UPDATE was before the SELECT, but putting it after leads to the
> same result, with or without the TRANSACTION_SERIALIZABLE.
>
> Cheers!
> Juan M.
>
> 2010/12/30 Ephraim Ofir <Ep...@icq.com>:
>> Does your function get_deltaimport_items perform the update first and then the select? Does it make a difference if you change the order? Did you try omitting the TRANSACTION_SERIALIZABLE part?
>>
>> Ephraim Ofir
>>
>> -----Original Message-----
>> From: Juan Manuel Alvarez [mailto:naicigam@gmail.com]
>> Sent: Thursday, December 30, 2010 7:04 PM
>> To: solr-user@lucene.apache.org
>> Subject: Re: UPDATE query in deltaquery
>>
>> Hi Ephraim! Thanks for the answer!
>>
>> Actually the user has permissions to make UPDATE queries.
>>
>> I changed the configuration to
>> <dataSource name="jdbc" driver="org.postgresql.Driver"
>> url="jdbc:postgresql://${dataimporter.request.dbHost}:${dataimporter.request.dbPort}/${dataimporter.request.dbName}"
>> user="${dataimporter.request.dbUser}"
>> password="${dataimporter.request.dbPassword}"
>> transactionIsolation="TRANSACTION_SERIALIZABLE"
>> readOnly="false"/>
>>
>> and I still get the same results.
>>
>> Cheers!
>> Juan M.
>>
>> On Thu, Dec 30, 2010 at 12:40 PM, Ephraim Ofir <Ep...@icq.com> wrote:
>>> This may sound silly, but are you sure the user you're using has
>>> permissions to do the updates you want? Not sure about postgres but I
>>> think some jdbc's require that the connection be defined as rw, maybe
>>> you should try adding readOnly="false" to your jdbc definition.
>>>
>>> Ephraim Ofir
>>>
>>> -----Original Message-----
>>> From: Juan Manuel Alvarez [mailto:naicigam@gmail.com]
>>> Sent: Thursday, December 30, 2010 2:52 PM
>>> To: solr-user@lucene.apache.org
>>> Subject: Re: UPDATE query in deltaquery
>>>
>>> Hi Erick!
>>>
>>> Here is my DIH configuration:
>>>
>>> <dataConfig>
>>> <dataSource name="jdbc" driver="org.postgresql.Driver"
>>>
>>> url="jdbc:postgresql://${dataimporter.request.dbHost}:${dataimporter.req
>>> uest.dbPort}/${dataimporter.request.dbName}"
>>> user="${dataimporter.request.dbUser}"
>>> password="${dataimporter.request.dbPassword}" autoCommit="false"
>>> transactionIsolation="TRANSACTION_READ_UNCOMMITTED"
>>> holdability="CLOSE_CURSORS_AT_COMMIT"/>
>>> <document>
>>> <entity name='myEntity' dataSource='jdbc' pk='id'
>>> query=' ..... '
>>> deltaImportQuery=' ..... '
>>> deltaQuery=' ..... '
>>> >
>>> </entity>
>>> </document>
>>> </dataConfig>
>>>
>>> I have tried two options for the deltaQuery:
>>> UPDATE "Global"."Projects" SET "prj_lastSync" = now() WHERE "prj_id" =
>>> '2'; <---- Throws a null pointer exception as described in the
>>> previous email
>>>
>>> The second option is a DB function that I am calling this way:
>>> SELECT "get_deltaimport_items" AS "id" FROM
>>> project.get_deltaimport_items(2, 'project');
>>>
>>> The function inside executes the UPDATE query shown above and a SELECT
>>> query for the ids.
>>> The ids are returned ok, but the UPDATE has no effect on the database.
>>>
>>> Cheers!
>>> Juan M.
>>>
>>>
>>> On Thu, Dec 30, 2010 at 1:32 AM, Erick Erickson
>>> <er...@gmail.com> wrote:
>>>> Well, let's see the queries you're sending, and your DIH
>>> configuration.
>>>>
>>>> Otherwise, we're just guessing...
>>>>
>>>> Best
>>>> Erick
>>>>
>>>> On Wed, Dec 29, 2010 at 9:58 PM, Juan Manuel Alvarez
>>> <na...@gmail.com>wrote:
>>>>
>>>>> Hi! I would like to ask you a question about using a deltaQuery in
>>> DIH.
>>>>> I am syncing with a PostgreSQL database.
>>>>>
>>>>> At first I was calling a function that made two queries: an UPDATE
>>> and a
>>>>> SELECT.
>>>>> The select result was properly returned, but the UPDATE query did not
>>>>> made any changes,
>>>>> so I tried calling the same function from a PostgreSQL client and
>>>>> everything went OK.
>>>>>
>>>>> So I tried calling a simple UPDATE query directly in the deltaQuery
>>>>> and I receive a
>>>>> NullPointerException that I traced to the line 251 of the
>>>>> JdbcDataSource.java
>>>>> colNames = readFieldNames(resultSet.getMetaData());
>>>>>
>>>>> The question is: is there a way I can make the update query work in
>>>>> the deltaQuery
>>>>> or am I doing something wrong?
>>>>>
>>>>> Happy new year!!!!
>>>>> Cheers!
>>>>> Juan M.
>>>>>
>>>>
>>>
>>
>
Re: UPDATE query in deltaquery
Posted by Juan Manuel Alvarez <na...@gmail.com>.
Hi Ephraim! Thanks again for taking the time to help me. Really appreciated =o)
The UPDATE was before the SELECT, but putting it after leads to the
same result, with or without the TRANSACTION_SERIALIZABLE.
Cheers!
Juan M.
2010/12/30 Ephraim Ofir <Ep...@icq.com>:
> Does your function get_deltaimport_items perform the update first and then the select? Does it make a difference if you change the order? Did you try omitting the TRANSACTION_SERIALIZABLE part?
>
> Ephraim Ofir
>
> -----Original Message-----
> From: Juan Manuel Alvarez [mailto:naicigam@gmail.com]
> Sent: Thursday, December 30, 2010 7:04 PM
> To: solr-user@lucene.apache.org
> Subject: Re: UPDATE query in deltaquery
>
> Hi Ephraim! Thanks for the answer!
>
> Actually the user has permissions to make UPDATE queries.
>
> I changed the configuration to
> <dataSource name="jdbc" driver="org.postgresql.Driver"
> url="jdbc:postgresql://${dataimporter.request.dbHost}:${dataimporter.request.dbPort}/${dataimporter.request.dbName}"
> user="${dataimporter.request.dbUser}"
> password="${dataimporter.request.dbPassword}"
> transactionIsolation="TRANSACTION_SERIALIZABLE"
> readOnly="false"/>
>
> and I still get the same results.
>
> Cheers!
> Juan M.
>
> On Thu, Dec 30, 2010 at 12:40 PM, Ephraim Ofir <Ep...@icq.com> wrote:
>> This may sound silly, but are you sure the user you're using has
>> permissions to do the updates you want? Not sure about postgres but I
>> think some jdbc's require that the connection be defined as rw, maybe
>> you should try adding readOnly="false" to your jdbc definition.
>>
>> Ephraim Ofir
>>
>> -----Original Message-----
>> From: Juan Manuel Alvarez [mailto:naicigam@gmail.com]
>> Sent: Thursday, December 30, 2010 2:52 PM
>> To: solr-user@lucene.apache.org
>> Subject: Re: UPDATE query in deltaquery
>>
>> Hi Erick!
>>
>> Here is my DIH configuration:
>>
>> <dataConfig>
>> <dataSource name="jdbc" driver="org.postgresql.Driver"
>>
>> url="jdbc:postgresql://${dataimporter.request.dbHost}:${dataimporter.req
>> uest.dbPort}/${dataimporter.request.dbName}"
>> user="${dataimporter.request.dbUser}"
>> password="${dataimporter.request.dbPassword}" autoCommit="false"
>> transactionIsolation="TRANSACTION_READ_UNCOMMITTED"
>> holdability="CLOSE_CURSORS_AT_COMMIT"/>
>> <document>
>> <entity name='myEntity' dataSource='jdbc' pk='id'
>> query=' ..... '
>> deltaImportQuery=' ..... '
>> deltaQuery=' ..... '
>> >
>> </entity>
>> </document>
>> </dataConfig>
>>
>> I have tried two options for the deltaQuery:
>> UPDATE "Global"."Projects" SET "prj_lastSync" = now() WHERE "prj_id" =
>> '2'; <---- Throws a null pointer exception as described in the
>> previous email
>>
>> The second option is a DB function that I am calling this way:
>> SELECT "get_deltaimport_items" AS "id" FROM
>> project.get_deltaimport_items(2, 'project');
>>
>> The function inside executes the UPDATE query shown above and a SELECT
>> query for the ids.
>> The ids are returned ok, but the UPDATE has no effect on the database.
>>
>> Cheers!
>> Juan M.
>>
>>
>> On Thu, Dec 30, 2010 at 1:32 AM, Erick Erickson
>> <er...@gmail.com> wrote:
>>> Well, let's see the queries you're sending, and your DIH
>> configuration.
>>>
>>> Otherwise, we're just guessing...
>>>
>>> Best
>>> Erick
>>>
>>> On Wed, Dec 29, 2010 at 9:58 PM, Juan Manuel Alvarez
>> <na...@gmail.com>wrote:
>>>
>>>> Hi! I would like to ask you a question about using a deltaQuery in
>> DIH.
>>>> I am syncing with a PostgreSQL database.
>>>>
>>>> At first I was calling a function that made two queries: an UPDATE
>> and a
>>>> SELECT.
>>>> The select result was properly returned, but the UPDATE query did not
>>>> made any changes,
>>>> so I tried calling the same function from a PostgreSQL client and
>>>> everything went OK.
>>>>
>>>> So I tried calling a simple UPDATE query directly in the deltaQuery
>>>> and I receive a
>>>> NullPointerException that I traced to the line 251 of the
>>>> JdbcDataSource.java
>>>> colNames = readFieldNames(resultSet.getMetaData());
>>>>
>>>> The question is: is there a way I can make the update query work in
>>>> the deltaQuery
>>>> or am I doing something wrong?
>>>>
>>>> Happy new year!!!!
>>>> Cheers!
>>>> Juan M.
>>>>
>>>
>>
>
RE: UPDATE query in deltaquery
Posted by Ephraim Ofir <Ep...@icq.com>.
Does your function get_deltaimport_items perform the update first and then the select? Does it make a difference if you change the order? Did you try omitting the TRANSACTION_SERIALIZABLE part?
Ephraim Ofir
-----Original Message-----
From: Juan Manuel Alvarez [mailto:naicigam@gmail.com]
Sent: Thursday, December 30, 2010 7:04 PM
To: solr-user@lucene.apache.org
Subject: Re: UPDATE query in deltaquery
Hi Ephraim! Thanks for the answer!
Actually the user has permissions to make UPDATE queries.
I changed the configuration to
<dataSource name="jdbc" driver="org.postgresql.Driver"
url="jdbc:postgresql://${dataimporter.request.dbHost}:${dataimporter.request.dbPort}/${dataimporter.request.dbName}"
user="${dataimporter.request.dbUser}"
password="${dataimporter.request.dbPassword}"
transactionIsolation="TRANSACTION_SERIALIZABLE"
readOnly="false"/>
and I still get the same results.
Cheers!
Juan M.
On Thu, Dec 30, 2010 at 12:40 PM, Ephraim Ofir <Ep...@icq.com> wrote:
> This may sound silly, but are you sure the user you're using has
> permissions to do the updates you want? Not sure about postgres but I
> think some jdbc's require that the connection be defined as rw, maybe
> you should try adding readOnly="false" to your jdbc definition.
>
> Ephraim Ofir
>
> -----Original Message-----
> From: Juan Manuel Alvarez [mailto:naicigam@gmail.com]
> Sent: Thursday, December 30, 2010 2:52 PM
> To: solr-user@lucene.apache.org
> Subject: Re: UPDATE query in deltaquery
>
> Hi Erick!
>
> Here is my DIH configuration:
>
> <dataConfig>
> <dataSource name="jdbc" driver="org.postgresql.Driver"
>
> url="jdbc:postgresql://${dataimporter.request.dbHost}:${dataimporter.req
> uest.dbPort}/${dataimporter.request.dbName}"
> user="${dataimporter.request.dbUser}"
> password="${dataimporter.request.dbPassword}" autoCommit="false"
> transactionIsolation="TRANSACTION_READ_UNCOMMITTED"
> holdability="CLOSE_CURSORS_AT_COMMIT"/>
> <document>
> <entity name='myEntity' dataSource='jdbc' pk='id'
> query=' ..... '
> deltaImportQuery=' ..... '
> deltaQuery=' ..... '
> >
> </entity>
> </document>
> </dataConfig>
>
> I have tried two options for the deltaQuery:
> UPDATE "Global"."Projects" SET "prj_lastSync" = now() WHERE "prj_id" =
> '2'; <---- Throws a null pointer exception as described in the
> previous email
>
> The second option is a DB function that I am calling this way:
> SELECT "get_deltaimport_items" AS "id" FROM
> project.get_deltaimport_items(2, 'project');
>
> The function inside executes the UPDATE query shown above and a SELECT
> query for the ids.
> The ids are returned ok, but the UPDATE has no effect on the database.
>
> Cheers!
> Juan M.
>
>
> On Thu, Dec 30, 2010 at 1:32 AM, Erick Erickson
> <er...@gmail.com> wrote:
>> Well, let's see the queries you're sending, and your DIH
> configuration.
>>
>> Otherwise, we're just guessing...
>>
>> Best
>> Erick
>>
>> On Wed, Dec 29, 2010 at 9:58 PM, Juan Manuel Alvarez
> <na...@gmail.com>wrote:
>>
>>> Hi! I would like to ask you a question about using a deltaQuery in
> DIH.
>>> I am syncing with a PostgreSQL database.
>>>
>>> At first I was calling a function that made two queries: an UPDATE
> and a
>>> SELECT.
>>> The select result was properly returned, but the UPDATE query did not
>>> made any changes,
>>> so I tried calling the same function from a PostgreSQL client and
>>> everything went OK.
>>>
>>> So I tried calling a simple UPDATE query directly in the deltaQuery
>>> and I receive a
>>> NullPointerException that I traced to the line 251 of the
>>> JdbcDataSource.java
>>> colNames = readFieldNames(resultSet.getMetaData());
>>>
>>> The question is: is there a way I can make the update query work in
>>> the deltaQuery
>>> or am I doing something wrong?
>>>
>>> Happy new year!!!!
>>> Cheers!
>>> Juan M.
>>>
>>
>
Re: UPDATE query in deltaquery
Posted by Juan Manuel Alvarez <na...@gmail.com>.
Hi Ephraim! Thanks for the answer!
Actually the user has permissions to make UPDATE queries.
I changed the configuration to
<dataSource name="jdbc" driver="org.postgresql.Driver"
url="jdbc:postgresql://${dataimporter.request.dbHost}:${dataimporter.request.dbPort}/${dataimporter.request.dbName}"
user="${dataimporter.request.dbUser}"
password="${dataimporter.request.dbPassword}"
transactionIsolation="TRANSACTION_SERIALIZABLE"
readOnly="false"/>
and I still get the same results.
Cheers!
Juan M.
On Thu, Dec 30, 2010 at 12:40 PM, Ephraim Ofir <Ep...@icq.com> wrote:
> This may sound silly, but are you sure the user you're using has
> permissions to do the updates you want? Not sure about postgres but I
> think some jdbc's require that the connection be defined as rw, maybe
> you should try adding readOnly="false" to your jdbc definition.
>
> Ephraim Ofir
>
> -----Original Message-----
> From: Juan Manuel Alvarez [mailto:naicigam@gmail.com]
> Sent: Thursday, December 30, 2010 2:52 PM
> To: solr-user@lucene.apache.org
> Subject: Re: UPDATE query in deltaquery
>
> Hi Erick!
>
> Here is my DIH configuration:
>
> <dataConfig>
> <dataSource name="jdbc" driver="org.postgresql.Driver"
>
> url="jdbc:postgresql://${dataimporter.request.dbHost}:${dataimporter.req
> uest.dbPort}/${dataimporter.request.dbName}"
> user="${dataimporter.request.dbUser}"
> password="${dataimporter.request.dbPassword}" autoCommit="false"
> transactionIsolation="TRANSACTION_READ_UNCOMMITTED"
> holdability="CLOSE_CURSORS_AT_COMMIT"/>
> <document>
> <entity name='myEntity' dataSource='jdbc' pk='id'
> query=' ..... '
> deltaImportQuery=' ..... '
> deltaQuery=' ..... '
> >
> </entity>
> </document>
> </dataConfig>
>
> I have tried two options for the deltaQuery:
> UPDATE "Global"."Projects" SET "prj_lastSync" = now() WHERE "prj_id" =
> '2'; <---- Throws a null pointer exception as described in the
> previous email
>
> The second option is a DB function that I am calling this way:
> SELECT "get_deltaimport_items" AS "id" FROM
> project.get_deltaimport_items(2, 'project');
>
> The function inside executes the UPDATE query shown above and a SELECT
> query for the ids.
> The ids are returned ok, but the UPDATE has no effect on the database.
>
> Cheers!
> Juan M.
>
>
> On Thu, Dec 30, 2010 at 1:32 AM, Erick Erickson
> <er...@gmail.com> wrote:
>> Well, let's see the queries you're sending, and your DIH
> configuration.
>>
>> Otherwise, we're just guessing...
>>
>> Best
>> Erick
>>
>> On Wed, Dec 29, 2010 at 9:58 PM, Juan Manuel Alvarez
> <na...@gmail.com>wrote:
>>
>>> Hi! I would like to ask you a question about using a deltaQuery in
> DIH.
>>> I am syncing with a PostgreSQL database.
>>>
>>> At first I was calling a function that made two queries: an UPDATE
> and a
>>> SELECT.
>>> The select result was properly returned, but the UPDATE query did not
>>> made any changes,
>>> so I tried calling the same function from a PostgreSQL client and
>>> everything went OK.
>>>
>>> So I tried calling a simple UPDATE query directly in the deltaQuery
>>> and I receive a
>>> NullPointerException that I traced to the line 251 of the
>>> JdbcDataSource.java
>>> colNames = readFieldNames(resultSet.getMetaData());
>>>
>>> The question is: is there a way I can make the update query work in
>>> the deltaQuery
>>> or am I doing something wrong?
>>>
>>> Happy new year!!!!
>>> Cheers!
>>> Juan M.
>>>
>>
>
RE: UPDATE query in deltaquery
Posted by Ephraim Ofir <Ep...@icq.com>.
This may sound silly, but are you sure the user you're using has
permissions to do the updates you want? Not sure about postgres but I
think some jdbc's require that the connection be defined as rw, maybe
you should try adding readOnly="false" to your jdbc definition.
Ephraim Ofir
-----Original Message-----
From: Juan Manuel Alvarez [mailto:naicigam@gmail.com]
Sent: Thursday, December 30, 2010 2:52 PM
To: solr-user@lucene.apache.org
Subject: Re: UPDATE query in deltaquery
Hi Erick!
Here is my DIH configuration:
<dataConfig>
<dataSource name="jdbc" driver="org.postgresql.Driver"
url="jdbc:postgresql://${dataimporter.request.dbHost}:${dataimporter.req
uest.dbPort}/${dataimporter.request.dbName}"
user="${dataimporter.request.dbUser}"
password="${dataimporter.request.dbPassword}" autoCommit="false"
transactionIsolation="TRANSACTION_READ_UNCOMMITTED"
holdability="CLOSE_CURSORS_AT_COMMIT"/>
<document>
<entity name='myEntity' dataSource='jdbc' pk='id'
query=' ..... '
deltaImportQuery=' ..... '
deltaQuery=' ..... '
>
</entity>
</document>
</dataConfig>
I have tried two options for the deltaQuery:
UPDATE "Global"."Projects" SET "prj_lastSync" = now() WHERE "prj_id" =
'2'; <---- Throws a null pointer exception as described in the
previous email
The second option is a DB function that I am calling this way:
SELECT "get_deltaimport_items" AS "id" FROM
project.get_deltaimport_items(2, 'project');
The function inside executes the UPDATE query shown above and a SELECT
query for the ids.
The ids are returned ok, but the UPDATE has no effect on the database.
Cheers!
Juan M.
On Thu, Dec 30, 2010 at 1:32 AM, Erick Erickson
<er...@gmail.com> wrote:
> Well, let's see the queries you're sending, and your DIH
configuration.
>
> Otherwise, we're just guessing...
>
> Best
> Erick
>
> On Wed, Dec 29, 2010 at 9:58 PM, Juan Manuel Alvarez
<na...@gmail.com>wrote:
>
>> Hi! I would like to ask you a question about using a deltaQuery in
DIH.
>> I am syncing with a PostgreSQL database.
>>
>> At first I was calling a function that made two queries: an UPDATE
and a
>> SELECT.
>> The select result was properly returned, but the UPDATE query did not
>> made any changes,
>> so I tried calling the same function from a PostgreSQL client and
>> everything went OK.
>>
>> So I tried calling a simple UPDATE query directly in the deltaQuery
>> and I receive a
>> NullPointerException that I traced to the line 251 of the
>> JdbcDataSource.java
>> colNames = readFieldNames(resultSet.getMetaData());
>>
>> The question is: is there a way I can make the update query work in
>> the deltaQuery
>> or am I doing something wrong?
>>
>> Happy new year!!!!
>> Cheers!
>> Juan M.
>>
>
Re: UPDATE query in deltaquery
Posted by Juan Manuel Alvarez <na...@gmail.com>.
Hi Erick!
Here is my DIH configuration:
<dataConfig>
<dataSource name="jdbc" driver="org.postgresql.Driver"
url="jdbc:postgresql://${dataimporter.request.dbHost}:${dataimporter.request.dbPort}/${dataimporter.request.dbName}"
user="${dataimporter.request.dbUser}"
password="${dataimporter.request.dbPassword}" autoCommit="false"
transactionIsolation="TRANSACTION_READ_UNCOMMITTED"
holdability="CLOSE_CURSORS_AT_COMMIT"/>
<document>
<entity name='myEntity' dataSource='jdbc' pk='id'
query=' ..... '
deltaImportQuery=' ..... '
deltaQuery=' ..... '
>
</entity>
</document>
</dataConfig>
I have tried two options for the deltaQuery:
UPDATE "Global"."Projects" SET "prj_lastSync" = now() WHERE "prj_id" =
'2'; <---- Throws a null pointer exception as described in the
previous email
The second option is a DB function that I am calling this way:
SELECT "get_deltaimport_items" AS "id" FROM
project.get_deltaimport_items(2, 'project');
The function inside executes the UPDATE query shown above and a SELECT
query for the ids.
The ids are returned ok, but the UPDATE has no effect on the database.
Cheers!
Juan M.
On Thu, Dec 30, 2010 at 1:32 AM, Erick Erickson <er...@gmail.com> wrote:
> Well, let's see the queries you're sending, and your DIH configuration.
>
> Otherwise, we're just guessing...
>
> Best
> Erick
>
> On Wed, Dec 29, 2010 at 9:58 PM, Juan Manuel Alvarez <na...@gmail.com>wrote:
>
>> Hi! I would like to ask you a question about using a deltaQuery in DIH.
>> I am syncing with a PostgreSQL database.
>>
>> At first I was calling a function that made two queries: an UPDATE and a
>> SELECT.
>> The select result was properly returned, but the UPDATE query did not
>> made any changes,
>> so I tried calling the same function from a PostgreSQL client and
>> everything went OK.
>>
>> So I tried calling a simple UPDATE query directly in the deltaQuery
>> and I receive a
>> NullPointerException that I traced to the line 251 of the
>> JdbcDataSource.java
>> colNames = readFieldNames(resultSet.getMetaData());
>>
>> The question is: is there a way I can make the update query work in
>> the deltaQuery
>> or am I doing something wrong?
>>
>> Happy new year!!!!
>> Cheers!
>> Juan M.
>>
>
Re: UPDATE query in deltaquery
Posted by Erick Erickson <er...@gmail.com>.
Well, let's see the queries you're sending, and your DIH configuration.
Otherwise, we're just guessing...
Best
Erick
On Wed, Dec 29, 2010 at 9:58 PM, Juan Manuel Alvarez <na...@gmail.com>wrote:
> Hi! I would like to ask you a question about using a deltaQuery in DIH.
> I am syncing with a PostgreSQL database.
>
> At first I was calling a function that made two queries: an UPDATE and a
> SELECT.
> The select result was properly returned, but the UPDATE query did not
> made any changes,
> so I tried calling the same function from a PostgreSQL client and
> everything went OK.
>
> So I tried calling a simple UPDATE query directly in the deltaQuery
> and I receive a
> NullPointerException that I traced to the line 251 of the
> JdbcDataSource.java
> colNames = readFieldNames(resultSet.getMetaData());
>
> The question is: is there a way I can make the update query work in
> the deltaQuery
> or am I doing something wrong?
>
> Happy new year!!!!
> Cheers!
> Juan M.
>