Re: [sqlite] data type problem
Thanks for you all. I don't know if [char](32) is valid standard SQL or not. But the MS use this type in SQLServer2000(and above?). The SQL statements that I used to create tables in SQLite3 was auto generated by my SQLServer2000 EnterpriseManager. And SQLite3 did not report any error about this. It likes a trap for me. What ever, I have reported this as a bug in www.sqlite.org, and, I will also choose my columnnames carefully the next time :) - Original Message - From: "Kees Nuyt" <[EMAIL PROTECTED]> To: <sqlite-users@sqlite.org> Sent: Friday, March 23, 2007 4:55 AM Subject: Re: [sqlite] data type problem > On Tue, 20 Mar 2007 13:24:17 +0800, you wrote: > >>if you create a table use following statement (script generated from MS SQL >>Server 2000) >>CREATE TABLE [XTollData] ( >> [DutyID] [char] (32) NOT NULL , >> [CarNumber] [char] (10) NULL >>); >> >>SQLite3_Column_decltype will treat DutyID as data type 'char' but not >>'char(32)' > > I don't think [char] (32) in > [DutyID] [char] (32) NOT NULL , > is valid SQL. Keywords can be quoted with "" or [] to be able to > use them as identifiers (i.e. objectnames), but IMHO keywords > that have to stay keywords shouldn't be quoted at all. > > If you choose your columnnames carefully (not contained in the > collection of reserved words), the [] could easily be filtered > out with sed or awk. > -- > ( Kees Nuyt > ) > c[_] > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > >
Re: [sqlite] data type problem
On Tue, 20 Mar 2007 13:24:17 +0800, you wrote: >if you create a table use following statement (script generated from MS SQL >Server 2000) >CREATE TABLE [XTollData] ( > [DutyID] [char] (32) NOT NULL , > [CarNumber] [char] (10) NULL >); > >SQLite3_Column_decltype will treat DutyID as data type 'char' but not >'char(32)' I don't think [char] (32) in [DutyID] [char] (32) NOT NULL , is valid SQL. Keywords can be quoted with "" or [] to be able to use them as identifiers (i.e. objectnames), but IMHO keywords that have to stay keywords shouldn't be quoted at all. If you choose your columnnames carefully (not contained in the collection of reserved words), the [] could easily be filtered out with sed or awk. -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] data type problem
[EMAIL PROTECTED] wrote:
> but if you create a table using:
>
> CREATE TABLE XTollData (
> DutyID char (32) NOT NULL ,
> CarNumber char (10) NULL
> );
>
> SQLite3_Column_decltype will return the result of 'DutyID' as 'char(32)'.
> That is, SQLite3_Column_decltype treat '[char](32)' as 'char', treat
> 'char(32)' as 'char(32)'.
> I think this IS a bug.
>
I did some checking and standard SQL:1999 does not allow the data type
(i.e. the char in your definition) in a column definition to be quoted.
Therefore the column definitions given in your original post are not
valid SQL syntax.
You should use
CREATE TABLE "XTollData" (
"DutyID" char(32) NOT NULL ,
"CarNumber" char(10) NULL
);
or if you must use the non-standard square bracket quotes
CREATE TABLE [XTollData] (
[DutyID] char(32) NOT NULL ,
[CarNumber] char(10) NULL
);
To define your table using valid SQL (or extended SQL) syntax.
SQLite accepts a wide variety of nonsense as the column data type
because it is generally ignored internally since SQLite uses dynamic
typing. While I think it should return whatever it accepts as the
column's declared type, it really shouldn't accept the syntax you used
to define your table. It should have given you a syntax error on your
create statement.
I have copied the pertinent sections from the standard syntax specs below.
::=
{ | }
[ ]
[ ]
[ ... ]
[ ]
::=
::=
::=
|
::=
::= ...
::=
|
::= !! See the Syntax Rules
::= "" !! two consecutive double quote characters
::=
|
|
|
|
::=
[ CHARACTER SET ]
|
|
|
|
|
|
|
::=
CHARACTER []
| CHAR []
| CHARACTER VARYING
| CHAR VARYING
| VARCHAR
| CHARACTER LARGE OBJECT []
| CHAR LARGE OBJECT []
| CLOB []
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] data type problem
[EMAIL PROTECTED] wrote: > but if you create a table using: > > CREATE TABLE XTollData ( > DutyID char (32) NOT NULL , > CarNumber char (10) NULL > ); > > SQLite3_Column_decltype will return the result of 'DutyID' as 'char(32)'. > That is, SQLite3_Column_decltype treat '[char](32)' as 'char', treat > 'char(32)' as 'char(32)'. > I think this IS a bug. > A Delphi compoenent(ASGSQLite3) use SQLite3_Column_decltype to determine the > data type and data size of a field. And this "feature" makes it get the wrong > size of '[char](32)' (as 1 byte), and get the right size of 'char(32)' (as 32 > bytes). > > I agree that what you have found is a bug, and you should report it by clicking the bugs link at http://www.sqlite.org/. In the mean time there is a workaround, and that is to eliminate the unnecessary square bracket quoting on your column type names (i.e. use char and not [char]). Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] data type problem
but if you create a table using: CREATE TABLE XTollData ( DutyID char (32) NOT NULL , CarNumber char (10) NULL ); SQLite3_Column_decltype will return the result of 'DutyID' as 'char(32)'. That is, SQLite3_Column_decltype treat '[char](32)' as 'char', treat 'char(32)' as 'char(32)'. I think this IS a bug. A Delphi compoenent(ASGSQLite3) use SQLite3_Column_decltype to determine the data type and data size of a field. And this "feature" makes it get the wrong size of '[char](32)' (as 1 byte), and get the right size of 'char(32)' (as 32 bytes). - Original Message - From: <[EMAIL PROTECTED]> To: <sqlite-users@sqlite.org> Sent: Tuesday, March 20, 2007 7:48 PM Subject: Re: [sqlite] data type problem <[EMAIL PROTECTED]> wrote: > if you create a table use following statement (script generated from MS SQL > Server 2000) > CREATE TABLE [XTollData] ( > [DutyID] [char] (32) NOT NULL , > [CarNumber] [char] (10) NULL > ); > > SQLite3_Column_decltype will treat DutyID as data type 'char' but not > 'char(32)' This is a feature, not a bug. If you want the database to enforce a maximum string length, then add CHECK( length(DutyID)<=32 ) -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] data type problem
[EMAIL PROTECTED] wrote: <[EMAIL PROTECTED]> wrote: if you create a table use following statement (script generated from MS SQL Server 2000) CREATE TABLE [XTollData] ( [DutyID] [char] (32) NOT NULL , [CarNumber] [char] (10) NULL ); SQLite3_Column_decltype will treat DutyID as data type 'char' but not 'char(32)' This is a feature, not a bug. If you want the database to enforce a maximum string length, then add CHECK( length(DutyID)<=32 ) I think what the OP is saying is that he thinks sqlite3_column_decltype should return the complete declared type char(32) not just the char portion. This seems to be an issue with the way sqlite is parsing the column declaration. I suspect sqlite may be getting confused by the square bracket quoting used on the typename. The syntax grammar at http://www.sqlite.org/lang_createtable.html says that the type of a column can be given as type := typename ( number ) Which the OP is doing, but the type returned by the decltype API only has the typename part. Whether or not the square bracket quoting should be included is debatable. This does seem like a bug to me. Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] data type problem
<[EMAIL PROTECTED]> wrote: > if you create a table use following statement (script generated from MS SQL > Server 2000) > CREATE TABLE [XTollData] ( > [DutyID] [char] (32) NOT NULL , > [CarNumber] [char] (10) NULL > ); > > SQLite3_Column_decltype will treat DutyID as data type 'char' but not > 'char(32)' This is a feature, not a bug. If you want the database to enforce a maximum string length, then add CHECK( length(DutyID)<=32 ) -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] data type problem
if you create a table use following statement (script generated from MS SQL Server 2000) CREATE TABLE [XTollData] ( [DutyID] [char] (32) NOT NULL , [CarNumber] [char] (10) NULL ); SQLite3_Column_decltype will treat DutyID as data type 'char' but not 'char(32)'
