SELECT *
FROM XST_WEATHER;
DECLARE ZBuildingGuid VARCHAR(40);
SET ZBuildGuid='Stupid';
PRINT ZBuildGuid;

ElevateDB Error #700 An error was found in the statement at line 3 and column 1 (Expected end of expression but instead found DECLARE)

A simple script to test, I have no words to say, can't use parameters? or is there some trick?

RichardWu


This one's easy. The DECLARE statements must all be at the beginning of the script. But its not going to work anyway.

DECLARE ZBuildingGuid VARCHAR(40);
SELECT * FROM XST_WEATHER;
SET ZBuildGuid='Stupid';
PRINT ZBuildGuid;

However, to respond to your title, there are no parameters. Also, because you may not have posted the full code, I'm not sure you're talking about a script or a query. If its a query then it won't work. If its a script it won't work either because ElevateDB has no PRINT verb.

I'm guessing that you're evaluating or learning ElevateDB and are trying to copy scripts from some other engine. If so you'll have to expect some conversion.

Try using ElevateDB to test things out. Its help system is very good and you'll easily find verbs, functions and operators that aren't available. You'll also see that you can create both scripts and queries. Scripts start SCRIPT and have a full programming language. Queries consist of one single SQL statement.


Roy Lambert [Team Elevate]

I am trying to convert a SQL procedure to run locally in EDB, because customers require run the application offline.
No I face a great challenge:
There are always errors when I run the converted scripts, so I try to put it in small pieces and try to debug it,
Now what I want to do is simple:
declare a parameter and set the parameter value from a SELECT query,That's it!
SCRIPT
BEGIN
DECLARE TEST VARCHAR(40);
SET TEST ='SSS'
END

ElevateDB Error #700 An error was found in the statement at line 1 and column 5 (Expected BACKUPS, FILES, UPDATES but instead found TEST)

any good idea on this? or

SCRIPT
BEGIN
DECLARE TEST VARCHAR(40);
SET TEST =(SELECT xx FROM xxx WHERE xxx)
END

How does make this work? No matter query or script, I just want it run without error and I could make the parameter get the value.

On 10/4/2013 3:37 PM, RichardWu wrote:
> declare a parameter and set the parameter value from a SELECT query,That's it!

How about this :

SCRIPT
BEGIN
   DECLARE myVal VARCHAR;
   EXECUTE IMMEDIATE 'SELECT MyVarColumn INTO ? FROM Mytable WHERE
Record=1' USING myVal;
END

Raul

When I run the execute
there is an error   'There are no statements to execute'


Raul wrote:

On 10/4/2013 3:37 PM, RichardWu wrote:
> declare a parameter and set the parameter value from a SELECT query,That's it!

How about this :

SCRIPT
BEGIN
   DECLARE myVal VARCHAR;
   EXECUTE IMMEDIATE 'SELECT MyVarColumn INTO ? FROM Mytable WHERE
Record=1' USING myVal;
END

Raul

RichardWu wrote:

> When I run the execute
> there is an error   'There are no statements to execute'
>
>
> Raul wrote:
>
> On 10/4/2013 3:37 PM, RichardWu wrote:
> > declare a parameter and set the parameter value from a SELECT
> > query,That's it!
>
> How about this :
>
> SCRIPT
> BEGIN
>     DECLARE myVal VARCHAR;
>     EXECUTE IMMEDIATE 'SELECT MyVarColumn INTO ? FROM Mytable WHERE
> Record=1' USING myVal;
> END
>
> Raul

RichardWu,

Did you choose "New > Statement" or "New > Script"?

--
Michael Riley
GySgt USMC (Ret)
www.zilchworks.com

RichardWu


As Michael says, and as I tried to indicate, you're using a query as a script. It will not work.

From the nature of your posts I have the impression that you're assuming that ElevateDB is the same as the other environments that you are used to. Yes Tim has followed the SQL standard but he has built extensions in as have most vendors, and there are divergences where the standard doesn't cover it. I'm also not sure wether you're using EDBManager or just stuffing code in a query in an app and trying to run it.

I think you need to take a step backwards and understand how ElevateDB works to start with.

One important difference is queries vs scripts. In an application this is taken care of with two different components TEDBQuery and TEDBScript (there are also TEDBStoredProc and TEDBUpdateSQL but ignore them for now). In EDBManager, as Michael has said, this is taken care of by selecting the appropriate type from the New button drop down (simply clicking the button gives a query).

I also think there may be important differences in the jargon used eg parameters. You keep using it in a way that is not going to make sense to an ElevateDB user and so will hinder us in giving assistance. There was also important information lacking eg this script came from a different language.

What may be most productive in your case is to post a couple of the scripts you're trying to convert, let people know what you're trying to convert from and see if any of us (not me unfortunately) can convert them for you. You will then have some examples to copy from.

Roy Lambert [Team Elevate]

Hi guys
  Thank you very much, that was really great help for me!
  But is there any way that I could see the parameters value?
  Like other languages ' PRINT parameter'
  I tried the  "Script > parameters" , but no parameter value displayed...





> Raul wrote:
>
> On 10/4/2013 3:37 PM, RichardWu wrote:
> > declare a parameter and set the parameter value from a SELECT
> > query,That's it!
>
> How about this :
>
> SCRIPT
> BEGIN
>     DECLARE myVal VARCHAR;
>     EXECUTE IMMEDIATE 'SELECT MyVarColumn INTO ? FROM Mytable WHERE
> Record=1' USING myVal;
> END
>
> Raul

RichardWu,

Did you choose "New > Statement" or "New > Script"?

--
Michael Riley
GySgt USMC (Ret)
www.zilchworks.com

RichardWu,

>     Like other languages ' PRINT parameter'
>     I tried the  "Script > parameters" , but no parameter value displayed...

You can use:

SET LOG MESSAGE TO MyParam;

or e.g. (from the manual):
SET LOG MESSAGE TO 'Customer # '+CAST(CustNo AS VARCHAR)+' updated';

Working with EDBManager you can see all log messages in the "log
messages" tab, in your application you can use the OnLogMessage event of
a query or stored procedure.

Uli