Oracle database allows packing multiple sql lines into an anonymous block
(it looks just like a stored procedure - but without a name and created "on
the fly") - that could be executed using a single native query.
I would verify if SQLServer provides similar functionality.
Alternatively you can execute multiple native queries:
// this is either DatabaseSession or ClientSession
((AbstractSession)session).beginTransaction();
session.executeNonSelectingCall("CREATE TABLE dbo.Tmp_mediafile(mfid int NOT
NULL IDENTITY (1, 1),title nvarchar(256) NULL) ON [PRIMARY]");
...
((AbstractSession)session).commitTransaction();
----- Original Message -----
From: "Oliver Kykal" <OKykal_at_gmx.de>
To: <persistence_at_glassfish.dev.java.net>
Sent: Thursday, September 18, 2008 4:57 PM
Subject: SQL-Scripts
> Hi,
>
> I'm using Toplink Essentials/Eclipselink for a standalone application. In
> most cases it is sufficient to execute a native query to alter the tables.
> In case of the MSSQL-Server I have to execute scripts like the following
> to remove default values etc.
>
> BEGIN TRANSACTION
> GO
> CREATE TABLE dbo.Tmp_mediafile
> (
> mfid int NOT NULL IDENTITY (1, 1),
> title nvarchar(256) NULL
> ) ON [PRIMARY]
> GO
> SET IDENTITY_INSERT dbo.Tmp_mediafile ON
> GO
> IF EXISTS(SELECT * FROM dbo.mediafile)
> EXEC('INSERT INTO dbo.Tmp_mediafile (mfid, title FROM dbo.mediafile
> WITH (HOLDLOCK TABLOCKX)')
> GO
> SET IDENTITY_INSERT dbo.Tmp_mediafile OFF
> GO
> DROP TABLE dbo.mediafile
> GO
> EXECUTE sp_rename N'dbo.Tmp_mediafile', N'mediafile', 'OBJECT'
> GO
> ALTER TABLE dbo.mediafile ADD CONSTRAINT
> PK_mediafile PRIMARY KEY CLUSTERED
> (
> mfid
> ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
> ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
>
> GO
> COMMIT
>
> As a native query it is not possible to execute this script. Do I have to
> create a Java SQL connection to database, or is there any way to do such
> scripting?
>
> Greetings
>
> Oliver Kykal
>