Tim, others,

Could you give a small example of a SP that populates a table with data (an
x number of records). This way I can complete the SP for creating and
populating a db.

Regards, Harry

Harry,

<< Could you give a small example of a SP that populates a table with data
(an x number of records). This way I can complete the SP for creating and
populating a db. >>

Well, there's two ways to populate a table in a SP - via a sensitive (live)
cursor and a series of cursor INSERT statements or via a parameterized
INSERT statement that is prepared and then executed a number of times with
different values.  Which would you like to see ?

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

As I am new to the SP concept: when to use and what are the benefits from
using one or the other? A little example of both would be nice (you can do
it with one table, one field ).

Furthermore I will wait for the build 5 to test my other questions again.
Thanks for your answers.

Regards, Harry

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> schreef in bericht
news:9AE8E551-81DC-49AA-BDD5-4D5C98A8D6B5@news.elevatesoft.com...
> Harry,
>
> << Could you give a small example of a SP that populates a table with data
> (an x number of records). This way I can complete the SP for creating and
> populating a db. >>
>
> Well, there's two ways to populate a table in a SP - via a sensitive
(live)
> cursor and a series of cursor INSERT statements or via a parameterized
> INSERT statement that is prepared and then executed a number of times with
> different values.  Which would you like to see ?
>
> --
> Tim Young
> Elevate Software
> www.elevatesoft.com
>
>

Harry,

<< As I am new to the SP concept: when to use and what are the benefits from
using one or the other? >>

Not much difference really.  One is just a different way from the other.

<< A little example of both would be nice (you can do it with one table, one
field ). >>

Here's an example of a cursor-based set of inserts:

DECLARE Test CURSOR FOR stmt;

PREPARE stmt FROM 'SELECT * FROM customer';

OPEN Test;

INSERT INTO Test (CustNo) VALUES 1000;
INSERT INTO Test (CustNo) VALUES 2000;

CLOSE Test;
UNPREPARE stmt;

If you wanted to go ahead and return the Test cursor from the SP, just use
this in the declaration and don't CLOSE it:

DECLARE Test CURSOR WITH RETURN FOR stmt;

Here's an example of using a prepared parameterized INSERT statement:

DECLARE stmt STATEMENT;

PREPARE stmt FROM 'INSERT INTO Customer (CustNo) VALUES ?';

EXECUTE stmt USING 1000;
EXECUTE stmt USING 2000;

UNPREPARE stmt;

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

Thank you for that. One more question and then I will test all that I
learned

How do I construct in a SP something like:

for i = 1 to 10000 do
 INSERT INTO Test (CustNo, CustName, ThisMoment) VALUES i, 'Name' +
IntToStr(i), Now() ;

An example would sure help generating data for my tests.

As I understand  build 5 contains reverse engineer to SP, thanks for that.
I'm still having plans on converting Ole's Northwind db to EDB format (if I
can spare the time), or is something like that already in the pipeline?

Regards, Harry


,
"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> schreef in bericht
news:44B5B928-31F7-42C8-9258-55FFB5418982@news.elevatesoft.com...
> Harry,
>
> << As I am new to the SP concept: when to use and what are the benefits
from
> using one or the other? >>
>
> Not much difference really.  One is just a different way from the other.
>
> << A little example of both would be nice (you can do it with one table,
one
> field ). >>
>
> Here's an example of a cursor-based set of inserts:
>
> DECLARE Test CURSOR FOR stmt;
>
> PREPARE stmt FROM 'SELECT * FROM customer';
>
> OPEN Test;
>
> INSERT INTO Test (CustNo) VALUES 1000;
> INSERT INTO Test (CustNo) VALUES 2000;
>
> CLOSE Test;
> UNPREPARE stmt;
>
> If you wanted to go ahead and return the Test cursor from the SP, just use
> this in the declaration and don't CLOSE it:
>
> DECLARE Test CURSOR WITH RETURN FOR stmt;
>
> Here's an example of using a prepared parameterized INSERT statement:
>
> DECLARE stmt STATEMENT;
>
> PREPARE stmt FROM 'INSERT INTO Customer (CustNo) VALUES ?';
>
> EXECUTE stmt USING 1000;
> EXECUTE stmt USING 2000;
>
> UNPREPARE stmt;
>
> --
> Tim Young
> Elevate Software
> www.elevatesoft.com
>
>

