Dataset with variables

[Mamede]

I am evaluating DOA and I thik it is very good.

I am having some problem with variables. I set the variables as parameters and I close and open the dataset but it allways tell me that there are no records. If I create a SQL using the same information directly on the SQL text and execute it I get the records.

If I use Substituition instead the variable type it works.

What am I doing wrong ??

Mamede

 

[hivaj]

Close and after use the metod ClearVariables and after open dataset...

dataset.close;
dataset.clearvariables;
dataset.setvariable('var',value);
dataset.open;

OK.

 

[Mamede]

dataset.close;
dataset.clearvariables;
dataset.setvariable('var',value);
dataset.open;

Hi hivaj

I did it but it did not works.

This is my code:

QTelefones.Close;
QTelefones.ClearVariables;
QTelefones.SetVariable('Pre',TServicos.fieldbyname('DDD').asstring);
QTelefones.SetVariable('Num',TServicos.fieldbyname('Telefone').asstring);
QTelefones.Open;

it allways result as a empty dataset...

 

[hivaj]

Sorry!

But, verify other configuration....

For example, session, the values of variables, the SQL, and if really exists the values that you search.

By...

 

[Mamede]

hivaj

Thanks for your attention...

This is the SQL text:

select loj_loja,
fon_prefixo,
fon_numero,
fon_descricao
from linhas_telefonicas
where linhas_telefonicas.fon_prefixo = re
and
linhas_telefonicas.fon_numero = :Num

The record that I am looking for exists but the component don

 

[F.Hinlopen]

Before the calls to SetVariable, you must declare them first:
QTelefones.DeclareVariable('Pre',otString);
QTelefones.DeclareVariable('Num',otString);
It is not necessary to do DeleteVariables, DeclareVariable, SetVariable at run time BTW. If you are not changing the SQL at run time, you can use the editor for the Variables property.
HTH

------------------
Frans

 

[Marco Kalter]

Or maybe the fon_prefixo and/or fon_numero columns are of type CHAR? The standard string variables are VARCHAR2, so trailing spaces would become siginificant.

If this is the case, use Char instead of String (otChar instead of otString at runtime) to fix this problem.

------------------
Marco Kalter
Allround Automations

 

[Mamede]

Thank you all.

About creation of variables, I don

 

[F.Hinlopen]

Mamede,

Sorry about misreading your earlier post. I read "DeleteVariables()" where you clearly wrote "ClearVariables()" and went off on a tangent.
How are you determining that no rows are returned? Do you have a data-aware component attached through a datasource, or are you looking at Eof or RecordCount properties? Does your query work if you pass it hardwired values known to exist, for example:
QTelefones.Close;
QTelefones.ClearVariables;
QTelefones.SetVariable('Pre','555');
QTelefones.SetVariable('Num','1234');
QTelefones.Open;
Are you sure TServicos has a usable result? Is it possible that there are padding spaces in the fon_prefixo, fon_numero, and especially DDD and Telefone columns? Are DDD and Telefone varchar2 also? How is TrimStringFields set in your OracleSession? I am asking you this because I think Marco is on the right track. The most significant clue is that things work when you use substitution. This works because you are plugging literal numbers into the SQL, and Oracle will do a data conversion before comparing a character field with a literal number, and it won't see the extra space.

Since you are setting all the variables yourself, it is not necessary to do a ClearVariables, by the way. This method is most useful in TOracleQuery with insert or update, when you want to get rid of the garbage from a previous execution, and just fill in a few values.

------------------
Frans