Hi, I want to create a stored proc

[llam]

Hi,
I want to create a stored procedure that will create a set of tables depending on the parameter. However, I found that I am having problem to run create table command within create procedure command. eg.

create procedure CreateTable ();
begin
create table t1 (c1 integer);
end;

I have not problem running create procedure without create table command. I can run create table command by itself (ie. no other commands and no semi-colon) without problem. However, it complaint if I put them together.

[Pervasive][ODBC Client Interface][LNA][Pervasive][ODBC Engine Interface][Data Record Manager][SPEng]3: 'create': Syntax error

Ultimately, I would like to do something like this:

create procedure CreateTable (in :strName char(6));
beging
create table strNameA (...);
create table strNameB (...);
...
end;

Anyone can help me finding a solution? thanks!

-- llam

 

[mirtheil]

What version of Pervasive.SQL are you using? I tried it with Pervasive.SQL V8 and it worked fine for me. If you're not using Pervasive.SQL V8, you should be. I know that previous versions (2000i and older) didn't support DDL (Create Table/Create Index/etc) in Stored Procedures. info@mirtheil.com
Custom VB and Btrieve development.
Certified Pervasive Developer
Certified Pervasive Technician

 

[llam]

I am using Pervasive 2000i SP3.
Hmm... sounds like SP3 does not support create table in procedures then. How about SP4? Do you know?
Thanks!

-- llam

 

[llam]

Oh! is there a way to pass command to Data Manager from VB then? like instead of setting up the procedure in pervasive, I do the routine in VB and pass the commands one by one to Pervasive. As I said, doing create table without semi-colon seems fine in Data Manager, if I can pass commands into pervasive, that may work.

-- llam

 

[mirtheil]

In the SQL Data Manager, you can run multiple statements by separating each statement with either a # or a semicolon depending on the setting. The default is the #. In terms of sending a command to the Data manager from VB, that's not possible. You could, though, send SQL statements through VB to the Pervasive ODBC driver.
info@mirtheil.com
Custom VB and Btrieve development.
Certified Pervasive Developer
Certified Pervasive Technician