Hi, I am looking at this too many hours again. In Microsoft SQL via vb6 front end.

I use: db.Execute "if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BuildFileAllOthersCDC]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)" & _ "drop table [dbo].[BuildFileAllOthersCDC]"

In Sybase with the same VB program. I am trying to use: myConnTM.Execute "if exists (select * from sysobjects where name=" 'mytable' " = 1) drop table 'mytable'"

I have tried all froms of this, all I can think of, does the "if exists" work with SYBASE SQL? I get an error, "Syntax error near if line one" Will trade gray hairs for suggestions... smile

George

asked 14 May '12, 17:22

George's gravatar image

George
165101017
accept rate: 0%


Try this:

myConnTM.Execute "if exists (select 1 from sysobjects where name= 'mytable' ) drop table mytable"

Changes

  • Fixed the name predicate which read " 'mytable' ". Should be 'mytable' without additional quotes.
  • Removed the = 1.
  • Changed the select list to SELECT 1 from SELECT *.
  • Changed DROP statement to DROP TABLE mytable.

BTW Is this a SQL Anywhere question or an Adaptive Server Enterprise question? The syntax changes were tested in SQL Anywhere.

permanent link

answered 14 May '12, 19:50

Chris%20Keating's gravatar image

Chris Keating
3.2k1956
accept rate: 29%

SQLAnywhere. Here is what I am getting to work: myConnATT.Execute "if exists (select 1 from autotiretech..sysobjects where name= 'EMAIL_LIST' ) drop table EMAIL_LIST" myConnATT.Execute "create table EMAIL_LIST (""Name"" Char)" Notice the double quotes on the column name. I can not get single quote to work for some reason. Can you point me to the values of the '1' after the select statement. I am new to SQLA so some of this takes me a few more hours then it should... smile.

(14 May '12, 23:24) George
Replies hidden

As to the "if exists (...)" vs. " = 1" tests:

EXISTS

The existence test (EXISTS) checks whether a subquery produces any rows of query results. If the subquery produces one or more rows of results, the EXISTS test returns TRUE. Otherwise, it returns FALSE.

That's what you're about here: You simply want to check if there is at least one row for that table in the system catalog.

The "= 1" would be useful if you would count the number of rows, i.e. for something like:

if (select count(*) from sysobjects where name= 'mytable') = 1 then ... end if;

(15 May '12, 03:27) Volker Barth

BTW: SQL Anywhere has two different SQL dialects for SQL batches, stored procedures and the like: Watcom SQL and Transact SQL (T-SQL), the latter is mostly similar to the dialects of ASE and MS SQL Server. The doc tell the differences en detail here - you just should be aware that one SQL batch should be written in one dialect...

(15 May '12, 03:34) Volker Barth

I don't have VB6 installed on my machine at this time but the statement

myConnATT.Execute "create table EMAIL_LIST (""Name"" Char)"

appears to be written correctly. You can also use [NAME] to delimit the identifier name. This assumes that your quoted_identifier database option is set to ON. See http://dcx.sybase.com/index.html#1201/en/dbadmin/quoted-identifier-option.html.

Or you could write it as

myConnATT.Execute "create table EMAIL_LIST (" & char(34) & "Name & char(34) &" Char)"

For troubleshooting statements, I build the value into a string variable and display it in a messagebox or an edit box to see what the value actually looks like.

For example (unchecked syntax but I think this is mostly correct)

dim stmt as String stmt = "create table EMAIL_LIST (""Name"" Char)" msgbox stmt tb.Text = stmt

(15 May '12, 09:10) Chris Keating

Thanks, I have this working/understood. I need to do it across two DB's, both SQLAnywhere. Will : Location work on SQLA 10?

INSERT INTO iq_table LOCATION 'ase_servername.ase_dbname' { SELECT col1, col2, col3,... FROM owner.ase_table }

What I am doing is working with a DB that has read only, I need to grab data, put it together and then export it for the customer. So I have a second DB, just a little problem getting the data from on to the other.... smile

(15 May '12, 11:27) George

This will not work in a pure SA environment. In SA, you need to implement remote server and proxy tables to achieve this.

(15 May '12, 11:49) Chris Keating
showing 4 of 6 show all flat view

What version of SQL Anywhere are you using?

Version 11.0.1 and above support the "DROP TABLE IF EXISTS MyTable" syntax, by this omitting the need to check beforehand if the table already exists.

permanent link

answered 15 May '12, 03:19

Volker%20Barth's gravatar image

Volker Barth
31.5k318461676
accept rate: 33%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×52
×21

question asked: 14 May '12, 17:22

question was seen: 4,632 times

last updated: 15 May '12, 11:49