SQL Embedded problem

[João Quina]

I there,

Im having an error with the sql embedded command:

set cod="xx"
set codigo =""
&sql(SELECT Field1 INTO :codigo FROM Data WHERE Field2 = :cod)
$$$TRACE(codigo)

The "Data" table is composed by 2 fields (Field1,Field2), and it has
only 1 row.

Everytime that in my code i run the above query, and nothing is found
in the table, the variable "codigo" returns the value of Field1 of the
last row, instead of "". does anyone have any idea about why this has
this behavior??

I'm running Ensemble (Version 2010.1)

Thanks,
João

[David Loveluck]

João

If the SQL query fails (including finding no rows), the content of codigo is not determined. You must always check the success of the query as returned in SQLCODE. SQLCODE=0 if the call is successful.

dave

--
You received this message because you are subscribed to the Google Groups "InterSystems: Ensemble in Healthcare Community" group.
To post to this group, send email to Ensemble-in...@googlegroups.com
To unsubscribe from this group, send email to Ensemble-in-Healt...@googlegroups.com
For more options, visit this group at http://groups.google.com/group/Ensemble-in-Healthcare?hl=en

[João Quina]

Hi dave,

The SQLCODE is 0, that's the problem.

doing this:
&sql(SELECT count(Field1) INTO :codigo FROM Data WHERE Field2 = :cod)
"codigo" comes with 0
but doing this &sql(SELECT Field1 INTO :codigo FROM Data WHERE Field2
= :cod)
"codigo" returns the value of the last row. Still the SQLCODE is 0 on
both cases.


João

On 9 Maio, 16:45, David Loveluck <David.Lovel...@intersystems.com>
wrote:

> João
>
> If the SQL query fails (including finding no rows), the content of codigo is not determined. You must always check the success of the query as returned in SQLCODE. SQLCODE=0 if the call is successful.
>
> dave
>
>
>
>
>
>
>
> -----Original Message-----
> From: ensemble-in...@googlegroups.com [mailto:ensemble-in...@googlegroups.com] On Behalf Of João Quina
> Sent: Wednesday, May 09, 2012 11:14 AM
> To: InterSystems: Ensemble in Healthcare
>

> Cc: jorge.serra...@gmail.com

[David Loveluck]

That doesn't seem right. If the query finds no rows, SQLCODE should be set to 100. Please post the code that shows SQLCODE=0 after finding no rows

[Jorge Serrão]

Hi,

we've been testing (me and João) and doing this:

 

&sql(SELECT count(Field1) INTO :codigo FROM Data WHERE Field2 = :cod) 

"codigo" comes with 0 

And SQLCODE=0

but doing this &sql(SELECT Field1 INTO :codigo FROM Data WHERE Field2 

= :cod) 

"codigo" returns the value of the last row. SQLCODE=100

is there any changes in the latest versions of ensemble to prevent this from happening? 

Jorge

> To post to this group, send email to Ensemble-in-Healthcare@googlegroups.com
> To unsubscribe from this group, send email to Ensemble-in-Healthcare-unsub...@googlegroups.com

> For more options, visit this group athttp://groups.google.com/group/Ensemble-in-Healthcare?hl=en

--
You received this message because you are subscribed to the Google Groups "InterSystems: Ensemble in Healthcare Community" group.

To post to this group, send email to Ensemble-in-Healthcare@googlegroups.com
To unsubscribe from this group, send email to Ensemble-in-Healthcare-unsub...@googlegroups.com

[David Loveluck]

Jorge

 

 

The first query returns a single row so SQLCODE=0 is correct and the value Codigo=0 can be used.

 

In the second case, no rows are found so SQLCODE=100. At this point the content of codigo is undertermined.

 

What do you want to prevent? Both behaviors seem correct.

 

 

dave

> To post to this group, send email to Ensemble-in...@googlegroups.com
> To unsubscribe from this group, send email to Ensemble-in-Healt...@googlegroups.com

> For more options, visit this group athttp://groups.google.com/group/Ensemble-in-Healthcare?hl=en

--
You received this message because you are subscribed to the Google Groups "InterSystems: Ensemble in Healthcare Community" group.

To post to this group, send email to Ensemble-in...@googlegroups.com
To unsubscribe from this group, send email to Ensemble-in-Healt...@googlegroups.com

For more options, visit this group at http://groups.google.com/group/Ensemble-in-Healthcare?hl=en

--

You received this message because you are subscribed to the Google Groups "InterSystems: Ensemble in Healthcare Community" group.

To post to this group, send email to Ensemble-in...@googlegroups.com
To unsubscribe from this group, send email to Ensemble-in-Healt...@googlegroups.com

[João Quina]

The second behavior isn't correct.

Imagine that you have a table with 2 columns like this:

Filed1 Field2
A 1
B 2

What happens is this:




set cod=3

&sql(SELECT count(Field1) INTO :codigo FROM Data WHERE Field2 = :cod)
"codigo" comes with 0
And SQLCODE=0

&sql(SELECT Field1 INTO :codigo FROM Data WHERE Field2 = :cod)

"codigo" comes with "B"
SQLCODE=100
It should return null, not "B" as there isn't a value of "3" in the
field2 column.

Thanks,

João


On 15 Maio, 11:54, David Loveluck <David.Lovel...@intersystems.com>
wrote:

> > To post to this group, send email to Ensemble-in...@googlegroups.com<mailto:Ensemble-in...@googlegroups.com>
> > To unsubscribe from this group, send email to Ensemble-in-Healt...@googlegroups.com<mailto:Ensemble-in-Healt...@googlegroups.com>
> > For more options, visit this group athttp://groups.google.com/group/Ensemble-in-Healthcare?hl=en<http://groups.google.com/group/Ensemble-in-Healthcare?hl=en>

