» » SQL-


SQL-

1 2 |

̲Ͳ ²

ȯ ͲѲ Ͳ

˲Բֲ

:

SQL-

: 4

ij

: ..

10, 25 2000 .

. . .

- 2000

̲

1....2

2. SQL .5

2.1 DAO.6

2.2 ODBC..6

2.3 JSCRIPT..7

3. ...17

ꅅ..21

腅.22

1 :

- Internet. . . Internet, SQL (Structured Query Language) .

SQL Oracle, Microsoft SQL Server, Sybase, dbase, Informix, Progress . SQL Microsoft SQL Server 7.0 .

MS SQL Server - 볺 , .

-, , . SQL- , , , .

볺/ -볺 - . 볺/ MS SQL Server 7.0 Windows NT.

MS SQL Server 7.0 , OLE-DB ᔺ ODBC - . ֳ .

SQL Server 7.0 , . SQL , [] GUI. , . SQL Server Internet.

SQL Server . . , , . , , .

2 : ʲ Ƞ SQL SERVER 7.0

SQL Server 볺 . , . SQL Query Analyzer.

䳿 SQL Server :

1 DB-Library

2 ODBC DAO

DB-Library SQL Server . ³ ODBC, .

, ϲ, SQL Server. ODBC ( SQL-OLE ) SQL Server .

DB-Lib . 䳺 . , .

2.1 òߠ DAO

(Data Access Object ᔺ ) ᔺ, , , . SQL ᔺ DAO - Jet ODBC, ODBC, .

DAO , , ᔺ. Add ᔺ, 㳿 ODBC.

- , ODBC.

, DAO 㳺 ADO(ActiveX Data Object ᔺ ActiveX ) .

2.2 ò ODBC

Microsoft ODBC . . Select - , , SQL.

ODBC . ODBC - , , . ODBC 㳿 Access Microsoft. ODBC BD-Lib ODBC SQL , BD-Lib , .

ODBC . . ODBC , -/ SQL Server , 볺 . ODBC .

2.3 JSCRIPT

, SQL Server 7.0 . shell OS Windows NT - Java Script . . . js OS Windows NT. Script :

// ADO Flags

var adLockOptimistic = 3;

var adOpenStatic = 3;

var SQL_DataBase = "DSN=OTSt;UID=sa;PWD=;DATABASE=OTSt"

//var SQL_DataBase = "DSN=OTStt;UID=;PWD="

var oConn;

var oRs;

function db_Connect(){

// Create ADO Connection Object. Use IISSDK OBDC Souce with

// default sa account and no password

oConn = new ActiveXObject("ADODB.Connection");

oConn.Open(SQL_DataBase);

oConn.CommandTimeout=600;

}

function db_Record(SQL){

var oRs;

// Create ADO Recordset Component, and associate it with ADO

connection

oRs = new ActiveXObject("ADODB.Recordset");

oRs.ActiveConnection = oConn;

// Get empty recordset

oRs.Source = SQL;

oRs.CursorType = adOpenStatic; // use a cursor

other than Forward Only

oRs.LockType = adLockOptimistic; // use a locktype

permitting insertions

oRs.Open();

return oRs;

}

function db_Close(){

oConn.Close();

}

function db_Requery(Rec,SQL){

Rec.Close();

Rec.Source = SQL;

Rec.Open();

}

db_Connect();

try {

oRs=db_Record("CREATE TABLE dbo.DBTEST ( String char (30) NULL, Number int NOT NULL DEFAULT (0), Random int NOT NULL DEFAULT (0))");

} catch (e) {

}

oRs=db_Record("SELECT * FROM DBTEST WHERE 0=1");

for(i=0;i<100000;i++){

oRs.Addnew();

oRs("String").value="User"+i;

oRs("Number").value=i;

oRs("Random").value=Math.round(100*Math.random());

oRs.Update();

if(i%10000==0)

WScript.echo("Now in base present "++" records");

}

WScript.echo("Completed");

:

String Number Random
User1 1 34
User2 2 12
User3 3 75
. . . . . . . . . . . .

Script Connect SQL Server DBTest .

DSN , ODBC.

UID , , SQL Server s .

