Informix

Hello community!

I need to provide a UDR which internally would need to execute the same statement with one parameter for each invocation. This suggests the usage of a prepared statement. The documentation for this topic is very unclear, but i thought i managed to understand how it works.

• in the first invocation I prepare the statement by mi_prepare() which returns me a statement descriptor. As of the documentation the descriptor is valid for a whole session. (at least, I interpret it like this, as it is stated that the resources are not freed until session termination if I omit mi_drop_prepared_statement)
• next I use mi_exec_prepared_statement() with this descriptor. this should fetch all resulting rows (in my case it is guaranteed to return only one row via group_concat())
• the statement descriptor is forwarded within mi_fparam structure within the command and for another invocation in a subsequent statement I use a named memory with session duration to preserve the pointer to the descriptor.
• extracting the result via mi_get_result(), mi_next_row() ... is equivalent to fetching data for mi_exec()
• mi_query_finish() is omitted, but mi_close() terminates the connection for a single invocation and frees the implicit cursor of mi_exec_prepared_statement.

That's the basic idea how it should work, but only the first invocation generates a result. All other invocations fail when executing mi_exec_prepared_statement() with the following error:

 7425: Invalid statement handle.
Error in line 1
Near character position 99

I searched over the documentation, but i did not find anything useful how to resolve that problem. The error is risen already with the second invocation within the same command - where the descriptor is passed through fparam.

Has anybody knwledge how to deal with that error?

kind regards 

Peter Weigert

Hi Peter,

I'm not that deep into C UDRs, but wouldn't another (SPL) UDR, invoked by your C UDR, solve the problem - it might be a little sub-optimal from performance perspective, though.

BR,
 Andreas

Hello community!

I need to provide a UDR which internally would need to execute the same statement with one parameter for each invocation. This suggests the usage of a prepared statement. The documentation for this topic is very unclear, but i thought i managed to understand how it works.

• in the first invocation I prepare the statement by mi_prepare() which returns me a statement descriptor. As of the documentation the descriptor is valid for a whole session. (at least, I interpret it like this, as it is stated that the resources are not freed until session termination if I omit mi_drop_prepared_statement)
• next I use mi_exec_prepared_statement() with this descriptor. this should fetch all resulting rows (in my case it is guaranteed to return only one row via group_concat())
• the statement descriptor is forwarded within mi_fparam structure within the command and for another invocation in a subsequent statement I use a named memory with session duration to preserve the pointer to the descriptor.
• extracting the result via mi_get_result(), mi_next_row() ... is equivalent to fetching data for mi_exec()
• mi_query_finish() is omitted, but mi_close() terminates the connection for a single invocation and frees the implicit cursor of mi_exec_prepared_statement.

That's the basic idea how it should work, but only the first invocation generates a result. All other invocations fail when executing mi_exec_prepared_statement() with the following error:

 7425: Invalid statement handle.
Error in line 1
Near character position 99

I searched over the documentation, but i did not find anything useful how to resolve that problem. The error is risen already with the second invocation within the same command - where the descriptor is passed through fparam.

Has anybody knwledge how to deal with that error?

kind regards 

Peter Weigert

Andreas, Art, the whole idea is to gain performance, and the routine should be a template for similar situations. Of course I could as well use mi_exec() which prepares the statement internally and this works quite well in other UDRs we already provide.

Maybe, that passing statemet descriptors to repeatedUDR- invocations is generally not possible, but the documentation (if correct) suggests different.

