Solved

Check for tables

Posted on 1998-02-20
10
510 Views
Last Modified: 2012-05-04
We use powerbuilder as front end for Sybase anywhere.

Please tell me how can I check if a table is existing in the database. Please tell me such that if it does not exist I should be able to trap the error and create a new one. Please suggest a method for this and sample code if possible. 150 points for sample code and 100 for only solution.
0
Comment
Question by:agvkumar
  • 6
  • 4
10 Comments
 
LVL 2

Expert Comment

by:jbiswas
ID: 1098296
I am not a powerbuilder programmer but I know bits and pieces of it.It would be possibly a lot easier if you were to use a stored procedure to do it, and call the stored proc from powerbuilder.I am providing you sample code partly in pseudo-code format which on little modification should enable you to do what you want.You should be able to do this through powerscript, but before executing any DDL statement in powerbuilder you should set autocommit to true.
select * from sysobjects where name='employee';
if SQLDBCODE =100(not found) then
(string Mysql
Mysql = "CREATE TABLE Employee "&
      +"(emp_id integer not null,"&
      +"dept_id integer not null, "&
      +"emp_fname char(10) not null, "&
      +"emp_lname char(20) not null)"
EXECUTE IMMEDIATE :Mysql ;

This statement assumes a transaction object named My_trans exists and is connected.
If you need to insert data to this table use:
string      Mysql
Mysql="INSERT INTO dept Values (1234, 'Purchasing')"
EXECUTE IMMEDIATE :Mysql USING My_trans ;

After transaction is over set autocommit to false.

On using this with proper syntax you should be able to execute the statement, otherwise e-mail me..
0
 

Author Comment

by:agvkumar
ID: 1098297
Please tell me if SQLDBCODE is a powerbuilder variable or the Database variable which is independenet of the vendor. We have plans to port it across oracle. If it is diff in Oracle give a tip and also please provide you mail address for further query.
0
 

Author Comment

by:agvkumar
ID: 1098298
Yes I was successful with your code. With little adjustments with the syntax I was successfull and happy. Please give me the code for a stored procedure and a sample statement to call it.

Sorry for dening the points to you. They will me released immediatly.
0
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 
LVL 2

Expert Comment

by:jbiswas
ID: 1098299
Need more points for the stored proc.
0
 
LVL 2

Expert Comment

by:jbiswas
ID: 1098300
By the way SQLDBCODE is a property of a trasaction object. It is specific for a database vendor
SQLCode      Long      The success or failure code of the most recent operation.
Return codes:    0 — Success100 — Not Found   -1 — Error (use SQLDBCode or SQLErrText to obtain the details)
SQLDBCode      Long      The database vendor's error code.
0
 
LVL 2

Expert Comment

by:jbiswas
ID: 1098301
By the way SQLDBCODE is a property of a trasaction object. It is specific for a database vendor
SQLCode      Long      The success or failure code of the most recent operation.
Return codes:    0 — Success100 — Not Found   -1 — Error (use SQLDBCode or SQLErrText to obtain the details)
SQLDBCode      Long      The database vendor's error code.
0
 

Author Comment

by:agvkumar
ID: 1098302
Yes , I have increased the points. Please give me the stored proc
and also please show how to read the return value of (1/0) from the stored proc.
  I know to execute stored procs but do not know how to receive the results.
0
 
LVL 2

Accepted Solution

by:
jbiswas earned 150 total points
ID: 1098303
Here is a script which will enable you to do that.
 create procedure "dba".existance_chk(@tablename varchar(50))
as
begin
  if exists(select 1 from sysobjects
      where "name"=@tablename)
    return 1
  else
    return 0
end

 If you call this procedure fron isql you will not see any results. You can see something by typing this:
if ( existance_chk ('locations') = 1)
      select 'exists'
where I'm checking for the table locations!
  Now to call this from Powerbuilder I think this can do it(but I'm not a powerbuilder person) so I'm not 100%

varchar(50) val = 'locations'
bit rv
rv = SQLCA.give_raise(val)




0
 
LVL 2

Expert Comment

by:jbiswas
ID: 1098304
I did a typo in the answer for the part about calling the procedure from Powerbuilder

varchar(50) val = 'locations'
bit rv
rv = SQLCA.existance_chk(val)
0
 

Author Comment

by:agvkumar
ID: 1098305
Thank you for the help and here are the rightly deserved points with Excellent grade.

Again Thank you

0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you’re making plans to join the modern business race, you should analyze various details that may affect your results. Nowadays, millions of businesses are trying to grow into established and appreciated professional enterprises.
When we purchase storage, we typically are advertised storage of 500GB, 1TB, 2TB and so on. However, when you actually install it into your computer, your 500GB HDD will actually show up as 465GB. Why? It has to do with the way people and computers…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now