You are viewing a plain text version of this content. The canonical link for it is here.
Posted to ddlutils-dev@db.apache.org by "Richard Bounds (JIRA)" <ji...@apache.org> on 2005/09/30 14:52:47 UTC
[jira] Created: (DDLUTILS-21) Sql Server drop statements fail without delimited identifiers
Sql Server drop statements fail without delimited identifiers
-------------------------------------------------------------
Key: DDLUTILS-21
URL: http://issues.apache.org/jira/browse/DDLUTILS-21
Project: DdlUtils
Type: Bug
Reporter: Richard Bounds
Assigned to: Thomas Dudziak
The drop statements that are generated for sql server are prefixed by:
IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'tablename')
Without a delimeter being specified the statement changes to:
IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = tablename)
...which fails (note the lack of quotes around tablename).
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DDLUTILS-21) Sql Server drop statements fail without delimited identifiers
Posted by "Thomas Dudziak (JIRA)" <ji...@apache.org>.
[ http://issues.apache.org/jira/browse/DDLUTILS-21?page=comments#action_12331248 ]
Thomas Dudziak commented on DDLUTILS-21:
----------------------------------------
Ok, I've changed the implementation so that the identifiers are always enclosed in single quotes when used in a WHERE clause. Please test.
> Sql Server drop statements fail without delimited identifiers
> -------------------------------------------------------------
>
> Key: DDLUTILS-21
> URL: http://issues.apache.org/jira/browse/DDLUTILS-21
> Project: DdlUtils
> Type: Bug
> Reporter: Richard Bounds
> Assignee: Thomas Dudziak
> Attachments: patch.txt
>
> The drop statements that are generated for sql server are prefixed by:
> IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'tablename')
> Without a delimeter being specified the statement changes to:
> IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = tablename)
> ...which fails (note the lack of quotes around tablename).
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DDLUTILS-21) Sql Server drop statements fail without delimited identifiers
Posted by "Thomas Dudziak (JIRA)" <ji...@apache.org>.
[ http://issues.apache.org/jira/browse/DDLUTILS-21?page=comments#action_12330978 ]
Thomas Dudziak commented on DDLUTILS-21:
----------------------------------------
I think what matters is whether the database has quoted_identifier = ON or not:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_set-set_9jxu.asp
Could you perhaps create a database with quoted_identifier = OFF - or perhaps it suffices to use
SET quoted_identifier=OFF
CREATE TABLE tableName ...
and then check the sysobjects table ?
> Sql Server drop statements fail without delimited identifiers
> -------------------------------------------------------------
>
> Key: DDLUTILS-21
> URL: http://issues.apache.org/jira/browse/DDLUTILS-21
> Project: DdlUtils
> Type: Bug
> Reporter: Richard Bounds
> Assignee: Thomas Dudziak
> Attachments: patch.txt
>
> The drop statements that are generated for sql server are prefixed by:
> IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'tablename')
> Without a delimeter being specified the statement changes to:
> IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = tablename)
> ...which fails (note the lack of quotes around tablename).
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DDLUTILS-21) Sql Server drop statements fail without delimited identifiers
Posted by "Richard Bounds (JIRA)" <ji...@apache.org>.
[ http://issues.apache.org/jira/browse/DDLUTILS-21?page=comments#action_12331143 ]
Richard Bounds commented on DDLUTILS-21:
----------------------------------------
I still see the same behaviour with quoted_identifier = OFF or ON
> Sql Server drop statements fail without delimited identifiers
> -------------------------------------------------------------
>
> Key: DDLUTILS-21
> URL: http://issues.apache.org/jira/browse/DDLUTILS-21
> Project: DdlUtils
> Type: Bug
> Reporter: Richard Bounds
> Assignee: Thomas Dudziak
> Attachments: patch.txt
>
> The drop statements that are generated for sql server are prefixed by:
> IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'tablename')
> Without a delimeter being specified the statement changes to:
> IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = tablename)
> ...which fails (note the lack of quotes around tablename).
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DDLUTILS-21) Sql Server drop statements fail without delimited identifiers
Posted by "Thomas Dudziak (JIRA)" <ji...@apache.org>.
[ http://issues.apache.org/jira/browse/DDLUTILS-21?page=comments#action_12330972 ]
Thomas Dudziak commented on DDLUTILS-21:
----------------------------------------
Hmm, interesting. Makes sense actually ;-)
But I wonder why this works in non-delimited mode then. As per SQL-92 spec, the server should convert the identifier to uppercase (ie. TABLENAME) upon creation, so neither
tables.name = "tableName"
nor
tables.name = 'tableName'
(with single quotes) should work but rather
tables.name = 'TABLENAME'
Could you have a look into the sysobjects table and see what the exact string is in there ?
> Sql Server drop statements fail without delimited identifiers
> -------------------------------------------------------------
>
> Key: DDLUTILS-21
> URL: http://issues.apache.org/jira/browse/DDLUTILS-21
> Project: DdlUtils
> Type: Bug
> Reporter: Richard Bounds
> Assignee: Thomas Dudziak
> Attachments: patch.txt
>
> The drop statements that are generated for sql server are prefixed by:
> IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'tablename')
> Without a delimeter being specified the statement changes to:
> IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = tablename)
> ...which fails (note the lack of quotes around tablename).
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DDLUTILS-21) Sql Server drop statements fail without delimited identifiers
Posted by "Richard Bounds (JIRA)" <ji...@apache.org>.
[ http://issues.apache.org/jira/browse/DDLUTILS-21?page=comments#action_12330961 ]
Richard Bounds commented on DDLUTILS-21:
----------------------------------------
It does seem to work OK as far as I can tell. I think the case sensitivity is dependant on the collation settings for the database - for some collation settings it is case sensitive, but in those cases 'TableName' and tablename' do not seem to collide.
Richard
> Sql Server drop statements fail without delimited identifiers
> -------------------------------------------------------------
>
> Key: DDLUTILS-21
> URL: http://issues.apache.org/jira/browse/DDLUTILS-21
> Project: DdlUtils
> Type: Bug
> Reporter: Richard Bounds
> Assignee: Thomas Dudziak
> Attachments: patch.txt
>
> The drop statements that are generated for sql server are prefixed by:
> IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'tablename')
> Without a delimeter being specified the statement changes to:
> IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = tablename)
> ...which fails (note the lack of quotes around tablename).
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DDLUTILS-21) Sql Server drop statements fail without delimited identifiers
Posted by "Thomas Dudziak (JIRA)" <ji...@apache.org>.
[ http://issues.apache.org/jira/browse/DDLUTILS-21?page=comments#action_12330956 ]
Thomas Dudziak commented on DDLUTILS-21:
----------------------------------------
Unfortunately I have no Sql Server to test against at the moment. But I wonder, does this work when using a table name like 'SomeTable' (note the upper case).
If you could verify for me that such mixed case names work with your patch when not in delimited mode, then I'll apply it ASAP.
Tom
> Sql Server drop statements fail without delimited identifiers
> -------------------------------------------------------------
>
> Key: DDLUTILS-21
> URL: http://issues.apache.org/jira/browse/DDLUTILS-21
> Project: DdlUtils
> Type: Bug
> Reporter: Richard Bounds
> Assignee: Thomas Dudziak
> Attachments: patch.txt
>
> The drop statements that are generated for sql server are prefixed by:
> IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'tablename')
> Without a delimeter being specified the statement changes to:
> IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = tablename)
> ...which fails (note the lack of quotes around tablename).
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Updated: (DDLUTILS-21) Sql Server drop statements fail
without delimited identifiers
Posted by "Thomas Dudziak (JIRA)" <ji...@apache.org>.
[ http://issues.apache.org/jira/browse/DDLUTILS-21?page=all ]
Thomas Dudziak updated DDLUTILS-21:
-----------------------------------
Component: Core - SqlServer
> Sql Server drop statements fail without delimited identifiers
> -------------------------------------------------------------
>
> Key: DDLUTILS-21
> URL: http://issues.apache.org/jira/browse/DDLUTILS-21
> Project: DdlUtils
> Type: Bug
> Components: Core - SqlServer
> Reporter: Richard Bounds
> Assignee: Thomas Dudziak
> Attachments: patch.txt
>
> The drop statements that are generated for sql server are prefixed by:
> IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'tablename')
> Without a delimeter being specified the statement changes to:
> IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = tablename)
> ...which fails (note the lack of quotes around tablename).
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Resolved: (DDLUTILS-21) Sql Server drop statements fail without delimited identifiers
Posted by "Thomas Dudziak (JIRA)" <ji...@apache.org>.
[ http://issues.apache.org/jira/browse/DDLUTILS-21?page=all ]
Thomas Dudziak resolved DDLUTILS-21:
------------------------------------
Resolution: Fixed
I've submitted a patch for both Sql Server and Sybase (which is probably equally affected). Please test in both delimited and not-delimited mode with mixed-case table names, and if it works, please close the issue.
> Sql Server drop statements fail without delimited identifiers
> -------------------------------------------------------------
>
> Key: DDLUTILS-21
> URL: http://issues.apache.org/jira/browse/DDLUTILS-21
> Project: DdlUtils
> Type: Bug
> Reporter: Richard Bounds
> Assignee: Thomas Dudziak
> Attachments: patch.txt
>
> The drop statements that are generated for sql server are prefixed by:
> IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'tablename')
> Without a delimeter being specified the statement changes to:
> IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = tablename)
> ...which fails (note the lack of quotes around tablename).
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Updated: (DDLUTILS-21) Sql Server drop statements fail without delimited identifiers
Posted by "Richard Bounds (JIRA)" <ji...@apache.org>.
[ http://issues.apache.org/jira/browse/DDLUTILS-21?page=all ]
Richard Bounds updated DDLUTILS-21:
-----------------------------------
Attachment: patch.txt
my patch
> Sql Server drop statements fail without delimited identifiers
> -------------------------------------------------------------
>
> Key: DDLUTILS-21
> URL: http://issues.apache.org/jira/browse/DDLUTILS-21
> Project: DdlUtils
> Type: Bug
> Reporter: Richard Bounds
> Assignee: Thomas Dudziak
> Attachments: patch.txt
>
> The drop statements that are generated for sql server are prefixed by:
> IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'tablename')
> Without a delimeter being specified the statement changes to:
> IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = tablename)
> ...which fails (note the lack of quotes around tablename).
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DDLUTILS-21) Sql Server drop statements fail without delimited identifiers
Posted by "Richard Bounds (JIRA)" <ji...@apache.org>.
[ http://issues.apache.org/jira/browse/DDLUTILS-21?page=comments#action_12330977 ]
Richard Bounds commented on DDLUTILS-21:
----------------------------------------
It seems to preserve the case of the name as it was specified in the create statement, so
create table testTable (
testcol varchar(255)
);
select name from sysobjects where name = 'testTable';
returns 'testTable'.
This is for SQL Server 2000 on Win 2000 Server - I tried it with the collation settings of either Latin1_General_Cl_AS (which should be case-insensitive) or SQL_LAtin1_General_CP850_BIN (which should be case-sensitive), if that makes any difference.
> Sql Server drop statements fail without delimited identifiers
> -------------------------------------------------------------
>
> Key: DDLUTILS-21
> URL: http://issues.apache.org/jira/browse/DDLUTILS-21
> Project: DdlUtils
> Type: Bug
> Reporter: Richard Bounds
> Assignee: Thomas Dudziak
> Attachments: patch.txt
>
> The drop statements that are generated for sql server are prefixed by:
> IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'tablename')
> Without a delimeter being specified the statement changes to:
> IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = tablename)
> ...which fails (note the lack of quotes around tablename).
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DDLUTILS-21) Sql Server drop statements fail without delimited identifiers
Posted by "Richard Bounds (JIRA)" <ji...@apache.org>.
[ http://issues.apache.org/jira/browse/DDLUTILS-21?page=comments#action_12330969 ]
Richard Bounds commented on DDLUTILS-21:
----------------------------------------
It now works fine in not-delimited mode, but in delimited mode I get a statement that looks like:
IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = "tableName")
......
where tables.id = ref.rkeyid
and cons.id = ref.constid
and reftables.id = ref.fkeyid
and tables.name = "tableName" OPEN refcursor
which gives: java.sql.SQLException: Invalid column name 'tableName'
I think that the table name still needs to be in single quotes in both modes so that sqlserver sees it as a VARCHAR (I guess) and not an identifier.
Sorry - probably my fault for not having tried it in delimited mode before.
Richard
> Sql Server drop statements fail without delimited identifiers
> -------------------------------------------------------------
>
> Key: DDLUTILS-21
> URL: http://issues.apache.org/jira/browse/DDLUTILS-21
> Project: DdlUtils
> Type: Bug
> Reporter: Richard Bounds
> Assignee: Thomas Dudziak
> Attachments: patch.txt
>
> The drop statements that are generated for sql server are prefixed by:
> IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'tablename')
> Without a delimeter being specified the statement changes to:
> IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = tablename)
> ...which fails (note the lack of quotes around tablename).
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira