Re: UPDATE query in deltaquery

[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, &#39;project&#39;);
>>>
>>> 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.
>>>>>
>>>>
>>>
>>
>