Skip to Content.
Sympa Menu

freetds - Re: [freetds] Proper query to create table

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: matthew.green AT datamartcomputing.com
  • To: "FreeTDS Development Group" <freetds AT lists.ibiblio.org>
  • Subject: Re: [freetds] Proper query to create table
  • Date: Wed, 25 Jul 2018 07:37:02 +0000

Hi Igor,

Glad you got it sorted :-)

I'm afraid I don't quite understand your follow up question about
schema/catalogue.

Usually people use this kind of format for adding tables/indexes:

If exists (....
drop table/index
go
create table/index
go

Rarely, in my experience but sometimes they may wish to only create the
table/index if it doesn't already exist but that's already covered by the
examples we've exchanged.

Which leads me to think that I'm not correctly understanding your question :-)

Cheers,

Matt.

July 24, 2018 7:44 PM, "Igor Korot" <ikorot01 AT gmail.com> wrote:

> Hi, Matthew (and list),
>
> On Mon, Jul 23, 2018 at 11:34 AM, Igor Korot <ikorot01 AT gmail.com> wrote:
>
>> Hi, Matthew,
>>
>> On Mon, Jul 23, 2018 at 11:22 AM, <matthew.green AT datamartcomputing.com>
>> wrote:
>>> 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.
>>
>> Yes, you are correct. It is a syntax issue.
>>
>>> 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 :-)
>>
>> That's weird.
>> Does this mean we have a bug in the freeTDS ODBC driver? Frediano?
>>
>> As I said - I ran it from the unixODBC DM and on the second try got
>> "Could not SQLExecute".
>
> After inspecting my code more closely I found I was checking the
> tempdb tables and not the actual ones.
> Stupid me. ;-) I guess I needed a fresh pair of eyes or it was too late.
>
> But now I have a more interesting question.
>
> Is there a way to add catalog/schema to the "SELECT 1 FROM sysindexes..."
> query?
>
> Thank you.
>
>>> Cheers,
>>>
>>> Matt.
>>>
>>> July 23, 2018 5:20 PM, "Igor Korot" <ikorot01 AT gmail.com> wrote:
>>
>> Hi, Matthew,
>>
>> On Mon, Jul 23, 2018 at 9:31 AM, <matthew.green AT datamartcomputing.com>
>> 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" <ikorot01 AT gmail.com> 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 <ikorot01 AT gmail.com> wrote:
>>
>> Hi,
>>
>> On Mon, Jul 23, 2018 at 2:48 AM, Frediano Ziglio <freddy77 AT gmail.com>
>> wrote:
>> 2018-07-22 7:57 GMT+01:00 Igor Korot <ikorot01 AT gmail.com>:
>>
>> 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
>> FreeTDS AT lists.ibiblio.org
>> https://lists.ibiblio.org/mailman/listinfo/freetds
>> _______________________________________________
>> FreeTDS mailing list
>> FreeTDS AT lists.ibiblio.org
>> https://lists.ibiblio.org/mailman/listinfo/freetds
>>
>> _______________________________________________
>> FreeTDS mailing list
>> FreeTDS AT lists.ibiblio.org
>> https://lists.ibiblio.org/mailman/listinfo/freetds
>>
>> _______________________________________________
>> FreeTDS mailing list
>> FreeTDS AT lists.ibiblio.org
>> https://lists.ibiblio.org/mailman/listinfo/freetds
>>> _______________________________________________
>>> FreeTDS mailing list
>>> FreeTDS AT lists.ibiblio.org
>>> https://lists.ibiblio.org/mailman/listinfo/freetds
>
> _______________________________________________
> FreeTDS mailing list
> FreeTDS AT lists.ibiblio.org
> https://lists.ibiblio.org/mailman/listinfo/freetds




Archive powered by MHonArc 2.6.24.

Top of Page