Thread: SQL TYPE MAP such as SQL_CHAR, SQL_NUMERIC , etc

SQL TYPE MAP such as SQL_CHAR, SQL_NUMERIC , etc

From
Emi Lu
Date:
Hello,

May I know where I can find some online documents about mapping the
integer values to the following SQL types please?

For example, if I have value 1 , so that I know 1 is mapped to SQL_CHAR;
if I have value 3, so that I know 3 is mapped to SQL_NUMERIC.

Thanks a lot,
Emi



        Data Types

  The following data types are supported:

  SQL_CHAR

  SQL_VARCHAR

  SQL_LONGVARCHAR

  SQL_NUMERIC

  SQL_DECIMAL

  SQL_SMALLINT

  SQL_INTEGER

  SQL_REAL

  SQL_FLOAT

  SQL_DOUBLE

  SQL_BIT

  SQL_TINYINT

  SQL_BIGINT

  SQL_BINARY

  SQL_VARBINARY

  SQL_LONGVARBINARY

  SQL_TYPE_DATE

  SQL_TYPE_TIME

  SQL_TYPE_TIMESTAMP

  SQL_INTERVALS (all types)


Re: SQL TYPE MAP such as SQL_CHAR, SQL_NUMERIC , etc

From
Emi Lu
Date:
I am waiting for your clues.

- Emi



> May I know where I can find some online documents about mapping the
> integer values to the following SQL types please?
>
> For example, if I have value 1 , so that I know 1 is mapped to
> SQL_CHAR; if I have value 3, so that I know 3 is mapped to SQL_NUMERIC.
>
>
>        Data Types
>
>  The following data types are supported:
>
>  SQL_CHAR
>
>  SQL_VARCHAR
>
>  SQL_LONGVARCHAR
>
>  SQL_NUMERIC
>
>  SQL_DECIMAL
>
>  SQL_SMALLINT
>
>  SQL_INTEGER
>
>  SQL_REAL
>
>  SQL_FLOAT
>
>  SQL_DOUBLE
>
>  SQL_BIT
>
>  SQL_TINYINT
>
>  SQL_BIGINT
>
>  SQL_BINARY
>
>  SQL_VARBINARY
>
>  SQL_LONGVARBINARY
>
>  SQL_TYPE_DATE
>
>  SQL_TYPE_TIME
>
>  SQL_TYPE_TIMESTAMP
>
>  SQL_INTERVALS (all types)


Re: SQL TYPE MAP such as SQL_CHAR, SQL_NUMERIC , etc

From
Ragnar
Date:
On fim, 2006-02-23 at 15:45 -0500, Emi Lu wrote:
> I am waiting for your clues.

Maybe it is us that need some clues from you.

gnari

>
>
>
> > May I know where I can find some online documents about mapping the
> > integer values to the following SQL types please?
> >
> > For example, if I have value 1 , so that I know 1 is mapped to
> > SQL_CHAR; if I have value 3, so that I know 3 is mapped to SQL_NUMERIC.
> >
> >
> >        Data Types
> >
> >  The following data types are supported:
> >
> >  SQL_CHAR
> >
> >  SQL_VARCHAR
> >
> >  SQL_LONGVARCHAR
> >
> >  SQL_NUMERIC
> >
> >  SQL_DECIMAL
> >
> >  SQL_SMALLINT
> >
> >  SQL_INTEGER
> >
> >  SQL_REAL
> >
> >  SQL_FLOAT
> >
> >  SQL_DOUBLE
> >
> >  SQL_BIT
> >
> >  SQL_TINYINT
> >
> >  SQL_BIGINT
> >
> >  SQL_BINARY
> >
> >  SQL_VARBINARY
> >
> >  SQL_LONGVARBINARY
> >
> >  SQL_TYPE_DATE
> >
> >  SQL_TYPE_TIME
> >
> >  SQL_TYPE_TIMESTAMP
> >
> >  SQL_INTERVALS (all types)
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>                http://archives.postgresql.org
>


Re: SQL TYPE MAP such as SQL_CHAR, SQL_NUMERIC , etc

From
Emi Lu
Date:
>Maybe it is us that need some clues from you.
>
>

We use perl DBI to read table names, column names, and column types from
Oracle rdb 7.3 through ODBC, and then try to create tables into postgresql.

Through perl DBI, we got:


Column Name                     Type  Precision  Scale Nullable?
------------------------------  ----  ---------  ----- ---------

col1                         1     4          0     Yes
col2                         1     4          0     Yes
col3                         1     2          0     Yes
col4                        4     11         0     Yes
col5                        3     4          2     Yes
col6                        93   13         0     Yes
...
...

