FreeTDS Development Group

[matthew.green AT datamartcomputing.com]

Hi Igor,

object_id in ASE can take the owner name too, so use object_id("foo.test"), 
in fact you can specify the DB name first too if you want.

Or you can use uid from sysobjects to do the same thing.

As far as I remember object_id, if you don't specify an owner will return the 
"test" owned by you (so foo.test if you're logged in as foo) in preference to 
the one owned by dbo.

Or, to put it like this, let's say there's foo.test and dbo.test. If you're 
logged in as foo then object_id("foo") will return the id of foo.test. If 
you're logged in as another user then you'll get the id of dbo.test (unless 
that user has also created its own test!).

Hope that clarifies things.

Cheers,

Matt.

July 25, 2018 5:30 PM, "Igor Korot" <ikorot01 AT gmail.com> wrote:

> Hi, Matthew,
> 
> On Wed, Jul 25, 2018 at 2:37 AM, <matthew.green AT datamartcomputing.com> 
> wrote:
> 
>> 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
> 
> Consider this scenario:
> 
> CREATE TABLE foo.test(<field_list>)
> GO
> CREATE TABLE bar.test(<field_list>)
> GO
> IF NOT EXCISTS (SELECT 1 FROM sysindexes WHERE id = object_id("test")
> AND name = "I1")
> EXECUTE("CREATE INDEX I1 ON test (test)")
> 
> What will happen in this case?
> How the server determine which test to use for verification?
> And what if I add:
> 
> CREATE TABLE dbo.test(<field_list>)
> GO
> 
> ?
> 
> Thank you.
> 
>> 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
>> 
>> _______________________________________________
>> 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