Jump to content


- - - - -

error with alphanumeric variable in SQL


6 replies to this topic

#1 Guest_Federico_*

Guest_Federico_*
  • Guests

Posted 19 August 2005 - 03:43 PM

Hello, I ask for its valuable support to solve the following problem:
I have the code...


@LOGIC
08/18/05 LOGIC DEFINITION
MDC/SYS/PTS_3

FUNCTION NAME: BORRAME2
LOGIC ID: 001
Change
LNE ST ----------------------- S T A T E M E N T
--------------------------
001 #NUMERO = 1111
002 $CADENA = 'XXXX'
003 SQL
004 BEGIN
005 :#NUMERO := 12345;
006 :$CADENA := 'FEDE';
007 END;
008 ENDSQL
009
010 UMSG('#NUMERO='+ CONV(#NUMERO) +' $CADENA='+ $CADENA, -1)




CMD>

According to the described code the message would have to be:
#NUMERO=12345 $CADENA=FEDE

Nevertheless, the message that I obtain is:
#NUMERO=12345 $CADENA=XXXX

Where it is the error?

NOTE: I have connection with RDBMS Oracle9i

Thank you very much.
Federico.

#2 Bill Loven

Bill Loven

    Expert

  • Members
  • PipPipPipPip
  • 147 posts
  • Gender:Male
  • Location:Coppell, United States

Posted 19 August 2005 - 03:59 PM

:D Where is your select statement?

#3 Guest_Federico_*

Guest_Federico_*
  • Guests

Posted 19 August 2005 - 04:43 PM

:D Where is your select statement?

with simple allocations like

:$my_var : = ' helló;

or with sentences SQL like

select column1
into :$my_var
from table1;

the same error is obtained

#4 Richard Bassett

Richard Bassett

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 696 posts
  • Location:Rural France

Posted 19 August 2005 - 05:20 PM

Federico..

I fear this may be going to surprise and disappoint you..

"Host variables that pass data from the RDBMS back to ProIV are not supported"

This is a direct quote from the SQL section of the ProIV V5.5 Environment Guide.

It might seem like it sort of works, sometimes, but that's just "undefined behaviour" as far as ProIV is concerned.
Nothing's as simple as you think

#5 Joseph Bove

Joseph Bove

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 756 posts
  • Gender:Male
  • Location:Ramsey, United States

Posted 23 August 2005 - 08:13 PM

Federico,

Along the lines of what Richard is saying:

When we have wanted to do thing things like that, we'll insert the result of the SQL call into a table and then read that table. Basically, the best we've been able to do is a two step approach to get a SQL value. It's not great, but it does work.

hth,

Joseph

#6 Wayne Biggs

Wayne Biggs

    Newbie

  • Members
  • Pip
  • 6 posts
  • Gender:Male
  • Location:UK

Posted 24 August 2005 - 09:12 AM

Federico,

Although not supported by Pro-IV, you can return numeric values from anonymous Oracle blocks and PL/SQL functions/Procedures so the following will work: -

SQL
BEGIN
:#result := some_package.some_function ;
END ;
ENDSQL

and

SQL
BEGIN
SELECT COUNT(*) into :#record_count FROM tablename WHERE condition ;
END ;
ENDSQL

You can also pass alphanumeric arguments into PL/SQL procedures/functions etc providing they are of type IN such as: -

SQL
BEGIN
some_package.some_procedure(:$INPUT1, :$INPUT2, :#OUTPUT1) ;
END ;
ENDSQL


The unofficial response from Pro-IV Ltd is that although it isn't supported, it does seem to work and they will try not to break it in the future :D

hth,

Wayne

#7 Guest_Guest_*

Guest_Guest_*
  • Guests

Posted 26 August 2005 - 03:05 PM

Federico,

Along the lines of what Richard is saying:

When we have wanted to do thing things like that, we'll insert the result of the SQL call into a table and then read that table. Basically, the best we've been able to do is a two step approach to get a SQL value. It's not great, but it does work.

hth,

Joseph


Joseph,

I have put in practices your suggestion, I have created a table to use it like bridge, the problem has been solved.

Thank you very much.
Federico.
:D



Reply to this topic



  


0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users