PWD password , . .

Record DBTest , Random 0 99 .

ʳ 10 000, 3,5 . Number .

Script , SQL Server . , , . - , SQL Server 

, .

// ADO Flags

var adLockOptimistic = 3;

var adOpenStatic = 3;

var SQL_DataBase = "DSN=OTSt;UID=sa;PWD=;DATABASE=OTSt"

//var SQL_DataBase = "DSN=OTStt;UID=;PWD="

var oConn;

var oRs;

function db_Connect(){

// Create ADO Connection Object. Use IISSDK OBDC Souce with

// default sa account and no password

oConn = new ActiveXObject("ADODB.Connection");

oConn.Open(SQL_DataBase);

oConn.CommandTimeout=600;

}

function db_Record(SQL){

var oRs;

// Create ADO Recordset Component, and associate it with ADO connection

oRs = new ActiveXObject("ADODB.Recordset");

oRs.ActiveConnection = oConn;

// Get empty recordset

oRs.Source = SQL;

oRs.CursorType = adOpenStatic; // use a cursor other than Forward Only

oRs.LockType = adLockOptimistic; // use a locktype permitting insertions

oRs.Open();

return oRs;

}

function db_Close(){

oConn.Close();

}

function db_Requery(Rec,SQL){

Rec.Close();

Rec.Source = SQL;

Rec.Open();

}

function Test(TestNumber, Query, CountTests){

Max=0;

Min=1000000;

Delta=0;

Ave=0;

WScript.echo("Query #"+TestNumber);

for(i=0;i<CountTests;i++){

StartTime=new Date();

db_Requery(oRs,Query);

EndTime=new Date();

Delta=(EndTime-StartTime);

WScript.echo(" Probe #"++" Result="+Delta+" ms");

Ave=(Ave*i+Delta)/(i+1);

}

WScript.echo(" Result="+Ave+" ms");

}

db_Connect();

oRs=db_Record("SELECT * FROM DBTEST WHERE 1=0");

Test(1, " SELECT * FROM DBTEST " , 50 );

Test(2 , " SELECT * FROM DBTEST ORDER BY String",50);

Test( 3 ," SELECT * FROM DBTEST ORDER BY Number ",50);

Test (4,"SELECT Sum(Number) FROM DBTEST GROUP BY String",50);

Test(5 , " SELECT Sum(Number) FROM DBTEST GROUP BY

Random",50);

Test( 6 , " SELECT * FROM DBTEST WHERE Number = 99999 " , 50);

Test(7 ," SELECT * FROM DBTEST WHERE String = 'User99999'",50);

Test(8,"SELECT * FROM DBTEST WHERE Number in (SELECT

Number FROM DBTEST ) " , 50 );

Test(9 ,"SELECT * FROM DBTEST WHERE Number in (SELECT

Number FROM DBTEST WHERE String < ' User50000 ' ) ",50);

Test(10 , " SELECT * FROM DBTEST WHERE Number in (SELECT

Number FROM DBTEST WHERE String='User50000')", 50);

Test(11, " SELECT * FROM DBTEST WHERE Number in (SELECT

Number FROM DBTEST WHERE String > ' User10000 ' ) " , 50);

Test(12 , "INSERT INTO DBTEST (String,Number)

VALUES('User30',30) " , 50 );

Test(13 , "DELETE FROM DBTEST (String,Number)

VALUES('User30',30) " , 50);

Test(14 ," DELETE FROM DBTEST " , 1);

db_Close()

Test 50 , :

StartTime=new Date();

EndTime=new Date();

Delta=(EndTime - StartTime);

script 14 - SQL Server. .

12 13 .

14 .

:

1:" SELECT * FROM DBTEST " - ;

2: " SELECT * FROM DBTEST ORDER BY String" - String;

3:" SELECT * FROM DBTEST ORDER BY Number"- - Number ;

4:"SELECT Sum(Number) FROM DBTEST GROUP BY String"

- Number - String;

5:" SELECT Sum(Number) FROM DBTEST GROUP BY

Random" - Number Random ;

6:" SELECT * FROM DBTEST WHERE Number = 99999 " , Number =

1 2 |