[firebird-support] "Invalid Cursor State"

2017-10-17 Thread Jorge Andres Brugger jorge.brug...@gmail.com [firebird-support] 
Please consider the following SP:

create or alter procedure SP_VAL_REGISTRAR_CANCELACION (
SUCURSAL smallint,
ID_SOLICITUD_A_CANCELAR numeric(8,0))
returns (
ID_SOLICITUD_CANCELACION numeric(8,0))
as
declare variable NUMERO_ITEM numeric(9,0);
declare variable IMPORTE_UNITARIO numeric(10,2);
declare variable CANTIDAD_SOLICITADA numeric(3,0);
declare variable CODIGO_ALFABETA numeric(5,0);
declare variable CODIGO_BARRAS numeric(15,2);
declare variable CODIGO_TROQUEL numeric(7,0);
declare variable GENERICO char(1);
declare variable CANTIDAD_APROBADA numeric(3,0);
declare variable CODIGO_AUTORIZACION varchar(20);
declare variable TIPO_COBERTURA char(1);
declare variable PORCENTAJE_COBERTURA numeric(6,2);
declare variable IMPORTE_COBERTURA numeric(15,2);
declare variable IMPORTE_A_CARGO_AFILIADO numeric(10,2);
begin

  /*Genero el ID_SOLICITUD para la cancelación y lo guardo en una variable
que utilizaré luego para insertar los items*/
  :ID_SOLICITUD_CANCELACION = next value for GEN_VAL_SOLICITUDES_ID;

  /*Copio los datos del encabezado de la solicitud pero asignándole el
código de acción para la CANCELACIÓN de la solicitud*/
  insert into VAL_SOLICITUDES (SUCURSAL, ID_SOLICITUD,
NUMERO_REFERENCIA_SOLICITUD, FECHA, HORA, TIPO_TERMINAL,
   TIPO_MENSAJE, CODIGO_ACCION,
NUMERO_TERMINAL, NOMBRE_SOFTWARE, VERSION_SOFTWARE,
   NOMBRE_VALIDADOR, CODIGO_PRESTADOR,
CUIT_PRESTADOR, TIPO_MATRICULA_PRESCRIPTOR,
   NUMERO_MATRICULA_PRESCRIPTOR,
TIPO_PRESCRIPTOR, PROVINCIA_PRESCRIPTOR,
   CODIGO_FINANCIADOR, TRACK_CREDENCIAL,
NUMERO_CREDENCIAL, NOMBRE_AFILIADO,
   MODO_INGRESO_CREDENCIAL, FECHA_RECETA,
FECHA_DISPENSA, HORA_DISPENSA, NUMERO_FORMULARIO,
   TIPO_TRATAMIENTO)
  select SUCURSAL, :ID_SOLICITUD_CANCELACION, NUMERO_REFERENCIA_SOLICITUD,
current_date, current_time, TIPO_TERMINAL,
 TIPO_MENSAJE, 20010, NUMERO_TERMINAL, NOMBRE_SOFTWARE,
VERSION_SOFTWARE, NOMBRE_VALIDADOR, CODIGO_PRESTADOR,
 CUIT_PRESTADOR, TIPO_MATRICULA_PRESCRIPTOR,
NUMERO_MATRICULA_PRESCRIPTOR, TIPO_PRESCRIPTOR,
 PROVINCIA_PRESCRIPTOR, CODIGO_FINANCIADOR, TRACK_CREDENCIAL,
NUMERO_CREDENCIAL, NOMBRE_AFILIADO,
 MODO_INGRESO_CREDENCIAL, FECHA_RECETA, FECHA_DISPENSA,
HORA_DISPENSA, NUMERO_FORMULARIO, TIPO_TRATAMIENTO
  from VAL_SOLICITUDES
  where ID_SOLICITUD = :ID_SOLICITUD_A_CANCELAR and
VAL_SOLICITUDES.SUCURSAL = :SUCURSAL;

  /*Copio los items de la solicitud para asignárselos a la solicitud de
cancelación*/
  for select VAL_SOLICITUDES_ITEMS.NUMERO_ITEM,
VAL_SOLICITUDES_ITEMS.IMPORTE_UNITARIO,
 VAL_SOLICITUDES_ITEMS.CANTIDAD_SOLICITADA,
VAL_SOLICITUDES_ITEMS.CODIGO_ALFABETA,
 VAL_SOLICITUDES_ITEMS.CODIGO_BARRAS,
VAL_SOLICITUDES_ITEMS.CODIGO_TROQUEL, VAL_SOLICITUDES_ITEMS.GENERICO,
 VAL_SOLICITUDES_ITEMS.CANTIDAD_APROBADA,
VAL_SOLICITUDES_ITEMS.CODIGO_AUTORIZACION,
 VAL_SOLICITUDES_ITEMS.TIPO_COBERTURA,