I'd like to know how to map the integer type value "1, 3, 4, 93, etc" to
SQL_type?




>
>
>>
>>
>>
>>>May I know where I can find some online documents about mapping the
>>>integer values to the following SQL types please?
>>>
>>>For example, if I have value 1 , so that I know 1 is mapped to
>>>SQL_CHAR; if I have value 3, so that I know 3 is mapped to SQL_NUMERIC.
>>>
>>>
>>>       Data Types
>>>
>>> The following data types are supported:
>>>
>>> SQL_CHAR
>>>
>>> SQL_VARCHAR
>>>
>>> SQL_LONGVARCHAR
>>>
>>> SQL_NUMERIC
>>>
>>> SQL_DECIMAL
>>>
>>> SQL_SMALLINT
>>>
>>> SQL_INTEGER
>>>
>>> SQL_REAL
>>>
>>> SQL_FLOAT
>>>
>>> SQL_DOUBLE
>>>
>>> SQL_BIT
>>>
>>> SQL_TINYINT
>>>
>>> SQL_BIGINT
>>>
>>> SQL_BINARY
>>>
>>> SQL_VARBINARY
>>>
>>> SQL_LONGVARBINARY
>>>
>>> SQL_TYPE_DATE
>>>
>>> SQL_TYPE_TIME
>>>
>>> SQL_TYPE_TIMESTAMP
>>>
>>> SQL_INTERVALS (all types)
>>>
>>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 4: Have you searched our list archives?
>>
>>               http://archives.postgresql.org
>>
>>
>>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 1: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly
>
>


Re: SQL TYPE MAP such as SQL_CHAR, SQL_NUMERIC , etc

From
Scott Marlowe
Date:
On Thu, 2006-02-23 at 15:31, Emi Lu wrote:
> >Maybe it is us that need some clues from you.
> >
> >
>
> We use perl DBI to read table names, column names, and column types from
> Oracle rdb 7.3 through ODBC, and then try to create tables into postgresql.
>
> Through perl DBI, we got:
>
>
> Column Name                     Type  Precision  Scale Nullable?
> ------------------------------  ----  ---------  ----- ---------
>
> col1                         1     4          0     Yes
> col2                         1     4          0     Yes
> col3                         1     2          0     Yes
> col4                        4     11         0     Yes
> col5                        3     4          2     Yes
> col6                        93   13         0     Yes
> ...
> ...
>
> I'd like to know how to map the integer type value "1, 3, 4, 93, etc" to
> SQL_type?

Assuming that those type numbers come from Oracle, you got me.  Can you
use some oracle tool to look at the table structure and compare it to
the numbers you get and make a map?

If they're numbers from Oracle, you likely won't get much help here.
Only a few folks here are all that intimate with oracle's inner
workings.  Heck, I use it every day, and I still don't know this kind of
stuff, and hope I never have to.  :)

Re: SQL TYPE MAP such as SQL_CHAR, SQL_NUMERIC , etc

From
Ragnar
Date:
On fim, 2006-02-23 at 16:31 -0500, Emi Lu wrote:

> We use perl DBI to read table names, column names, and column types from
> Oracle rdb 7.3 through ODBC, and then try to create tables into postgresql.
>
> Through perl DBI, we got:
>
>
> Column Name                     Type  Precision  Scale Nullable?
> ------------------------------  ----  ---------  ----- ---------
>
> col1                         1     4          0     Yes
> col2                         1     4          0     Yes
> col3                         1     2          0     Yes
> col4                        4     11         0     Yes
> col5                        3     4          2     Yes
> col6                        93   13         0     Yes
> ...
> ...
>
> I'd like to know how to map the integer type value "1, 3, 4, 93, etc" to
> SQL_type?
>
>
> >>>For example, if I have value 1 , so that I know 1 is mapped to
> >>>SQL_CHAR; if I have value 3, so that I know 3 is mapped to SQL_NUMERIC.
> >>>
> >>>
> >>>       Data Types
> >>>
> >>> The following data types are supported:
> >>>
> >>> SQL_CHAR
> >>>
> >>> SQL_VARCHAR
> >>>
> >>> SQL_LONGVARCHAR
> >>>
> >>> SQL_NUMERIC
> >>>
....


these are not really Oracle type names so I guess these are ODBC type
names, and the mapping you talk of is
maybe some ODBC thing.

in DBD::Oracle found on CPAN I find this nice little SQL to generate
columns listing, which might give you some clues:

    my $Sql = <<"SQL";
SELECT *
  FROM
