• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 526
  • Last Modified:

Check for tables

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
agvkumar
Asked:
agvkumar
  • 6
  • 4
1 Solution
 
jbiswasCommented:
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
 
agvkumarAuthor Commented:
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
 
agvkumarAuthor Commented:
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
Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

 
jbiswasCommented:
Need more points for the stored proc.
0
 
jbiswasCommented:
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
 
jbiswasCommented:
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
 
agvkumarAuthor Commented:
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
 
jbiswasCommented:
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
 
jbiswasCommented:
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
 
agvkumarAuthor Commented:
Thank you for the help and here are the rightly deserved points with Excellent grade.

Again Thank you

0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now