VAL_SOLICITUDES_ITEMS.PORCENTAJE_COBERTURA,
 VAL_SOLICITUDES_ITEMS.IMPORTE_COBERTURA,
VAL_SOLICITUDES_ITEMS.IMPORTE_A_CARGO_AFILIADO
  from VAL_SOLICITUDES_ITEMS
  where VAL_SOLICITUDES_ITEMS.ID_SOLICITUD = :ID_SOLICITUD_A_CANCELAR
and
VAL_SOLICITUDES_ITEMS.SUCURSAL = :SUCURSAL
  into :NUMERO_ITEM, :IMPORTE_UNITARIO, :CANTIDAD_SOLICITADA,
:CODIGO_ALFABETA, :CODIGO_BARRAS, :CODIGO_TROQUEL,
   :GENERICO, :CANTIDAD_APROBADA, :CODIGO_AUTORIZACION,
:TIPO_COBERTURA, :PORCENTAJE_COBERTURA,
   :IMPORTE_COBERTURA, :IMPORTE_A_CARGO_AFILIADO
  do
insert into VAL_SOLICITUDES_ITEMS (SUCURSAL, ID_SOLICITUD, NUMERO_ITEM,
IMPORTE_UNITARIO, CANTIDAD_SOLICITADA,
   CODIGO_ALFABETA, CODIGO_BARRAS,
CODIGO_TROQUEL, GENERICO, CANTIDAD_APROBADA,
   CODIGO_AUTORIZACION, TIPO_COBERTURA,
PORCENTAJE_COBERTURA, IMPORTE_COBERTURA,
   IMPORTE_A_CARGO_AFILIADO)
values (:SUCURSAL, :ID_SOLICITUD_CANCELACION, :NUMERO_ITEM,
:IMPORTE_UNITARIO, :CANTIDAD_SOLICITADA,
:CODIGO_ALFABETA, :CODIGO_BARRAS, :CODIGO_TROQUEL, :GENERICO,
:CANTIDAD_APROBADA, :CODIGO_AUTORIZACION,
:TIPO_COBERTURA, :PORCENTAJE_COBERTURA, :IMPORTE_COBERTURA,
:IMPORTE_A_CARGO_AFILIADO);
end

I´m calling it from Clarion by using ODBC, and getting "Invalid Cursor
State".
ODBC trace includes:

FARMACLICK  a2c-2774ENTER SQLFreeStmt
HSTMT   0x0376DF80
UWORD3 

FARMACLICK  a2c-2774EXIT  SQLFreeStmt  with return code 0
(SQL_SUCCESS)
HSTMT   0x0376DF80
UWORD3 

FARMACLICK  a2c-2774ENTER SQLPrepare
HSTMT   0x0376DF80
UCHAR * 0x03C64990 [  -3] "{CALL
sp_val_registrar_cancelacion(2,42)}\ 0"
SDWORD

Re: [firebird-support] "Invalid Cursor State"

2017-10-17 Thread Dimitry Sibiryakov s...@ibphoenix.com [firebird-support] 
17.10.2017 20:12, Jorge Andres Brugger jorge.brug...@gmail.com 
[firebird-support] wrote:
> What am I doing wrong?

   You cannot fetch from non-select query.


-- 
   WBR, SD.






++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu there.

Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ 

++


Yahoo Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

Re: [firebird-support] "Invalid Cursor State"

2017-10-17 Thread Jorge Andres Brugger jorge.brug...@gmail.com [firebird-support] 
Dimitry:
I do it all the time ... (read output parameters from executable
procedures, using "call executable_sp" by ODBC). No issues with other SPs.
Regards



2017-10-17 15:38 GMT-03:00 Dimitry Sibiryakov s...@ibphoenix.com
[firebird-support] :

> 17.10.2017 20:12, Jorge Andres Brugger jorge.brug...@gmail.com
> [firebird-support] wrote:
> > What am I doing wrong?
>
>You cannot fetch from non-select query.
>
>
> --
>WBR, SD.
>
>
> 
>
> 
>
> ++
>
> Visit http://www.firebirdsql.org and click the Documentation item
> on the main (top) menu.  Try FAQ and other links from the left-side menu
> there.
>
> Also search the knowledgebases at http://www.ibphoenix.com/
> resources/documents/
>
> ++
> 
>
> Yahoo Groups Links
>
>
>
>

RE: [firebird-support] "Invalid Cursor State"

2017-10-17 Thread 'Leyne, Sean' s...@broadviewsoftware.com [firebird-support] 

Dimitry:
I do it all the time ... (read output parameters from executable procedures, 
using "call executable_sp" by ODBC). No issues with other SPs.

 Unless I missed something, your SP is missing the required SUSPEND to make 
the SP selectable.


Regards



2017-10-17 15:38 GMT-03:00 Dimitry Sibiryakov 
s...@ibphoenix.com [firebird-support] 
mailto:firebird-support@yahoogroups.com>>:
17.10.2017 20:12, Jorge Andres Brugger 
jorge.brug...@gmail.com [firebird-support] 
wrote:
> What am I doing wrong?

   You cannot fetch from non-select query.


