|
|
|
|
HI ,I am using MyBatis 3.3.0 with Postgresql 12. I want to inject some pl/pgsql in my xml mapperfor example: to insert data with for loop<update id="generateNumbers" parameterType="tn.tt.nbms.dto.RangeDTO" statementType="CALLABLE"> <![CDATA[
declare
.....
begin
for number in ......
insert into.....(....) values (...)
end loop;
end;
]]></update>with oracle and Pl/sql it works finebut I can't convert it to pl/pgsqlHow can I do itI know I can use stored procedure but my question is how to inject pl/pgsql in xml mapper files if it is possible?Best regards,
--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/2be8db7e-14ce-480b-832d-fba2f9828996%40googlegroups.com.
|
|
|
PostgreSQL also has a FOR loop. See an example here:
You can put that loop into an anonymous code block inside your XML mapper.
On 1/29/2020 4:17:26 AM, "'Yosra TR' via mybatis-user" < [hidden email]> wrote:
HI ,I am using MyBatis 3.3.0 with Postgresql 12. I want to inject some pl/pgsql in my xml mapperfor example: to insert data with for loop<update id="generateNumbers" parameterType="tn.tt.nbms.dto.RangeDTO" statementType="CALLABLE"> <![CDATA[
declare
.....
begin
for number in ......
insert into.....(....) values (...)
end loop;
end;
]]></update>with oracle and Pl/sql it works finebut I can't convert it to pl/pgsqlHow can I do itI know I can use stored procedure but my question is how to inject pl/pgsql in xml mapper files if it is possible?Best regards,
--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/2be8db7e-14ce-480b-832d-fba2f9828996%40googlegroups.com.
--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/emf29bb680-807c-4b77-a483-a7f51fa93472%40asus.
|
|
|
Thank you for your response To test anonymous code block
I tried to delete a row from the table RANGE :
<update id="deleteRange" parameterType="tn.tt.nbms.dto.RangeDTO" statementType="CALLABLE"> <![CDATA[
DO $$ DECLARE BEGIN
delete from RANGE where ID_RANGE = #{idRange};
END $$;
</update>
it throws an exception :
org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.type.TypeException: Could not set parameters for mapping: ParameterMapping{property='idRange', mode=IN, javaType=class java.math.BigDecimal, jdbcType=null, numericScale=null, resultMapId='null', jdbcTypeName='null', expression='null'}. Cause: org.apache.ibatis.type.TypeException: Error setting non null for parameter #1 with JdbcType null . Try setting a different JdbcType for this parameter or a different configuration property. Cause: org.postgresql.util.PSQLException: L'indice de la colonne est hors limite : 1, nombre de colonnes : 0.
but if I replace
#{idRange} by a value like 3381 for example it works
so how I can set parameters ? can you help me ?
Le jeu. 30 janv. 2020 à 05:06, Guy Rouillier < [hidden email]> a écrit : PostgreSQL also has a FOR loop. See an example here:
You can put that loop into an anonymous code block inside your XML mapper.
On 1/29/2020 4:17:26 AM, "'Yosra TR' via mybatis-user" < [hidden email]> wrote:
HI ,I am using MyBatis 3.3.0 with Postgresql 12. I want to inject some pl/pgsql in my xml mapperfor example: to insert data with for loop<update id="generateNumbers" parameterType="tn.tt.nbms.dto.RangeDTO" statementType="CALLABLE"> <![CDATA[
declare
.....
begin
for number in ......
insert into.....(....) values (...)
end loop;
end;
]]></update>with oracle and Pl/sql it works finebut I can't convert it to pl/pgsqlHow can I do itI know I can use stored procedure but my question is how to inject pl/pgsql in xml mapper files if it is possible?Best regards,
--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/2be8db7e-14ce-480b-832d-fba2f9828996%40googlegroups.com.
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/emf29bb680-807c-4b77-a483-a7f51fa93472%40asus.
--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/CAAjYA1Ng10bYkhhgXy%2BYBxhdVJghqfmrR0ocaqcxDqS_y-zJoQ%40mail.gmail.com.
|
|
|
Hello,
The doc says:
> The code block is treated as though it were the body of a function with no parameters
Which means that (unlike Oracle) you cannot pass parameters to the code block.
Executing multiple statements with a single execute() call is not portable and usually comes with limitation.
I would recommend using batch operation [1][2]. It is a better solution especially when dealing with many rows
On Fri, Jan 31, 2020 at 12:50 AM 'Yosra TRABELSI' via mybatis-user < [hidden email]> wrote: Thank you for your response To test anonymous code block
I tried to delete a row from the table RANGE :
<update id="deleteRange" parameterType="tn.tt.nbms.dto.RangeDTO" statementType="CALLABLE"> <![CDATA[
DO $$ DECLARE BEGIN
delete from RANGE where ID_RANGE = #{idRange};
END $$;
</update>
it throws an exception :
org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.type.TypeException: Could not set parameters for mapping: ParameterMapping{property='idRange', mode=IN, javaType=class java.math.BigDecimal, jdbcType=null, numericScale=null, resultMapId='null', jdbcTypeName='null', expression='null'}. Cause: org.apache.ibatis.type.TypeException: Error setting non null for parameter #1 with JdbcType null . Try setting a different JdbcType for this parameter or a different configuration property. Cause: org.postgresql.util.PSQLException: L'indice de la colonne est hors limite : 1, nombre de colonnes : 0.
but if I replace
#{idRange} by a value like 3381 for example it works
so how I can set parameters ? can you help me ?
Le jeu. 30 janv. 2020 à 05:06, Guy Rouillier < [hidden email]> a écrit : PostgreSQL also has a FOR loop. See an example here:
You can put that loop into an anonymous code block inside your XML mapper.
On 1/29/2020 4:17:26 AM, "'Yosra TR' via mybatis-user" < [hidden email]> wrote:
HI ,I am using MyBatis 3.3.0 with Postgresql 12. I want to inject some pl/pgsql in my xml mapperfor example: to insert data with for loop<update id="generateNumbers" parameterType="tn.tt.nbms.dto.RangeDTO" statementType="CALLABLE"> <![CDATA[
declare
.....
begin
for number in ......
insert into.....(....) values (...)
end loop;
end;
]]></update>with oracle and Pl/sql it works finebut I can't convert it to pl/pgsqlHow can I do itI know I can use stored procedure but my question is how to inject pl/pgsql in xml mapper files if it is possible?Best regards,
--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/2be8db7e-14ce-480b-832d-fba2f9828996%40googlegroups.com.
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/emf29bb680-807c-4b77-a483-a7f51fa93472%40asus.
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/CAAjYA1Ng10bYkhhgXy%2BYBxhdVJghqfmrR0ocaqcxDqS_y-zJoQ%40mail.gmail.com.
--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/CA%2Buep2S35b3AAM2vEHoBSd-V_3rrp0abGMeaUtXNqx4dLHgsSg%40mail.gmail.com.
|
|
|
thank you very much So what is more performant stored procedure or batch operation ?
Hello,
The doc says:
> The code block is treated as though it were the body of a function with no parameters
Which means that (unlike Oracle) you cannot pass parameters to the code block.
Executing multiple statements with a single execute() call is not portable and usually comes with limitation.
I would recommend using batch operation [1][2]. It is a better solution especially when dealing with many rows
On Fri, Jan 31, 2020 at 12:50 AM 'Yosra TRABELSI' via mybatis-user < [hidden email]> wrote: Thank you for your response To test anonymous code block
I tried to delete a row from the table RANGE :
<update id="deleteRange" parameterType="tn.tt.nbms.dto.RangeDTO" statementType="CALLABLE"> <![CDATA[
DO $$ DECLARE BEGIN
delete from RANGE where ID_RANGE = #{idRange};
END $$;
</update>
it throws an exception :
org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.type.TypeException: Could not set parameters for mapping: ParameterMapping{property='idRange', mode=IN, javaType=class java.math.BigDecimal, jdbcType=null, numericScale=null, resultMapId='null', jdbcTypeName='null', expression='null'}. Cause: org.apache.ibatis.type.TypeException: Error setting non null for parameter #1 with JdbcType null . Try setting a different JdbcType for this parameter or a different configuration property. Cause: org.postgresql.util.PSQLException: L'indice de la colonne est hors limite : 1, nombre de colonnes : 0.
but if I replace
#{idRange} by a value like 3381 for example it works
so how I can set parameters ? can you help me ?
Le jeu. 30 janv. 2020 à 05:06, Guy Rouillier < [hidden email]> a écrit : PostgreSQL also has a FOR loop. See an example here:
You can put that loop into an anonymous code block inside your XML mapper.
On 1/29/2020 4:17:26 AM, "'Yosra TR' via mybatis-user" < [hidden email]> wrote:
HI ,I am using MyBatis 3.3.0 with Postgresql 12. I want to inject some pl/pgsql in my xml mapperfor example: to insert data with for loop<update id="generateNumbers" parameterType="tn.tt.nbms.dto.RangeDTO" statementType="CALLABLE"> <![CDATA[
declare
.....
begin
for number in ......
insert into.....(....) values (...)
end loop;
end;
]]></update>with oracle and Pl/sql it works finebut I can't convert it to pl/pgsqlHow can I do itI know I can use stored procedure but my question is how to inject pl/pgsql in xml mapper files if it is possible?Best regards,
--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/2be8db7e-14ce-480b-832d-fba2f9828996%40googlegroups.com.
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/emf29bb680-807c-4b77-a483-a7f51fa93472%40asus.
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/CAAjYA1Ng10bYkhhgXy%2BYBxhdVJghqfmrR0ocaqcxDqS_y-zJoQ%40mail.gmail.com.
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/CA%2Buep2S35b3AAM2vEHoBSd-V_3rrp0abGMeaUtXNqx4dLHgsSg%40mail.gmail.com.
--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/CAAjYA1NULd-A9tkFSXtDYjjd-5tA4tOZDgVv5F8Ou1NZxgbYtw%40mail.gmail.com.
|
|
|
In general, stored procedure performs better, I think, but you should compare both with your actual data if the performance is your primary concern. I On Fri, Jan 31, 2020 at 2:29 AM 'Yosra TRABELSI' via mybatis-user < [hidden email]> wrote: thank you very much So what is more performant stored procedure or batch operation ?
Hello,
The doc says:
> The code block is treated as though it were the body of a function with no parameters
Which means that (unlike Oracle) you cannot pass parameters to the code block.
Executing multiple statements with a single execute() call is not portable and usually comes with limitation.
I would recommend using batch operation [1][2]. It is a better solution especially when dealing with many rows
On Fri, Jan 31, 2020 at 12:50 AM 'Yosra TRABELSI' via mybatis-user < [hidden email]> wrote: Thank you for your response To test anonymous code block
I tried to delete a row from the table RANGE :
<update id="deleteRange" parameterType="tn.tt.nbms.dto.RangeDTO" statementType="CALLABLE"> <![CDATA[
DO $$ DECLARE BEGIN
delete from RANGE where ID_RANGE = #{idRange};
END $$;
</update>
it throws an exception :
org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.type.TypeException: Could not set parameters for mapping: ParameterMapping{property='idRange', mode=IN, javaType=class java.math.BigDecimal, jdbcType=null, numericScale=null, resultMapId='null', jdbcTypeName='null', expression='null'}. Cause: org.apache.ibatis.type.TypeException: Error setting non null for parameter #1 with JdbcType null . Try setting a different JdbcType for this parameter or a different configuration property. Cause: org.postgresql.util.PSQLException: L'indice de la colonne est hors limite : 1, nombre de colonnes : 0.
but if I replace
#{idRange} by a value like 3381 for example it works
so how I can set parameters ? can you help me ?
Le jeu. 30 janv. 2020 à 05:06, Guy Rouillier < [hidden email]> a écrit : PostgreSQL also has a FOR loop. See an example here:
You can put that loop into an anonymous code block inside your XML mapper.
On 1/29/2020 4:17:26 AM, "'Yosra TR' via mybatis-user" < [hidden email]> wrote:
HI ,I am using MyBatis 3.3.0 with Postgresql 12. I want to inject some pl/pgsql in my xml mapperfor example: to insert data with for loop<update id="generateNumbers" parameterType="tn.tt.nbms.dto.RangeDTO" statementType="CALLABLE"> <![CDATA[
declare
.....
begin
for number in ......
insert into.....(....) values (...)
end loop;
end;
]]></update>with oracle and Pl/sql it works finebut I can't convert it to pl/pgsqlHow can I do itI know I can use stored procedure but my question is how to inject pl/pgsql in xml mapper files if it is possible?Best regards,
--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/2be8db7e-14ce-480b-832d-fba2f9828996%40googlegroups.com.
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/emf29bb680-807c-4b77-a483-a7f51fa93472%40asus.
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/CAAjYA1Ng10bYkhhgXy%2BYBxhdVJghqfmrR0ocaqcxDqS_y-zJoQ%40mail.gmail.com.
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/CA%2Buep2S35b3AAM2vEHoBSd-V_3rrp0abGMeaUtXNqx4dLHgsSg%40mail.gmail.com.
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/CAAjYA1NULd-A9tkFSXtDYjjd-5tA4tOZDgVv5F8Ou1NZxgbYtw%40mail.gmail.com.
--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/CA%2Buep2T9ieH_oC3d%2B9r8r9eoLsf4Qxorq_cyOF_1uJyuAfwDZA%40mail.gmail.com.
|
|
|
Thank you In general, stored procedure performs better, I think, but you should compare both with your actual data if the performance is your primary concern. I On Fri, Jan 31, 2020 at 2:29 AM 'Yosra TRABELSI' via mybatis-user < [hidden email]> wrote: thank you very much So what is more performant stored procedure or batch operation ?
Hello,
The doc says:
> The code block is treated as though it were the body of a function with no parameters
Which means that (unlike Oracle) you cannot pass parameters to the code block.
Executing multiple statements with a single execute() call is not portable and usually comes with limitation.
I would recommend using batch operation [1][2]. It is a better solution especially when dealing with many rows
On Fri, Jan 31, 2020 at 12:50 AM 'Yosra TRABELSI' via mybatis-user < [hidden email]> wrote: Thank you for your response To test anonymous code block
I tried to delete a row from the table RANGE :
<update id="deleteRange" parameterType="tn.tt.nbms.dto.RangeDTO" statementType="CALLABLE"> <![CDATA[
DO $$ DECLARE BEGIN
delete from RANGE where ID_RANGE = #{idRange};
END $$;
</update>
it throws an exception :
org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.type.TypeException: Could not set parameters for mapping: ParameterMapping{property='idRange', mode=IN, javaType=class java.math.BigDecimal, jdbcType=null, numericScale=null, resultMapId='null', jdbcTypeName='null', expression='null'}. Cause: org.apache.ibatis.type.TypeException: Error setting non null for parameter #1 with JdbcType null . Try setting a different JdbcType for this parameter or a different configuration property. Cause: org.postgresql.util.PSQLException: L'indice de la colonne est hors limite : 1, nombre de colonnes : 0.
but if I replace
#{idRange} by a value like 3381 for example it works
so how I can set parameters ? can you help me ?
Le jeu. 30 janv. 2020 à 05:06, Guy Rouillier < [hidden email]> a écrit : PostgreSQL also has a FOR loop. See an example here:
You can put that loop into an anonymous code block inside your XML mapper.
On 1/29/2020 4:17:26 AM, "'Yosra TR' via mybatis-user" < [hidden email]> wrote:
HI ,I am using MyBatis 3.3.0 with Postgresql 12. I want to inject some pl/pgsql in my xml mapperfor example: to insert data with for loop<update id="generateNumbers" parameterType="tn.tt.nbms.dto.RangeDTO" statementType="CALLABLE"> <![CDATA[
declare
.....
begin
for number in ......
insert into.....(....) values (...)
end loop;
end;
]]></update>with oracle and Pl/sql it works finebut I can't convert it to pl/pgsqlHow can I do itI know I can use stored procedure but my question is how to inject pl/pgsql in xml mapper files if it is possible?Best regards,
--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/2be8db7e-14ce-480b-832d-fba2f9828996%40googlegroups.com.
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/emf29bb680-807c-4b77-a483-a7f51fa93472%40asus.
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/CAAjYA1Ng10bYkhhgXy%2BYBxhdVJghqfmrR0ocaqcxDqS_y-zJoQ%40mail.gmail.com.
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/CA%2Buep2S35b3AAM2vEHoBSd-V_3rrp0abGMeaUtXNqx4dLHgsSg%40mail.gmail.com.
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/CAAjYA1NULd-A9tkFSXtDYjjd-5tA4tOZDgVv5F8Ou1NZxgbYtw%40mail.gmail.com.
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/CA%2Buep2T9ieH_oC3d%2B9r8r9eoLsf4Qxorq_cyOF_1uJyuAfwDZA%40mail.gmail.com.
--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/CAAjYA1Nwc9QKeM4XySmWeA%2BNgXs9MJmgPhor%3Do83nJ3zYuA33Q%40mail.gmail.com.
|
|
Locked
