Re: Proper query to create table

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" <[email protected]> wrote:

> Hi, Matthew,
> 
> On Wed, Jul 25, 2018 at 2:37 AM, <[email protected]> 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" <[email protected]> wrote:
>> 
>>> Hi, Matthew (and list),
>>> 
>>> On Mon, Jul 23, 2018 at 11:34 AM, Igor Korot <[email protected]> wrote:
>> 
>> Hi, Matthew,
>> 
>> On Mon, Jul 23, 2018 at 11:22 AM, <[email protected]> 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" <[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
>> _______________________________________________
>> 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