Question Re: Sybase db.execute if exist in vb6 & SQL in Technology Q&A

Sybase db.execute if exist in vb6 & SQL

Former Member — Mon, 14 May 2012 17:22:05 GMT

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

Re: Sybase db.execute if exist in vb6 & SQL

chris_keating — Mon, 14 May 2012 19:50:08 GMT

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.

Re: Sybase db.execute if exist in vb6 & SQL

Former Member — Mon, 14 May 2012 23:24:06 GMT

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.

Re: Sybase db.execute if exist in vb6 & SQL

VolkerBarth — Tue, 15 May 2012 03:19:44 GMT

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.

Re: Sybase db.execute if exist in vb6 & SQL

VolkerBarth — Tue, 15 May 2012 03:27:45 GMT

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;

Re: Sybase db.execute if exist in vb6 & SQL

VolkerBarth — Tue, 15 May 2012 03:34:59 GMT

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

Re: Sybase db.execute if exist in vb6 & SQL

chris_keating — Tue, 15 May 2012 09:10:44 GMT

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

Re: Sybase db.execute if exist in vb6 & SQL

Former Member — Tue, 15 May 2012 11:27:10 GMT

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

Re: Sybase db.execute if exist in vb6 & SQL

chris_keating — Tue, 15 May 2012 11:49:06 GMT

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