SELECT LOCAL VARIABLE(s)

[jdesign]

hello guys,

is this possible to use SELECT statement to initialize local variable.
MS SQL supports such kind of operation:

SELECT @myCustomersName = CustomersName
FROM Customers
WHERE SecPolID = 3233

when I try to use this in pervasive I got Syntax Error: SELECT ?<< ??? >>=CustomersName FROM Customers WHERE SecPolID = 3233

Any idea to solve it?

regards
Jacek

 

[jdesign]

surely I've forgot to show you my pervasive code:

-- do something...

SELECT :myCustomersName = CustomersName
FROM Customers
WHERE SecPolID = 3233

-- ok, here goes another fragment of code...

 

[mirtheil]

Are you trying to do this within a Stored Procedure? If not, then that would be the problem. Select Variables are not valid outside of Stored Procedures. If you are using Stored Procedures, you need to make sure the variable is declared before using it.

info@mirtheil.com
Custom VB and Btrieve development.
Certified Pervasive Developer
Certified Pervasive Technician

 

[jdesign]

yes, I am using stored procedure!

create procedure MyProc() as
begin
-- this is a sample only not real problem
declare :myCustomerName varchar(30);
declare :myCustomerUID varchar(30);
declare :myCustomerPrice decimal(19,0);
-- I want to initialize all this variables in ONE STATEMENT!!!
SELECT :myCustomerName = CustomerName, :myCustomerUID = CustomerUID, :myCustomerPrice = CustomerPrice FROM Customers WHERE SecPolID = 3444

-- surely I CAN USE 3 TIMES statements like below:
SET :myCustomerName = (SELECT CustomerName FROM Customers WHERE SecPolID = 3444);
SET :myCustomerUID = (SELECT :CustomerUID FROM Customers WHERE SecPolID = 3444);
--- and so on...

--- but in my opinion this is not elegant, not so smart... this is not it :-(

end;

I am a little disappointed pervasive sql... in MS SQL (transact-sql) such kind of statement I wanted to use there is NO PROBLEM...
could you help me? I use Pervasive SQL 2000i with Service pack; my colleague is using pervasive 8 with sp1. and that really do not works (statement error)

regards
Jacek

 

[mirtheil]

I tried here and you're right, it doesn't work with one statement. I even tried using the SELECT INTO construct (select Name into :myName from table).
I am a little curious why you're using Stored Procedures? WIth Pervasive, there's no difference in performance between SPs and regular statements.
At this point, I would suggest contacting Pervasive so that they can investigate to see if this should work or if it needs to be added to the product.


info@mirtheil.com
Custom VB and Btrieve development.
Certified Pervasive Developer
Certified Pervasive Technician

 

[jdesign]

ok, as I know SP are much faster that traditional select because SP are stored on server and need no being parsed everytime one execute them.
if this is not true why there are SP on pervasive sql platform? ;-)
besides I need to do a lot of things not only one statement so I choose SP
anyway, thank you for your help.

regards
Jacek

PS. this is so irritating that simple pervasive's sql statements do not work... using MS SQL is a great thing comparing to pervasive... :-(

 

[mirtheil]

SPs are not precompiled with Pervasive. I also have to disagree on the comment that MS SQL is better than Pervasive.
I use the Btrieve API and nothing compares to it in terms of speed. SQL is good for some things but is not designed for every single application.
Microsoft has shoved Stored Procedures down the developer's throat and I don't see a reason for it. It's interesting that MS SQL needs to pre compile the SPs to get better performance.

info@mirtheil.com
Custom VB and Btrieve development.
Certified Pervasive Developer
Certified Pervasive Technician

 

[jdesign]

In my opinion nothing compares with MS SQL speed. I have copied about 1.5 milion (!!!) records with SP in... 30 seconds! this is not a joke... but good written SP.
by the way - show me such trick on pervasive. I'd like to know how to optimize it's speed.
My idea wasn't to prove which is the best/better. But I am at the edge of crazy ;-))) when statement DOES NOT WORK and you can read in pervasive docs that it works. Horrible. simple and stupid, elementary things DO NOT WORKS. @@ROWCOUNT, ABS() with variables (not DB FIELD or number but ABSnumber)), select :blahblahblah = name from mytable) and so on...
unfortunelly I have to use this tool (pervasive). please do not take it personally. but as I said it's irritating and something like that has no place in MS or oracle tools (for example).
thank you for your help.
J.