Thread: SQL TYPE MAP such as SQL_CHAR, SQL_NUMERIC , etc
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)
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)
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 >
>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 > >
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. :)
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