473,573 Members | 3,524 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

problems creating tables inside a stored procedure

1 New Member
I am generating upgrade/new install scripts for my project. I am able to do this in oracle and sql server but an running into problems in db2. What I am trying to do is to use some logic to only create table that have not been generated in prevous releases. I am trying to create a stored procedure to do this.
I am not able to create the stored procedure because it errors out with the error below. I am stumped as how to get around this error because the errorr is happening when I am creating the stored procedure and not when I am executing.

ERROR [42710] [IBM][DB2/NT] SQL0601N The name of the object to be created is identical to the existing name "MIKEBO.TBLROLE S" of type "TABLE". LINE NUMBER=11. SQLSTATE=42710

here is the base test procedure with one table

CREATE PROCEDURE CREATETABLE
(
)
SPECIFIC CreateTable
LANGUAGE SQL
BEGIN
DECLARE table_count INTEGER 0;

select COUNT(*) INTO table_count from syscat.tables where tabname = 'TBLROLES';
IF( table_count=0) THEN
CREATE TABLE "TBLROLES" (
"ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (
START WITH +1
INCREMENT BY +1
MINVALUE +1
MAXVALUE +2147483647
NO CYCLE
CACHE 20
NO ORDER ) ,
"ROLENAME" VARGRAPHIC(10) NOT NULL ,
"AUTHDESCRIPTIO N" VARGRAPHIC(50),
PRIMARY KEY ("ID"),
CONSTRAINT "IX_TBLROLE S" UNIQUE ("ROLENAME") )
IN "USERSPACE1 " ;
END IF;
END;
Feb 15 '07 #1
1 6343
mersis
2 New Member
I am having exactly the same problem. I need to drop and recreate same stored procedure from time to time. And in the stored procedure I test if a table does not exist I create it. If it does, I drop it and then create a new one. But when I try to recreate the stored proc second time after some tables have already been created by the previous stored proc, creation fails.

Did you find a way to turn that stupid check (that is unable to see that I am actually droping the tables before creating them if they exist) to make the creation of a stored to continue?

Would be great to hear if you have solved this problem somehow (other than using dynamic SQL, which is very ugly).

Thanks

I am generating upgrade/new install scripts for my project. I am able to do this in oracle and sql server but an running into problems in db2. What I am trying to do is to use some logic to only create table that have not been generated in prevous releases. I am trying to create a stored procedure to do this.
I am not able to create the stored procedure because it errors out with the error below. I am stumped as how to get around this error because the errorr is happening when I am creating the stored procedure and not when I am executing.

ERROR [42710] [IBM][DB2/NT] SQL0601N The name of the object to be created is identical to the existing name "MIKEBO.TBLROLE S" of type "TABLE". LINE NUMBER=11. SQLSTATE=42710

here is the base test procedure with one table

CREATE PROCEDURE CREATETABLE
(
)
SPECIFIC CreateTable
LANGUAGE SQL
BEGIN
DECLARE table_count INTEGER 0;

select COUNT(*) INTO table_count from syscat.tables where tabname = 'TBLROLES';
IF( table_count=0) THEN
CREATE TABLE "TBLROLES" (
"ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (
START WITH +1
INCREMENT BY +1
MINVALUE +1
MAXVALUE +2147483647
NO CYCLE
CACHE 20
NO ORDER ) ,
"ROLENAME" VARGRAPHIC(10) NOT NULL ,
"AUTHDESCRIPTIO N" VARGRAPHIC(50),
PRIMARY KEY ("ID"),
CONSTRAINT "IX_TBLROLE S" UNIQUE ("ROLENAME") )
IN "USERSPACE1 " ;
END IF;
END;
Jun 22 '07 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

5
6043
by: Lili | last post by:
I'm having problems creating a simple stored procedure in DB2. Can someone help? Here is the screen dump when I tried to load the stored procedure. Thanks for any help. Create procedure update_salary (in emp_number char(6), in rate integer) language sql begin update employee
1
1798
by: Paul | last post by:
Hi, I wish to be able to add tables to a sql server database at runtime from my asp.net application. As well as creating fields I also wish to be able to create indexes on selected fields and to assign user permissions. (I need to assign permissions to the table object as I will be using sp_executesql or exec to execute a string). I'm...
8
1930
by: Mauricio | last post by:
Hello, Currently we have an ASP.NET 2003 app running, on one function the app calls to a stored procedure to SQLServerONE, that stored procedure creates some TEMP tables with the results of a stored procedure that is remotely called con SQLServerTWO that generates TEMP tables that are used to return results. When we begin stress-testing...
2
1737
by: =?Utf-8?B?SXbDoW4gU2Fpbno=?= | last post by:
I ran the aspnet_regsql.exe tool with the -sqlexportonly option to create the script. I searched and replace the string by and had the dba's run the script. When I do anything like Membership.CreateUser or click on the 'security' tab in the website admin tool I get... Could not find stored procedure 'dbo.aspnet_CheckSchemaVersion'. I think...
9
9301
by: Ots | last post by:
I'm using SQL 2000, which is integrated with a VB.NET 2003 app. I have an Audit trigger that logs changes to tables. I want to apply this trigger to many different tables. It's the same trigger, with the exception of the table name. I could manually change the table name in the trigger and create it, over and over, but I'd like to automate...
1
3031
by: birundha | last post by:
Hi Hope this will help more peolpe... We got a new design where we use Global temporary tables(GTT) inside stored procedure... My question is can we use GTT inside a stored procedure and insert values in to it? Can we fetch multiple rows from that GTT and return it from DB2(Note: My stored procedure is created in DB2)? Please help me out...
1
1065
by: Roobmeister | last post by:
I have a script that builds multiple tables and then builds tables from those tables, etc.. Usually, I run the script as a DTS package, and it doesn't have any problems. However, when I save the script as a stored procedure, I think it is compiling the table builds into a different sequence. As a result, some of the tables are blank when...
5
4066
by: Bogdan | last post by:
Hi, I have a stored procedure that uses JOINs to return columns from multiple tables. I also have another stored proc that that takes a series of params and updates multiple tables. I used the framework to auto-generate a table adapter specifying both stored procs as Get/Fill and Update. The problem is that columns from the JOINed table...
2
1757
by: Andrew Cooper | last post by:
Greetings, I'm creating a website using ASP.NET. In creating my DAL I've got a Table Adapter that I've set up to use an existing Stored Procedure from an SQL Server 2000 database. However, when I select the stored procedure I want to use the fields that are returned do not show up in the listbox for that stored procedure. If I continue...
0
7789
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7707
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8215
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7800
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
6432
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5605
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5296
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
1
1325
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
1048
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.