The rare samples I found in the HCL-Documentation always use the prepared statements in a loop within the very same invocation.  (https://help.hcltechsw.com/hclinformix/1410/dapip/ids_dapip_0353.html 2nd code snippet)

I have a table with 1:n (0<=n<=5 (mainly 5, max. 20)) relation to a item-key. I need to fetch these rows for the item, analyze the available combination and return the resulting property. It has the patential to be called for tons of items within one single query. Normally I would use a subquery for this problem, but the customer prefers a UDR to reduce SQL-complexity.  

Intended USAGE: select get_property(prim_key) from <huge_table> where <some_condition>

Instead of: select get_property((select group_concat(val) from <details-table> where <huge_table>.prim_key=<details-table>.prim_key)) from <huge_table> where <some_condition>

The idea is to include the subquery into get_property() function.

This is a first aproach - and I think it's valid to test the performance against other approaches. 

And as far as I learned C-UDRs are much, much faster than any SPL.

Hi Peter,

I'm not that deep into C UDRs, but wouldn't another (SPL) UDR, invoked by your C UDR, solve the problem - it might be a little sub-optimal from performance perspective, though.

BR,
 Andreas

Hello community!

I need to provide a UDR which internally would need to execute the same statement with one parameter for each invocation. This suggests the usage of a prepared statement. The documentation for this topic is very unclear, but i thought i managed to understand how it works.

• in the first invocation I prepare the statement by mi_prepare() which returns me a statement descriptor. As of the documentation the descriptor is valid for a whole session. (at least, I interpret it like this, as it is stated that the resources are not freed until session termination if I omit mi_drop_prepared_statement)
• next I use mi_exec_prepared_statement() with this descriptor. this should fetch all resulting rows (in my case it is guaranteed to return only one row via group_concat())
• the statement descriptor is forwarded within mi_fparam structure within the command and for another invocation in a subsequent statement I use a named memory with session duration to preserve the pointer to the descriptor.
• extracting the result via mi_get_result(), mi_next_row() ... is equivalent to fetching data for mi_exec()
• mi_query_finish() is omitted, but mi_close() terminates the connection for a single invocation and frees the implicit cursor of mi_exec_prepared_statement.

That's the basic idea how it should work, but only the first invocation generates a result. All other invocations fail when executing mi_exec_prepared_statement() with the following error:

 7425: Invalid statement handle.
Error in line 1
Near character position 99

I searched over the documentation, but i did not find anything useful how to resolve that problem. The error is risen already with the second invocation within the same command - where the descriptor is passed through fparam.

Has anybody knwledge how to deal with that error?

kind regards 

Peter Weigert

Peter:

Gotcha. First "C" UDRs are much much faster than an SPL routine depending on what you are doing. If it is simply preparing and executing a DML statement and returning data, my experience is that there isn't much difference. If you have to perform extensive text manipulation or arithmetic calculation, then yes, there will be a significant gain from using a "C" UDR over an SPL routine.

But, as you say, you need to test that assertion, I get it. I have had similar issues trying to make a statement handle persist across UDR calls to simulate the kind of PREPARE once, EXECUTE multiple strategy that I would implement in a standalone ESQL/C program in a "C" UDR. Tried passing it back to the caller and back in as an argument, saving tha handle in the fparam structure, and using named memory to hold it all to no avail. So I share your frustration. "It should work!" but it doesn't. 

As far as checking my assertion versus the idea that "C" should be faster, try a simpler example that does not require the persistence but performs similar operations internally to see if it is faster than the SPL solution is. If it is not, then the effort to try to get this solution to work may not be worth it. 

Andreas, Art, the whole idea is to gain performance, and the routine should be a template for similar situations. Of course I could as well use mi_exec() which prepares the statement internally and this works quite well in other UDRs we already provide.

Maybe, that passing statemet descriptors to repeatedUDR- invocations is generally not possible, but the documentation (if correct) suggests different.

The rare samples I found in the HCL-Documentation always use the prepared statements in a loop within the very same invocation.  (https://help.hcltechsw.com/hclinformix/1410/dapip/ids_dapip_0353.html 2nd code snippet)

I have a table with 1:n (0<=n<=5 (mainly 5, max. 20)) relation to a item-key. I need to fetch these rows for the item, analyze the available combination and return the resulting property. It has the patential to be called for tons of items within one single query. Normally I would use a subquery for this problem, but the customer prefers a UDR to reduce SQL-complexity.  

Intended USAGE: select get_property(prim_key) from <huge_table> where <some_condition>

Instead of: select get_property((select group_concat(val) from <details-table> where <huge_table>.prim_key=<details-table>.prim_key)) from <huge_table> where <some_condition>

The idea is to include the subquery into get_property() function.

This is a first aproach - and I think it's valid to test the performance against other approaches. 

And as far as I learned C-UDRs are much, much faster than any SPL.

Hi Peter,

I'm not that deep into C UDRs, but wouldn't another (SPL) UDR, invoked by your C UDR, solve the problem - it might be a little sub-optimal from performance perspective, though.

BR,
 Andreas

Hello community!

I need to provide a UDR which internally would need to execute the same statement with one parameter for each invocation. This suggests the usage of a prepared statement. The documentation for this topic is very unclear, but i thought i managed to understand how it works.

• in the first invocation I prepare the statement by mi_prepare() which returns me a statement descriptor. As of the documentation the descriptor is valid for a whole session. (at least, I interpret it like this, as it is stated that the resources are not freed until session termination if I omit mi_drop_prepared_statement)
• next I use mi_exec_prepared_statement() with this descriptor. this should fetch all resulting rows (in my case it is guaranteed to return only one row via group_concat())
• the statement descriptor is forwarded within mi_fparam structure within the command and for another invocation in a subsequent statement I use a named memory with session duration to preserve the pointer to the descriptor.
• extracting the result via mi_get_result(), mi_next_row() ... is equivalent to fetching data for mi_exec()
• mi_query_finish() is omitted, but mi_close() terminates the connection for a single invocation and frees the implicit cursor of mi_exec_prepared_statement.

That's the basic idea how it should work, but only the first invocation generates a result. All other invocations fail when executing mi_exec_prepared_statement() with the following error:

 7425: Invalid statement handle.
Error in line 1
Near character position 99

I searched over the documentation, but i did not find anything useful how to resolve that problem. The error is risen already with the second invocation within the same command - where the descriptor is passed through fparam.

Has anybody knwledge how to deal with that error?

kind regards 

Peter Weigert

Peter:

Gotcha. First "C" UDRs are much much faster than an SPL routine depending on what you are doing. If it is simply preparing and executing a DML statement and returning data, my experience is that there isn't much difference. If you have to perform extensive text manipulation or arithmetic calculation, then yes, there will be a significant gain from using a "C" UDR over an SPL routine.

But, as you say, you need to test that assertion, I get it. I have had similar issues trying to make a statement handle persist across UDR calls to simulate the kind of PREPARE once, EXECUTE multiple strategy that I would implement in a standalone ESQL/C program in a "C" UDR. Tried passing it back to the caller and back in as an argument, saving tha handle in the fparam structure, and using named memory to hold it all to no avail. So I share your frustration. "It should work!" but it doesn't. 

As far as checking my assertion versus the idea that "C" should be faster, try a simpler example that does not require the persistence but performs similar operations internally to see if it is faster than the SPL solution is. If it is not, then the effort to try to get this solution to work may not be worth it. 

Andreas, Art, the whole idea is to gain performance, and the routine should be a template for similar situations. Of course I could as well use mi_exec() which prepares the statement internally and this works quite well in other UDRs we already provide.

Maybe, that passing statemet descriptors to repeatedUDR- invocations is generally not possible, but the documentation (if correct) suggests different.

The rare samples I found in the HCL-Documentation always use the prepared statements in a loop within the very same invocation.  (https://help.hcltechsw.com/hclinformix/1410/dapip/ids_dapip_0353.html 2nd code snippet)

I have a table with 1:n (0<=n<=5 (mainly 5, max. 20)) relation to a item-key. I need to fetch these rows for the item, analyze the available combination and return the resulting property. It has the patential to be called for tons of items within one single query. Normally I would use a subquery for this problem, but the customer prefers a UDR to reduce SQL-complexity.  

Intended USAGE: select get_property(prim_key) from <huge_table> where <some_condition>

Instead of: select get_property((select group_concat(val) from <details-table> where <huge_table>.prim_key=<details-table>.prim_key)) from <huge_table> where <some_condition>

The idea is to include the subquery into get_property() function.

This is a first aproach - and I think it's valid to test the performance against other approaches. 

And as far as I learned C-UDRs are much, much faster than any SPL.

Hi Peter,

I'm not that deep into C UDRs, but wouldn't another (SPL) UDR, invoked by your C UDR, solve the problem - it might be a little sub-optimal from performance perspective, though.

BR,
 Andreas

Hello community!

I need to provide a UDR which internally would need to execute the same statement with one parameter for each invocation. This suggests the usage of a prepared statement. The documentation for this topic is very unclear, but i thought i managed to understand how it works.

• in the first invocation I prepare the statement by mi_prepare() which returns me a statement descriptor. As of the documentation the descriptor is valid for a whole session. (at least, I interpret it like this, as it is stated that the resources are not freed until session termination if I omit mi_drop_prepared_statement)
• next I use mi_exec_prepared_statement() with this descriptor. this should fetch all resulting rows (in my case it is guaranteed to return only one row via group_concat())
• the statement descriptor is forwarded within mi_fparam structure within the command and for another invocation in a subsequent statement I use a named memory with session duration to preserve the pointer to the descriptor.
• extracting the result via mi_get_result(), mi_next_row() ... is equivalent to fetching data for mi_exec()
• mi_query_finish() is omitted, but mi_close() terminates the connection for a single invocation and frees the implicit cursor of mi_exec_prepared_statement.

That's the basic idea how it should work, but only the first invocation generates a result. All other invocations fail when executing mi_exec_prepared_statement() with the following error:

 7425: Invalid statement handle.
Error in line 1
Near character position 99

I searched over the documentation, but i did not find anything useful how to resolve that problem. The error is risen already with the second invocation within the same command - where the descriptor is passed through fparam.

Has anybody knwledge how to deal with that error?

kind regards 

Peter Weigert

Paul, to your response according the duration of a statement descriptor: yes I read that, but there is also mentioned for the function mi_drop_prepared_statement():

Important: It is recommended that you explicitly free prepared statements with mi_drop_prepared_statement() once you no longer need them. Otherwise, these prepared statements remain until the associated session ends. To clear the cursor associated with a prepared statement instead of freeing the cursor, use the mi_close_statement() function.

And once more, as far as  I learned C is still much faster also for simple string manipulations - the more invocations the higher the difference.

And how would I do that approach to "prepare once, exec many" in SPL?

But, ok - if Art already failed on that, it might not be worth to give frustration a 2nd chance ;-)

Peter:

Gotcha. First "C" UDRs are much much faster than an SPL routine depending on what you are doing. If it is simply preparing and executing a DML statement and returning data, my experience is that there isn't much difference. If you have to perform extensive text manipulation or arithmetic calculation, then yes, there will be a significant gain from using a "C" UDR over an SPL routine.

But, as you say, you need to test that assertion, I get it. I have had similar issues trying to make a statement handle persist across UDR calls to simulate the kind of PREPARE once, EXECUTE multiple strategy that I would implement in a standalone ESQL/C program in a "C" UDR. Tried passing it back to the caller and back in as an argument, saving tha handle in the fparam structure, and using named memory to hold it all to no avail. So I share your frustration. "It should work!" but it doesn't. 

As far as checking my assertion versus the idea that "C" should be faster, try a simpler example that does not require the persistence but performs similar operations internally to see if it is faster than the SPL solution is. If it is not, then the effort to try to get this solution to work may not be worth it. 

Andreas, Art, the whole idea is to gain performance, and the routine should be a template for similar situations. Of course I could as well use mi_exec() which prepares the statement internally and this works quite well in other UDRs we already provide.

Maybe, that passing statemet descriptors to repeatedUDR- invocations is generally not possible, but the documentation (if correct) suggests different.

The rare samples I found in the HCL-Documentation always use the prepared statements in a loop within the very same invocation.  (https://help.hcltechsw.com/hclinformix/1410/dapip/ids_dapip_0353.html 2nd code snippet)

I have a table with 1:n (0<=n<=5 (mainly 5, max. 20)) relation to a item-key. I need to fetch these rows for the item, analyze the available combination and return the resulting property. It has the patential to be called for tons of items within one single query. Normally I would use a subquery for this problem, but the customer prefers a UDR to reduce SQL-complexity.  

Intended USAGE: select get_property(prim_key) from <huge_table> where <some_condition>

Instead of: select get_property((select group_concat(val) from <details-table> where <huge_table>.prim_key=<details-table>.prim_key)) from <huge_table> where <some_condition>

The idea is to include the subquery into get_property() function.

This is a first aproach - and I think it's valid to test the performance against other approaches. 

And as far as I learned C-UDRs are much, much faster than any SPL.

Hi Peter,

I'm not that deep into C UDRs, but wouldn't another (SPL) UDR, invoked by your C UDR, solve the problem - it might be a little sub-optimal from performance perspective, though.

BR,
 Andreas

Hello community!

I need to provide a UDR which internally would need to execute the same statement with one parameter for each invocation. This suggests the usage of a prepared statement. The documentation for this topic is very unclear, but i thought i managed to understand how it works.

• in the first invocation I prepare the statement by mi_prepare() which returns me a statement descriptor. As of the documentation the descriptor is valid for a whole session. (at least, I interpret it like this, as it is stated that the resources are not freed until session termination if I omit mi_drop_prepared_statement)
• next I use mi_exec_prepared_statement() with this descriptor. this should fetch all resulting rows (in my case it is guaranteed to return only one row via group_concat())
• the statement descriptor is forwarded within mi_fparam structure within the command and for another invocation in a subsequent statement I use a named memory with session duration to preserve the pointer to the descriptor.
• extracting the result via mi_get_result(), mi_next_row() ... is equivalent to fetching data for mi_exec()
• mi_query_finish() is omitted, but mi_close() terminates the connection for a single invocation and frees the implicit cursor of mi_exec_prepared_statement.

That's the basic idea how it should work, but only the first invocation generates a result. All other invocations fail when executing mi_exec_prepared_statement() with the following error:

 7425: Invalid statement handle.
Error in line 1
Near character position 99

I searched over the documentation, but i did not find anything useful how to resolve that problem. The error is risen already with the second invocation within the same command - where the descriptor is passed through fparam.

Has anybody knwledge how to deal with that error?

kind regards 

Peter Weigert

Hi Peter,

I'm not that deep into C UDRs, but wouldn't another (SPL) UDR, invoked by your C UDR, solve the problem - it might be a little sub-optimal from performance perspective, though.

BR,
 Andreas

Hello community!

I need to provide a UDR which internally would need to execute the same statement with one parameter for each invocation. This suggests the usage of a prepared statement. The documentation for this topic is very unclear, but i thought i managed to understand how it works.

• in the first invocation I prepare the statement by mi_prepare() which returns me a statement descriptor. As of the documentation the descriptor is valid for a whole session. (at least, I interpret it like this, as it is stated that the resources are not freed until session termination if I omit mi_drop_prepared_statement)
• next I use mi_exec_prepared_statement() with this descriptor. this should fetch all resulting rows (in my case it is guaranteed to return only one row via group_concat())
• the statement descriptor is forwarded within mi_fparam structure within the command and for another invocation in a subsequent statement I use a named memory with session duration to preserve the pointer to the descriptor.
• extracting the result via mi_get_result(), mi_next_row() ... is equivalent to fetching data for mi_exec()
• mi_query_finish() is omitted, but mi_close() terminates the connection for a single invocation and frees the implicit cursor of mi_exec_prepared_statement.

That's the basic idea how it should work, but only the first invocation generates a result. All other invocations fail when executing mi_exec_prepared_statement() with the following error:

 7425: Invalid statement handle.
Error in line 1
Near character position 99

I searched over the documentation, but i did not find anything useful how to resolve that problem. The error is risen already with the second invocation within the same command - where the descriptor is passed through fparam.

Has anybody knwledge how to deal with that error?

kind regards 

Peter Weigert

Hello community!

I need to provide a UDR which internally would need to execute the same statement with one parameter for each invocation. This suggests the usage of a prepared statement. The documentation for this topic is very unclear, but i thought i managed to understand how it works.

• in the first invocation I prepare the statement by mi_prepare() which returns me a statement descriptor. As of the documentation the descriptor is valid for a whole session. (at least, I interpret it like this, as it is stated that the resources are not freed until session termination if I omit mi_drop_prepared_statement)
• next I use mi_exec_prepared_statement() with this descriptor. this should fetch all resulting rows (in my case it is guaranteed to return only one row via group_concat())
• the statement descriptor is forwarded within mi_fparam structure within the command and for another invocation in a subsequent statement I use a named memory with session duration to preserve the pointer to the descriptor.
• extracting the result via mi_get_result(), mi_next_row() ... is equivalent to fetching data for mi_exec()
• mi_query_finish() is omitted, but mi_close() terminates the connection for a single invocation and frees the implicit cursor of mi_exec_prepared_statement.

That's the basic idea how it should work, but only the first invocation generates a result. All other invocations fail when executing mi_exec_prepared_statement() with the following error:

 7425: Invalid statement handle.
Error in line 1
Near character position 99

I searched over the documentation, but i did not find anything useful how to resolve that problem. The error is risen already with the second invocation within the same command - where the descriptor is passed through fparam.

Has anybody knwledge how to deal with that error?

kind regards 

Peter Weigert