Re: Proper query to create table

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