on 2012 May 14 6:22 PM
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
Try this:
myConnTM.Execute "if exists (select 1 from sysobjects where name= 'mytable' ) drop table mytable"
Changes
BTW Is this a SQL Anywhere question or an Adaptive Server Enterprise question? The syntax changes were tested in SQL Anywhere.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
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;
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...
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
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
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
69 | |
10 | |
9 | |
6 | |
6 | |
6 | |
6 | |
5 | |
5 | |
5 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.