Results 1 to 5 of 5
  1. #1
    Join Date
    May 2011
    Posts
    2

    Unanswered: For a column datatype as int. Sybase is allowing to create default value as varchar?

    Issue occurs in sybase isql,
    scenerio --
    >create table a ( c1 integer,c2 integer); -SUCCESSFUL
    >alter table a add c3 integer default 0; - SUCCESSFUL
    >insert into a(c1,c2) values('1','2'); - SUCCESSFUL
    >alter table CustCareConfig drop c3 - SUCCESSFUL
    >alter table a add c3 integer default '0'; -SUCCESSFUL
    > insert into a(c1,c2) values('3','4'); - Implicit conversion from datatype 'VARCHAR' to 'INT' is not allowed. Use the CONVERT function to run this query. --Error is throwing

    The error can be explaioned more clearly in below sequence.
    1> ALTER TABLE CustCareConfig Add TimeOut int DEFAULT '0'
    2> go
    (7 rows affected)
    1> insert into CustCareConfig (OrganizationID,OrganizationName) values (22,"TEST2")
    2> go
    Msg 257, Level 16, State 1:
    Server 'PPLE_SYB', Line 1:
    Implicit conversion from datatype 'VARCHAR' to 'INT' is not allowed. Use the CONVERT function to run this query.
    1> alter table CustCareConfig drop TimeOut
    2> go
    (7 rows affected)
    1> ALTER TABLE CustCareConfig Add TimeOut int DEFAULT 0
    2> go
    (7 rows affected)
    1> insert into CustCareConfig (OrganizationID,OrganizationName) values (22,"TEST2")
    2> go
    (1 row affected)
    1>


    The same issue will not occur when using oracle, so only issues exists in sybase isql.

    Could you help me with some web links or else why the same issue working in oracle and not in sybase.
    Also suggest the way farward and in your inputs here..

  2. #2
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4
    Charactervalues need to be inserted in a character-clumn surrounded by quotes. Integervalues inserted in an integer-column cannot have quotes.
    Example:
    insert value 4 in an integercolumn = succes
    insert value '4' in an integercolumn = failure
    insert value 'a' in a charactercolumn = succes
    insert value a in a charactercolumn = failure

    As a result, an integercolumn cannot have a charactervalue as default.
    I'm not crazy, I'm an aeroplane!

  3. #3
    Join Date
    May 2011
    Posts
    2

    insert value '4' in an integercolumn is success and not getting any error

    Thanks Martijnvs for your reply,

    but integer datatype allows '' when value is number in '' as like '2' - implicitly it will convert to number and so value will be stored successfully..
    insert value 4 in an integercolumn = succes
    insert value '4' in an integercolumn = success , so why is it success ?

  4. #4
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4
    Hmm..maybe there is a difference in datatype conversion-possibilities between different Sybase versions. I got an error when testing an insert of '4' in an integercolumn.

    I looked in the documentation, and implicit datatype conversions are supported for a number of datatypes, but not for every combination.
    I'm not crazy, I'm an aeroplane!

  5. #5
    Join Date
    Jun 2011
    Posts
    1

    Arrow ERROR while insert -> insert into a(c1,c2) values('1','2')

    hi,
    i am using sybase Adaptive Server Enterprise/15.0.3
    when i run below query.

    create table a ( c1 int,c2 int) --success

    alter table a add c3 int default 0 -- success


    insert into a(c1,c2) values('1','2') --fails
    .
    .
    .
    .
    .

    please check your version..

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •