FreeTDS Development Group

[Igor Korot <ikorot01 AT gmail.com>]

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".

>
> 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.

OK. Thank you for testing.
I will check it later tonight and report back.

But if this works in the Sybase own isql, it should work on ODBC one.


>
> 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