Solved

Check for tables

Posted on 1998-02-20
10
509 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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SyBase SQL Syntax 7 314
SYBASE ASE HA Configuration 8 381
Sybase 2 MS SQL migration/conversion 4 145
Session that filled up my transaction logs 1 39
There are many Password Managers (PM) out there to choose from. PM's can help with your password habits and routines, but they should not be a crutch you rely on too heavily. I also have an article for company/enterprise PM's.
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

911 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

22 Experts available now in Live!

Get 1:1 Help Now