On 03-Dec-2002 Brad Smith wrote:
> On MSDN, I found a data type chart
> (http://msdn.microsoft.com/library/en-
> us/odbc/htm/odbcsql_data_types.asp) for ODBC, which lists what I
> presume to be the types of data types that can be created for a table
> using DBI. Am I correct so far?
You really need to use SQLGetTypeInfo (or DBI's get_type_info()) to get a list
of the types supported by the DB, their local names and any options you can use
with them. When I run this to MS SQL Server and print out TYPE_NAME,
LOCAL_TYPE_NAME, DATA_TYPE, CREATE_PARAMS I get this:
sql_variant, sql_variant, -150,
uniqueidentifier, uniqueidentifier, -11,
ntext, ntext, -10,
nvarchar, nvarchar, -9, max length
sysname, sysname, -9,
nchar, nchar, -8, length
bit, bit, -7,
tinyint, tinyint, -6,
tinyint identity, tinyint identity, -6,
bigint, bigint, -5,
bigint identity, bigint identity, -5,
image, image, -4,
varbinary, varbinary, -3, max length
binary, binary, -2, length
timestamp, timestamp, -2,
text, text, -1,
char, char, 1, length
numeric, numeric, 2, precision,scale
numeric() identity, numeric() identity, 2, precision
decimal, decimal, 3, precision,scale
money, money, 3,
smallmoney, smallmoney, 3,
decimal() identity, decimal() identity, 3, precision
int, int, 4,
int identity, int identity, 4,
smallint, smallint, 5,
smallint identity, smallint identity, 5,
float, float, 6,
real, real, 7,
varchar, varchar, 12, max length
datetime, datetime, 93,
smalldatetime, smalldatetime, 93,
> For those of you going to look at this
> for the first time, I am also presuming that the middle column, labelled
> "Typical SQL data type" is the data type description that would be used
> when creating a table with DBI.
Yes but see my results from MS SQL Server which shows it is not typical.
> The table I am creating needs to have an automatically incremental
> column, named 'sid'. So far, this is what I have:
>
> my $dbh = DBI->connect('dbi:ODBC:armstrongfrn_survey') || die
> DBI::errstr;
> my $sth = $dbh->do("CREATE TABLE sample (
> sid INTEGER auto_increment,
> abstract VARCHAR(255) )
> ");
> $dbh->disconnect();
Yup that is one way of doing it but another is to use "int identity" - depends
on the database. I don't think identity or auto_increment is SQL92.
> But, I get an error, of course. Does anyone know where there is a
> convenient chart of attributes to apply to data types, using ODBC?
> And, even better, if anyone knows of syntax examples, that would be
> super, too.
>
> Right now, I am roaming back and forth between MSDN, "Programming
> the Perl DBI", the DBI perldoc, and the "ActivePerl Developer's Guide",
> none with much luck on finding this attribute information.
>
> Thanks again for the help.
Hope this helps.
Martin
--
Martin J. Evans
Easysoft Ltd, UK
Development