(
  SELECT /*+ RULE*/
         to_char( NULL )     TABLE_CAT
       , tc.OWNER            TABLE_SCHEM
       , tc.TABLE_NAME       TABLE_NAME
       , tc.COLUMN_NAME      COLUMN_NAME
       , $typecase decode( tc.DATA_TYPE
         , 'MLSLABEL' , -9106
         , 'ROWID'    , -9104
         , 'UROWID'   , -9104
         , 'BFILE'    ,    -4 -- 31?
         , 'LONG RAW' ,    -4
         , 'RAW'      ,    -3
         , 'LONG'     ,    -1
         , 'UNDEFINED',     0
         , 'CHAR'     ,     1
         , 'NCHAR'    ,     1
         , 'NUMBER'   ,     decode( tc.DATA_SCALE, NULL, 8, 3 )
         , 'FLOAT'    ,     8
         , 'VARCHAR2' ,    12
         , 'NVARCHAR2',    12
         , 'BLOB'     ,    30
         , 'CLOB'     ,    40
         , 'NCLOB'    ,    40
         , 'DATE'     ,    93
         , NULL
         ) $typecaseend      DATA_TYPE          -- ...
       , tc.DATA_TYPE        TYPE_NAME          -- std.?
       , decode( tc.DATA_TYPE
         , 'LONG RAW' , 2147483647
         , 'LONG'     , 2147483647
         , 'CLOB'     , 2147483647
         , 'NCLOB'    , 2147483647
         , 'BLOB'     , 2147483647
         , 'BFILE'    , 2147483647
         , 'NUMBER'   , decode( tc.DATA_SCALE
                        , NULL, 126
                        , nvl( tc.DATA_PRECISION, 38 )
                        )
         , 'FLOAT'    , tc.DATA_PRECISION
         , 'DATE'     , 19
         , tc.DATA_LENGTH
         )                   COLUMN_SIZE
       , decode( tc.DATA_TYPE
         , 'LONG RAW' , 2147483647
         , 'LONG'     , 2147483647
         , 'CLOB'     , 2147483647
         , 'NCLOB'    , 2147483647
         , 'BLOB'     , 2147483647
         , 'BFILE'    , 2147483647
         , 'NUMBER'   , nvl( tc.DATA_PRECISION, 38 ) + 2
         , 'FLOAT'    ,  8 -- ?
         , 'DATE'     , 16
         , tc.DATA_LENGTH
         )                   BUFFER_LENGTH
       , decode( tc.DATA_TYPE
         , 'DATE'     ,  0
         , tc.DATA_SCALE
         )                   DECIMAL_DIGITS     -- ...
       , decode( tc.DATA_TYPE
         , 'FLOAT'    ,  2
         , 'NUMBER'   ,  decode( tc.DATA_SCALE, NULL, 2, 10 )
         , NULL
         )                   NUM_PREC_RADIX
       , decode( tc.NULLABLE
         , 'Y'        ,  1
         , 'N'        ,  0
         , NULL
         )                   NULLABLE
       , cc.COMMENTS         REMARKS
       , tc.DATA_DEFAULT     COLUMN_DEF         -- Column is LONG!
       , decode( tc.DATA_TYPE
         , 'MLSLABEL' , -9106
         , 'ROWID'    , -9104
         , 'UROWID'   , -9104
         , 'BFILE'    ,    -4 -- 31?
         , 'LONG RAW' ,    -4
         , 'RAW'      ,    -3
         , 'LONG'     ,    -1
         , 'UNDEFINED',     0
         , 'CHAR'     ,     1
         , 'NCHAR'    ,     1
         , 'NUMBER'   ,     decode( tc.DATA_SCALE, NULL, 8, 3 )
         , 'FLOAT'    ,     8
         , 'VARCHAR2' ,    12
         , 'NVARCHAR2',    12
         , 'BLOB'     ,    30
         , 'CLOB'     ,    40
         , 'NCLOB'    ,    40
         , 'DATE'     ,     9 -- not 93!
         , NULL
         )                   SQL_DATA_TYPE      -- ...
       , decode( tc.DATA_TYPE
         , 'DATE'     ,     3
         , NULL
         )                   SQL_DATETIME_SUB   -- ...
       , to_number( NULL )   CHAR_OCTET_LENGTH  -- TODO
       , tc.COLUMN_ID        ORDINAL_POSITION
       , decode( tc.NULLABLE
         , 'Y'        , 'YES'
         , 'N'        , 'NO'
         , NULL
         )                   IS_NULLABLE
    FROM ALL_TAB_COLUMNS  tc
       , ALL_COL_COMMENTS cc
   WHERE tc.OWNER         = cc.OWNER
     AND tc.TABLE_NAME    = cc.TABLE_NAME
     AND tc.COLUMN_NAME   = cc.COLUMN_NAME
)
 WHERE 1              = 1
SQL