Link to home
Start Free TrialLog in
Avatar of agvkumar
agvkumar

asked on

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.
Avatar of jbiswas
jbiswas

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..
Avatar of agvkumar

ASKER

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.
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.
Need more points for the stored proc.
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.
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.
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.
ASKER CERTIFIED SOLUTION
Avatar of jbiswas
jbiswas

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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)
Thank you for the help and here are the rightly deserved points with Excellent grade.

Again Thank you