ScriptFixture executes a database script on DefaultConnection?.

Comment

EXECUTE

DBMS_BUFLEN

SUBST0 …

SUBST9

BIND1 …

BIND9

line

statement()

call()

bindVariableI()

bindI()

enableOracleOutput()

getOracleLine()

getOracleOutput()

String

String

String

String

String

String

String

String

String

String

String

String

String

String

A single line script can be passed as arg[0].

Script Fixture	CREATE TABLE T ( NAME CHAR(25) NULL)
call?
OK

Call? returns exceptions.

Script Fixture	CREATE TABLE T ( NAME CHAR(25) NULL)
call?
	This should fail, can't create the same table.

Clean Up

Script Fixture	DROP TABLE T
call?
OK

---

A multiline script can be passed as a series of lines. A slash creates a statement.

Script Fixture
line	call?
CREATE TABLE T
(
NAME CHAR(25) NULL
)
/	OK

Clean Up

Script Fixture
line	call?
DROP TABLE T
/	OK

---

Substitution

Substitution Variables ( SUBSTN where N is 0-9) define strings that are simply 'pasted' into the statement. The database server is unaware of any Substitution Variables. There are no word boundaries defined, as this example shows. The server must recompile each row.

Script Fixture	CREATE TABLE PARMTEST ( PARM VARCHAR(25) NULL)
call?
OK

Use Substitution Variables SUBST1 and SUBST2. statement? shows the string that is sent to the database server.

Script Fixture	insert into PARMTEST values('SUBST1SUBST2')
SUBST1	SUBST2	call?	statement?
Hel	lo World	OK
Goodbye W	orld	OK

---

Bind

Bind Variables (BINDN where N is 1-9) define input/output variables. The server sees both the statement and the Bind Variables.

Bind Variables are useful as parameters for stored procedures. The following fixture would call the stored procedure FINDNAME. FINDNAME would take one input parameter and one output parameter.

 
|Script Fixture|CALL FINDDNAME(10,:out 1)|
|call?|BIND1|
|||

Oracle-ism

Oracle defines an alternative syntax to the standard jdbc syntax. BEGIN ... END; can include multiple calls to stored procedures. Oracle calls this an anonymous block. Think of them as stored procedures that aren't stored :). It's easy to forget to include a BEGIN ... END; in a script. They are required if you are using bind variables.

|Script Fixture|BEGIN FINDDNAME(10,:out 1); END;|
|call?|BIND1|
|||

The script refers to a Bind Variable with a colon, an optional direction, and the number 1-9. The direction can be in,out or inout. in is assumed if no direction is provided.

JDBC uses question marks, not colons and numbers. call? converts the references to Bind Variables to question marks. See the results in statement?.

bindI? returns the bind variables starting with BIND1, then BIND2, etc.

Script Fixture	begin SELECT PARM into :out 1 from PARMTEST WHERE PARM LIKE :2; end;
BIND2	call?	BIND1	bindI?	bindI?	statement?
He%	OK		Hello World	He%
Go%	OK		Goodbye World	Go%

Clean Up

Script Fixture	DROP TABLE PARMTEST
call?
OK

---

[.FrontPage] [.RecentChanges]
Last modified anonymously on May 15, 2008 at 06:12:45 PM