This Bugzilla instance is a read-only archive of historic NetBeans bug reports. To report a bug in NetBeans please follow the project's instructions for reporting issues.

Bug 177132 - [68cat][db]Error Executing a valid T-SQL script
Summary: [68cat][db]Error Executing a valid T-SQL script
Status: RESOLVED INVALID
Alias: None
Product: db
Classification: Unclassified
Component: Code (show other bugs)
Version: 6.x
Hardware: PC Windows XP
: P3 normal (vote)
Assignee: Jiri Rechtacek
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2009-11-18 13:19 UTC by michbarsinai
Modified: 2009-11-19 03:46 UTC (History)
0 users

See Also:
Issue Type: DEFECT
Exception Reporter:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description michbarsinai 2009-11-18 13:19:10 UTC
The following script runs fine in earlier builds and in ms' query editor, but fails in netbeans SQL editor.
Build 200911171401, MS-SQL 2005, using ms' driver.
The error:
Error code 1064, SQL state 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE objects CURSOR FOR
SELECT so.name, so.type, so.type_desc, p.name as pare' at line 1
Line 2, column 1

Execution finished after 0 s, 1 error(s) occurred.

The Script (mass deletion of tables and constraints):
-- Get all the objects that define a DB.
DECLARE objects CURSOR FOR
SELECT so.name, so.type, so.type_desc, p.name as parentName
FROM sys.objects as so LEFT JOIN sys.objects as p
	ON so.parent_object_id = p.object_id
WHERE so.schema_id = 1
ORDER BY CASE
	WHEN so.type = 'F' then 0
	WHEN so.type = 'TR' then 1
	WHEN so.type = 'U' then 2
	WHEN so.type = 'F' then 3
	ELSE 4
END

OPEN objects
DECLARE @name as nvarchar(max)
DECLARE @type as nvarchar(2)
DECLARE @type_desc as nvarchar
DECLARE @parentName as nvarchar(max)
DECLARE @statement as nvarchar(max)

-- We delete the foreign keys first, and then we devour the tables. We take some precautions not to delete system stuff.
FETCH NEXT FROM objects INTO @name, @type, @type_desc, @parentName
WHILE @@FETCH_STATUS = 0
BEGIN
	SET @statement = ''
	IF (@type = 'F' ) BEGIN
		PRINT 'DROPING FK: ' + @name + ' from table ' + @parentName
		SET @statement = 'ALTER TABLE ' + @parentName + ' DROP CONSTRAINT ' + @name
		EXECUTE(@statement)
	END
	ELSE IF (@type = 'U') AND (@name NOT IN ('sysdiagrams')) BEGIN
		PRINT 'DROPING TABLE: ' + @name + ' of type ' + @type + ' (' + @type_desc + ')'
		SET @statement = 'DROP TABLE ' + @name
		EXECUTE(@statement)
	END
	FETCH NEXT FROM objects INTO @name, @type, @type_desc, @parentName
END

CLOSE objects
DEALLOCATE objects
Comment 1 Jiri Skrivanek 2009-11-19 03:46:20 UTC
It seems suspicious that the error message states "...your MySQL server..". But your target server should be MS-SQL 2005. Didn't you executed this script against MySQL connection by accident?