Harry,

<< How do I construct in a SP something like:

for i = 1 to 10000 do
 INSERT INTO Test (CustNo, CustName, ThisMoment) VALUES i, 'Name'
+IntToStr(i), Now() ;

An example would sure help generating data for my tests. >>

Here you go:

DECLARE Test CURSOR FOR stmt;
DECLARE I INTEGER DEFAULT 1;

PREPARE stmt FROM 'SELECT * FROM Test';

OPEN Test;

WHILE I <= 10000 DO
  INSERT INTO Test (CustNo, CustName, ThisMoment) VALUES I, 'Name' + CAST(I
AS VARCHAR(10)), CURRENT_TIMESTAMP;
  SET I=I+1;
END WHILE;

CLOSE Test;

<< As I understand  build 5 contains reverse engineer to SP, thanks for
that. I'm still having plans on converting Ole's Northwind db to EDB format
(if I can spare the time), or is something like that already in the
pipeline? >>

Any example databases we distribute won't be Northwind, but we will have
something.

--
Tim Young
Elevate Software
www.elevatesoft.com

Thanks Tim!

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> schreef in bericht
news:6294251C-0768-4B00-9058-DE89A20BDF5F@news.elevatesoft.com...
> Harry,
>
> << How do I construct in a SP something like:
>
>  for i = 1 to 10000 do
>   INSERT INTO Test (CustNo, CustName, ThisMoment) VALUES i, 'Name'
> +IntToStr(i), Now() ;
>
>  An example would sure help generating data for my tests. >>
>
> Here you go:
>
> DECLARE Test CURSOR FOR stmt;
> DECLARE I INTEGER DEFAULT 1;
>
> PREPARE stmt FROM 'SELECT * FROM Test';
>
> OPEN Test;
>
> WHILE I <= 10000 DO
>    INSERT INTO Test (CustNo, CustName, ThisMoment) VALUES I, 'Name' +
CAST(I
> AS VARCHAR(10)), CURRENT_TIMESTAMP;
>    SET I=I+1;
> END WHILE;
>
> CLOSE Test;
>
> << As I understand  build 5 contains reverse engineer to SP, thanks for
> that. I'm still having plans on converting Ole's Northwind db to EDB
format
> (if I can spare the time), or is something like that already in the
> pipeline? >>
>
> Any example databases we distribute won't be Northwind, but we will have
> something.
>
> --
> Tim Young
> Elevate Software
> www.elevatesoft.com
>
>

Tim,

The VALUES also has to be inside () or an error occurs.

Regards, Harry

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> schreef in bericht
news:6294251C-0768-4B00-9058-DE89A20BDF5F@news.elevatesoft.com...
> Harry,
>
> << How do I construct in a SP something like:
>
>  for i = 1 to 10000 do
>   INSERT INTO Test (CustNo, CustName, ThisMoment) VALUES i, 'Name'
> +IntToStr(i), Now() ;
>
>  An example would sure help generating data for my tests. >>
>
> Here you go:
>
> DECLARE Test CURSOR FOR stmt;
> DECLARE I INTEGER DEFAULT 1;
>
> PREPARE stmt FROM 'SELECT * FROM Test';
>
> OPEN Test;
>
> WHILE I <= 10000 DO
>    INSERT INTO Test (CustNo, CustName, ThisMoment) VALUES I, 'Name' +
CAST(I
> AS VARCHAR(10)), CURRENT_TIMESTAMP;
>    SET I=I+1;
> END WHILE;
>
> CLOSE Test;
>
> << As I understand  build 5 contains reverse engineer to SP, thanks for
> that. I'm still having plans on converting Ole's Northwind db to EDB
format
> (if I can spare the time), or is something like that already in the
> pipeline? >>
>
> Any example databases we distribute won't be Northwind, but we will have
> something.
>
> --
> Tim Young
> Elevate Software
> www.elevatesoft.com
>
>

Harry,

<< The VALUES also has to be inside () or an error occurs. >>

Yep, sorry about that.

--
Tim Young
Elevate Software
www.elevatesoft.com