Results 1 to 14 of 14
  1. #1
    Join Date
    Jan 2005
    Posts
    5

    Unanswered: Unsigned int - how?

    In MySQL you can define a unsigned integer as uint
    But MS don't allow this. Only as int.
    How can I define a column/field as a unsigned int ?

    CREATE TABLE StringTable (
    NDX uint IDENTITY(1,1),
    MID varchar(81),
    TableTXT varchar(65),
    TableNDX uint,
    TXT1 varchar(129),
    TXT2 varchar(129),
    UNCDateTime datetime,
    Data image)
    -1 42000 2715 [Microsoft][ODBC SQL Server Driver][SQL Server]Column or
    parameter #1: Cannot find data type uint.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what's wrong with integer? why does it have to be unsigned?

    how many rows will the table have to hold?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2003
    Location
    @ home
    Posts
    163
    Just take the ABS of the number and you will get the unsigned.

    Paulo

  4. #4
    Join Date
    Jan 2005
    Posts
    5
    Quote Originally Posted by r937
    what's wrong with integer? why does it have to be unsigned?

    how many rows will the table have to hold?
    The question is not about the number of rows,
    but how I can define a column as 32 bits unsigned int value.
    0 - 4294967295
    So I can't to store the value 4294967290 to a MS SQL server?
    Must I use a 64 bits type - prefer not.
    This works fint in MySQL, P.SQL, Oracle(?) and many other
    vell known SQL engines - but not MS SQL ? Realy ?

    My program using ODBC have a common SQL interface.
    When doing a lookup after the number 4294967290 in a
    MS SQL 2000 table I revcieve the error code:

    SQLExecDirect [SELECT NDX,ConType FROM ContactTable WHERE NDX=4294967290] -1 22003 8115 [Microsoft][ODBC SQL Server Driver][SQL Server]Arithmetic overflow error converting numeric to data type int.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if you are trying to use the unsigned integer as an IDENTITY column, then it most certainly is about the number of rows

    use BIGINT
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Oct 2003
    Posts
    268
    Quote Originally Posted by GTi
    How can I define a column/field as a unsigned int ?
    I don't think you can do this in SQL Server. If you need to exceed 2^31, you can use BIGINT.

  7. #7
    Join Date
    Jan 2005
    Posts
    5
    Quote Originally Posted by RogerWilco
    I don't think you can do this in SQL Server. If you need to exceed 2^31, you can use BIGINT.
    Ok... but MSDE 2000 dosn't support BIGINT.
    Now what?

    CREATE TABLE AccessRightsTable (
    NDX bigint IDENTITY(1,1),
    UserNDX bigint,
    Module varchar(129),
    Function varchar(129),
    SubFunction varchar(254),
    AccessLevel bigint)
    -1 42000 2715 [Microsoft][ODBC SQL Server Driver][SQL Server]
    Column or parameter #1: Cannot find data type bigint.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by GTi
    Ok... but MSDE 2000 dosn't support BIGINT.
    Now what?
    now what? now you go and re-install MSDE, because it works fine on my copy

    create table testbigint
    ( id tinyint not null primary key
    , foo varchar(9)
    , myint integer
    , mybigint bigint
    )
    insert into testbigint values (1,'FOO1',1,1)
    insert into testbigint values (2,'FOO2',2000000000,2000000000000)

    select * from testbigint

    1 FOO1 1 1
    2 FOO2 2000000000 2000000000000
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    CREATE TABLE AccessRightsTable (
    NDX bigint IDENTITY(1,1),
    UserNDX bigint,
    Module varchar(129),
    Funcshin varchar(129),
    SubFunction varchar(254),
    AccessLevel bigint)

    insert into AccessRightsTable (UserNDX,Module,Funcshin,SubFunction,AccessLevel)
    values (2111222333444,'foo','bar','fap',98765432198765)

    select * from AccessRightsTable

    1 2111222333444 foo bar fap 98765432198765

    see? it works!

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Jan 2005
    Posts
    5

    Red face

    Quote Originally Posted by r937
    CREATE TABLE AccessRightsTable (
    NDX bigint IDENTITY(1,1),
    UserNDX bigint,
    Module varchar(129),
    Funcshin varchar(129),
    SubFunction varchar(254),
    AccessLevel bigint)

    insert into AccessRightsTable (UserNDX,Module,Funcshin,SubFunction,AccessLevel)
    values (2111222333444,'foo','bar','fap',98765432198765)

    select * from AccessRightsTable

    1 2111222333444 foo bar fap 98765432198765

    see? it works!

    Ok then ...
    But I just installed Microsoft Visual Studio 2003 and MSDE.
    But I will try to download it again (didn't find it at on one of the MSDN CD )

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    dude, i was only kidding about reinstalling it

    you probably have something else wrong with the query

    like, the use of the reserved word Function as a column name
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    If you just cast the uint to an int16 at the source, that sidesteps the problem. It introduces the nasty side effect of having different high level values for the same bit pattern (which are due to changing from uint to int16), but at least it allows you to store the data easily in normal int columns.

    This functionalit is provided implicitly in older SQL libraries, and I believe it is still supported, even though it has been many years since I've had any need for it.

    -PatP

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    pat, can you please explain how to store the value 9,888,777,666,555 "easily" into a normal int column

    and for those of us without the experience, what is "int16"?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Jan 2005
    Posts
    5
    Quote Originally Posted by r937
    dude, i was only kidding about reinstalling it

    you probably have something else wrong with the query

    like, the use of the reserved word Function as a column name
    I only did what you told me to do (no brain - no headache)
    But you was right. it works now.
    Using create table
    And in the Microsoft Development Enviroment 2003 BIGINT is now listed as available field type.

Posting Permissions

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