Re: Proper query to create table

Sorry Igor if I wasn't clear.

I ran this from SAP's isql as your original e-mail seemed to me to be more a problem with getting the command right than FreeTDS.

So, from isql and now on ASE 16 the following works (adopting more your syntax):

if not exists (select 1 from sysobjects where name = "test" and type = "U")
    exec("create table test (test int not null)")

if not exists (select 1 from sysindexes where id = object_id("test") and name = "I1")
     exec("create index I1 on test (test)")

I ran it 20 times, obviously the first time when the table and index don't exist and the following ones when it does :-)

I just tried with bsqldb from FreeTDS and it works as expected, the first time creates the table and index and the following executions do nothing and give no errors.

Cheers,

Matt.

July 23, 2018 5:20 PM, "Igor Korot" <[email protected]> wrote:

> Hi, Matthew,
> 
> On Mon, Jul 23, 2018 at 9:31 AM, <[email protected]> wrote:
> 
>> This works for me on 15.7:
>> 
>> create table test (test int not null)
>> 
>> declare @i varchar(8000)
>> 
>> select @i = "create index I1 on test (test)"
>> 
>> if not exists (select 1 from sysindexes where id = object_id("test") and name = "I1")
>> exec(@i)
> 
> How many times did you run it?
> When I tried to execute:
> 
> IF NOT EXISTS(SELECT o.name, i.name FROM tempdb..sysobjects o,
> tempdb..sysindexes i WHERE o.id = i.id AND o.name='abcattbl' AND
> i.name='abcattbl_tnam_ownr') EXECUTE("CREATE INDEX abcattbl_tnam_ownr
> ON abcattbl(abt_tnam ASC, abt_ownr ASC)")
> 
> when the index already been created, I got:
> 
> "Could not SQLExecute"
> 
> And I have Sybase 16.0 installed on Linux Gentoo.
> Tried the query on isql from unixODBC w/freeTDS driver.
> 
> Thank you.
> 
>> Cheers,
>> 
>> Matt.
>> 
>> July 23, 2018 3:02 PM, "Igor Korot" <[email protected]> wrote:
>> 
>>> And following statement also fails:
>>> 
>>> IF NOT EXISTS(SELECT o.name, i.name FROM tempdb..sysobjects o,
>>> tempdb..sysindexes i WHERE o.id = i.id AND o.name='abcattbl' AND
>>> i.name='abcattbl_tnam_ownr') EXECUTE("CREATE INDEX abcattbl_tnam_ownr
>>> ON abcattbl(abt_tnam ASC, abt_ownr ASC)")
>>> 
>>> "Could not SQLExecute"
>>> 
>>> Log says "Index already exists"
>>> 
>>> Thank you.
>>> 
>>> On Mon, Jul 23, 2018 at 5:57 AM, Igor Korot <[email protected]> wrote:
>> 
>> Hi,
>> 
>> On Mon, Jul 23, 2018 at 2:48 AM, Frediano Ziglio <[email protected]> wrote:
>> 2018-07-22 7:57 GMT+01:00 Igor Korot <[email protected]>:
>> 
>> Hi, ALL,
>> I'm trying to execute following query:
>> 
>> query1 = L"IF NOT EXISTS(SELECT 1 FROM sysobjects WHERE name =
>> 'abcatcol' AND type = 'U') CREATE TABLE abcatcol( <field_list>))";
>> 
>> Unfortunately it fails.
>> If the table does not exist I'm getting 100 (SQL_NO_DATA) from the ODBC
>> driver.
>> And if the table does exist, I'm getting -1 with the error:
>> 
>> "The table already exists".
>> 
>> Is there a way to do this in 1 shot? Or I will have to do a conditional
>> for Sybase to make it in 2 queries?
>> 
>> Please advise.
>> 
>> Thank you.
>> This works for mssql:
>> 
>> if object_id('test123') is null create table test123(i int)
>> 
>> What about this one:
>> 
>> CREATE INDEX IF NOT EXISTS abcattbl_tnam_ownr ON abcattbl(\"abt_tnam\"
>> ASC, \"abt_ownr\" ASC)
>> 
>> ?
>> 
>> It is on Sybase 16.
>> It says "Syntax error on the 'IF'".
>> 
>> Thank you.
>> 
>> Frediano
>> _______________________________________________
>> FreeTDS mailing list
>> [email protected]
>> https://lists.ibiblio.org/mailman/listinfo/freetds
>>> _______________________________________________
>>> FreeTDS mailing list
>>> [email protected]
>>> https://lists.ibiblio.org/mailman/listinfo/freetds
>> 
>> _______________________________________________
>> FreeTDS mailing list
>> [email protected]
>> https://lists.ibiblio.org/mailman/listinfo/freetds
> 
> _______________________________________________
> FreeTDS mailing list
> [email protected]
> https://lists.ibiblio.org/mailman/listinfo/freetds