>
> --
> You received this message because you are subscribed to the Google Groups "InterSystems: Ensemble in Healthcare Community" group.

> To post to this group, send email to Ensemble-in...@googlegroups.com<mailto:Ensemble-in...@googlegroups.com>
> To unsubscribe from this group, send email to Ensemble-in-Healt...@googlegroups.com<mailto:Ensemble-in-Healt...@googlegroups.com>

[David Loveluck]

João

null would only be a 'correct value' if a row had been returned with field 2 containing null. That is not the case.

You could argue that codigo should contain null, "" (empty string), the value before the SQL statement or many other things if no rows were returned. But if SQLCODE=100 no value (null or otherwise) in codigo can be said to be the result of the query. In practice it is arbitrary.

If, in your particular case, you want to use a particular value when no rows are found, you must check for SQLCODE=100 and set codigo to whatever you want.

Dave

[JonA]

Hi Joao,
I've just tried this; codigo was undefined then I ran

set cod=3
&sql(SELECT Field1 INTO :codigo FROM DBH.Data WHERE Field2 = :cod)

after this codigo was still undefined and SQLCODE = 100 which is what
I had expected. However if I set codigo="hello" then run the code
above codigo remains set to "hello"; again as I expected - the SQL
doesn't change the value of the variable as the SQL didn't return a
value for codigo.

Jon

[João Quina]

Hi,
Yes David i ment to say "" (null string) as thers not that value in
the table. I think the behavior is wrong, or maybe im doing it the
wrong way, ill post my code here:


set cod= pRequest.Valores.GetAt(i)

$$$TRACE("isencao before "_cod)
//set codigo= "teste"
k codigo // added 22-05-2012 for testing
k SQLCode // added 22-05-2012 for testing
//$$$TRACE("isencao mapeamento1 "_codigo)
&sql(SELECT CampoHosix INTO :codigo FROM RNU_Map.TipoExencion
WHERE CampoRNU = :cod )
$$$TRACE("isencao mapeamento2 "_codigo)
$$$TRACE("SQLCode "_SQLCODE)

do aux.Valores.Insert("'"_codigo_"'")


my table is called RNU_Map.TipoExencion and its like this:

# ID CampoHosix CampoRNU
1 1 02 201
2 2 03 301
3 3 04 401


Just so i can explain it properly, the cod comes with "705", so i
expect that the answer is a null string as there is no CampoHosix for
that value. The SQLCode comes with 100. This query is returning "04"
instead of "" (or SQLCode=0).

Thanks,
João

[David Loveluck]

I have answered this as clearly as I can. If the query returns no row, the value is completely undetermined.

There is no 'correct' value for the returned value. The query did not return a row with "" or null so they are not correct values.

Check for SQL CODE and if it is not 0 then you MUST ignore the value returned.

This is correct behavior that has stood for many, many years and will not change.

-----Original Message-----
From: ensemble-in...@googlegroups.com [mailto:ensemble-in...@googlegroups.com] On Behalf Of João Quina
Sent: Tuesday, May 22, 2012 4:49 AM
To: InterSystems: Ensemble in Healthcare
Subject: [InterSystems-EnsHlth] Re: SQL Embedded problem

[Dale du Preez]

Hi Joao,

The problem Dave is referring to is the fact that you are not checking the value of SQLCODE. Your code should look something like this:

&sql(SELECT CampoHosix INTO :codigo FROM RNU_Map.TipoExencion WHERE CampoRNU = :cod )

If SQLCODE = 0 {
// SQL statement was executed successfully
Do aux.valores.Insert(codigo)
}
Else {
// SQL statement did not find any rows
// what should happen here...?
}

As Dave mentions, this has been the behaviour of embedded SQL for a very long time, so we cannot change this without having a major impact on a very large number of applications.

If you would like an alternative approach, the following may be worth considering:
Set resultset = ##class(%SQL.Statement).%ExecDirect(,"SELECT CampoHosix FROM RNU_Map.TipoExencion WHERE CampoRNU = ?", cod)
If resultset.%Next() {
Set codigo = resultset.CampoHosix
}
Else {
// take default action
}

I hope that helps,
Dale

[eric]

Hi  João ,

An alternative too is to use a Cursor for your query :

NEW SQLCODE,%ROWCOUNT

set  codigo ="" 

&sql(DECLARE C1 CURSOR FOR SELECT   Field1 INTO :codigo FROM Data WHERE Field2 = :cod) 

&sql(OPEN C1)
  &sql(FETCH C1)
While (SQLCODE = 0) {
     s c = codigo
             
     &sql(FETCH C1)
  }
    
  &sql(CLOSE C1)

if SQLCODE<0 q $$$ERROR($$$SQLError,SQLCODE,$g(%msg))

the  %ROWCOUNT value shows the number of rows returned. If no row is returned the value of codigo is the initialized value.

The SQLCODE, cod   variables have to be declared as PUBLIC  at the beginning (see InterSystems Documentation).

Regards,

Eric

[João Quina]

Hi,

I see now that my only solution is check for the SQLCODE (yes Dave i
know you said it before, sorry)
I didn't know that i had to igonre every value different from 0.

anyways i solved it with this line:
if SQLCODE'=0 set codigo=""

As i need to be inserted "" null string into the list because it is
already validating this one my code.

Thanks for all the help.


João