Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Check for tables

Posted on 1998-02-20
10
Medium Priority
?
524 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

 
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 600 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Are you looking for the options available for exporting EDB files to PST? You may be confused as they are different in different Exchange versions. Here, I will discuss some options available.
With so many activities to perform, Exchange administrators are always busy in organizations. If everything, including Exchange Servers, Outlook clients, and Office 365 accounts work without any issues, they can sit and relax. But unfortunately, it…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

636 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