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
Show quoted text
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
Show quoted text
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?
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?
Show quoted text
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?---------------------------(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