--
   WBR, SD.






++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu there.

Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/

++


Yahoo Groups Links

Re: [firebird-support] "Invalid Cursor State"

2017-10-18 Thread Jorge Andres Brugger jorge.brug...@gmail.com [firebird-support] 
Sean:
It´s an executable procedure which returns a parameter. It´s not a
selectable procedure.
Its the kind of SP you invoke like "execute procedure ... returning values
..." while selectable ones are usually called as "select ... from
stored_procedure_name"
I´ve managed to fix my issue, anyway.
Thanks!

2017-10-17 17:47 GMT-03:00 'Leyne, Sean' s...@broadviewsoftware.com
[firebird-support] :

>
>
>
>
> Dimitry:
> I do it all the time ... (read output parameters from executable
> procedures, using "call executable_sp" by ODBC). No issues with other SPs.
>
>
>
>  Unless I missed something, your SP is missing the required SUSPEND to
> make the SP selectable.
>
>
>
>
>
> Regards
>
>
>
>
>
>
>
> 2017-10-17 15:38 GMT-03:00 Dimitry Sibiryakov s...@ibphoenix.com
> [firebird-support] :
>
> 17.10.2017 20:12, Jorge Andres Brugger jorge.brug...@gmail.com
> [firebird-support] wrote:
> > What am I doing wrong?
>
>You cannot fetch from non-select query.
>
>
> --
>WBR, SD.
>
>
> 
>
> 
>
> ++
>
> Visit http://www.firebirdsql.org and click the Documentation item
> on the main (top) menu.  Try FAQ and other links from the left-side menu
> there.
>
> Also search the knowledgebases at http://www.ibphoenix.com/
> resources/documents/
>
> ++
> 
>
> Yahoo Groups Links
>
>
>
>
>
>
>
>
>
>
> 
>

RE: [firebird-support] "Invalid Cursor State"

2017-10-18 Thread 'Leyne, Sean' s...@broadviewsoftware.com [firebird-support] 


It´s an executable procedure which returns a parameter. It´s not a selectable 
procedure.
Its the kind of SP you invoke like "execute procedure ... returning values ..." 
while selectable ones are usually called as "select ... from 
stored_procedure_name"
I´ve managed to fix my issue, anyway.

 What was the solution?  (Others might want to know)

Re: [firebird-support] "Invalid Cursor State"

2017-10-19 Thread Jorge Andres Brugger jorge.brug...@gmail.com [firebird-support] 
I´m not sure which was the solution that finally worked, as I´ve made
several changes, mostly on the execution order in my program (windows exe,
not FB).
I suppose there is some issues with ODBC driver _and_ Clarion, but I´m not
really sure about it.
Regards

2017-10-18 15:19 GMT-03:00 'Leyne, Sean' s...@broadviewsoftware.com
[firebird-support] :

>
>
>
>
>
>
> It´s an executable procedure which returns a parameter. It´s not a
> selectable procedure.
>
> Its the kind of SP you invoke like "execute procedure ... returning values
> ..." while selectable ones are usually called as "select ... from
> stored_procedure_name"
>
> I´ve managed to fix my issue, anyway.
>
>
>
>  What was the solution?  (Others might want to know)
>
>
>
>
> 
>

Re: [firebird-support] "Invalid Cursor State"

2017-10-19 Thread Tomasz Tyrakowski t.tyrakow...@sol-system.pl [firebird-support] 
On 19.10.2017 at 16:46, Jorge Andres Brugger jorge.brug...@gmail.com 
[firebird-support] wrote:
> I´m not sure which was the solution that finally worked, as I´ve made
> several changes, mostly on the execution order in my program (windows exe,
> not FB).
> I suppose there is some issues with ODBC driver _and_ Clarion, but I´m not
> really sure about it.

The real nightmare of a programmer is not when the program doesn't work 
and she/he doesn't know why; it's when the program does work and she/he 
doesn't know why :)

have a good one
Tomasz

-- 
__--==--__
__--== Tomasz Tyrakowski==--__
__--==SOL-SYSTEM==--__
__--== http://www.sol-system.pl ==--__
__--==--__

Re: [firebird-support] "Invalid Cursor State"

2017-10-19 Thread Dimitry Sibiryakov s...@ibphoenix.com [firebird-support] 
19.10.2017 18:11, Tomasz Tyrakowski t.tyrakow...@sol-system.pl 
[firebird-support] wrote:
> The real nightmare of a programmer is not when the program doesn't work
> and she/he doesn't know why; it's when the program does work and she/he
> doesn't know why :)

https://docs.microsoft.com/en-us/sql/odbc/reference/develop-app/was-a-result-set-created


-- 
   WBR, SD.






++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu there.

Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ 

++


Yahoo Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/