关于Modify和嵌入式SQL的问题

[saral]

请问SQL Server2000有没有alter table <表名> modify <列名><数据类型> 的功能？
如果有该怎么表达才是正确的？alter table student modify sage smallint;
系统老说有错误，该了N次都不对。
关于嵌入式SQL是在什么环境下使用它，我如果想在我的机器中定义游标并与主语言进行通信该怎么办？是在SQL Server2000中加东西还是在别的象VC中加？
十分感谢大家的帮忙！

[happydreamer]

alter table student drop constraint 约束名

[saral]

那上一句怎么改呢，我把约束名和属性列的位置换了又换怎么都不行？

[Rivulet119]

你必须知道该唯一约束的约束名，然后使用语句：
alter table student drop constraint 约束名

关于嵌入式SQL的使用：
先摘录我编著的《数据库原理》一书中的章节：

前面第二章介绍SQL语句时，都是作为独立的数据语言，以交互的方式使用的。而实际开发应用系统时，为了缩短开发周期、美化用户界面，应用系统的开发常常借助于面向对象的前台开发工具，如目前流行的Visual Basic、PowerBuilder、Delphi、Visual C＋＋等，他们使用的是某种高级语言（例如：Basic、PbScript、PASCAL、 C等）。但是，当需要在程序中完成对后台数据库的处理时，又使用了能高效处理数据的SQL语言（包括后台DBMS的某些函数）。这种方式下使用的SQL语言称为嵌入式SQL（Embedded SQL），其中传统的高级语言称为宿主语言（或主语言）。
由于SQL是非过程的、面向集合的数据操纵语言，它大部分语句的使用都是独立的，与上下文条件无关的。在事务处理中，常常需有流程控制，即需要程序根据不同的条件执行不同的任务，如果单单使用SQL语言，很难实现这类应用。另一方面，高级语言在涉及数据库操作时，不能高效地进行数据的存取。所以，嵌入式SQL的使用，结合了高级语言的过程性和SQL语言的数据操纵能力，可提高数据库应用程序的效率。
......

在交互式和嵌入式两种不同的使用方式下，SQL语言的语法结构基本相同。
各个DBMS在实现嵌入式SQL时，对不同的宿主语言，所用的方法基本上是相同的。但由于宿主语言的差异，在实现时也各有特点。
在图7-5中，预编译器不能够检查宿主语言的语法合法性，它所能做的是查找表示“嵌入式SQL从这里开始”或“嵌入式SQL在这里结束”的信号。所以嵌入式SQL一般都具有一个前缀和一个结束符。一般的前缀是EXEC SQL，结束符是分号一般地，对嵌入的SQL语句加前缀EXEC SQL，而结束标志则随宿主语言的不同而不同：
Ada： EXEC SQL┅；
C : EXEC SQL┅；
COBOL: EXEC SQL┅ END EXEC
FORTRAN: EXEC SQL┅<no end>(即，没有显式结束符)
MUMPS: &SQL(┅)
PASCAL: EXEC SQL┅；
PL/I: EXEC SQL┅；

[saral]

例如：删除学生姓名必须取唯一值的约束
这个语句怎么写？在表student中，学生姓名是sname。约束unique，用你们所说的alter table student drop constraint 约束名，怎么写？

[happydreamer]

alter table student drop constraint 约束名

如果约束名不知道的话

select name from sysobjects where id in
(
select constid from
sysconstraints a join sysobjects b on a.id=b.id
and b.name='tablename '
)

[happy_0325]

约束名
企业管理器-〉找到你的表右击-〉设计表-〉点击管理索引和键 找到你的约束名。

[saral]

太感谢了，所有的楼上的兄弟们。

[txlicenhe]

alter table student alter column sage tinyint

[saral]

谢谢你的帮助，这样写果然可以了，但是必须改掉modify吗？

[hjb111]

alter table yourtable alter column colnmae 新的类型

[happydreamer]

修改列是用
alter table tbname alter column columnname xxx 的

再仔细看看alter的语法
语法
ALTER TABLE table
{ [ ALTER COLUMN column_name
{ new_data_type [ ( precision [ , scale ] ) ]
[ COLLATE < collation_name > ]
[ NULL | NOT NULL ]
| {ADD | DROP } ROWGUIDCOL }
]
| ADD
{ [ < column_definition > ]
| column_name AS computed_column_expression
} [ ,...n ]
| [ WITH CHECK | WITH NOCHECK ] ADD
{ < table_constraint > } [ ,...n ]
| DROP
{ [ CONSTRAINT ] constraint_name
| COLUMN column } [ ,...n ]
| { CHECK | NOCHECK } CONSTRAINT
{ ALL | constraint_name [ ,...n ] }
| { ENABLE | DISABLE } TRIGGER
{ ALL | trigger_name [ ,...n ] }
}

< column_definition > ::=
{ column_name data_type }
[ [ DEFAULT constant_expression ] [ WITH VALUES ]
| [ IDENTITY [ ( seed , increment ) [ NOT FOR REPLICATION ] ] ]
]
[ ROWGUIDCOL ]
[ COLLATE < collation_name > ]
[ < column_constraint > ] [ ...n ]

< column_constraint > ::=
[ CONSTRAINT constraint_name ]
{ [ NULL | NOT NULL ]
| [ { PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ]
[ WITH FILLFACTOR = fillfactor ]
[ ON { filegroup | DEFAULT } ]
]
| [ [ FOREIGN KEY ]
REFERENCES ref_table [ ( ref_column ) ]
[ ON DELETE { CASCADE | NO ACTION } ]
[ ON UPDATE { CASCADE | NO ACTION } ]
[ NOT FOR REPLICATION ]
]
| CHECK [ NOT FOR REPLICATION ]
( logical_expression )
}

< table_constraint > ::=
[ CONSTRAINT constraint_name ]
{ [ { PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ]
{ ( column [ ,...n ] ) }
[ WITH FILLFACTOR = fillfactor ]
[ ON { filegroup | DEFAULT } ]
]
| FOREIGN KEY
[ ( column [ ,...n ] ) ]
REFERENCES ref_table [ ( ref_column [ ,...n ] ) ]
[ ON DELETE { CASCADE | NO ACTION } ]
[ ON UPDATE { CASCADE | NO ACTION } ]
[ NOT FOR REPLICATION ]
| DEFAULT constant_expression
[ FOR column ] [ WITH VALUES ]
| CHECK [ NOT FOR REPLICATION ]
( search_conditions )
}

[happydreamer]

参考
c语言中嵌入式SQL是的使用

exapmle

main()
{
exec sql include sqlca
exec sql begin declare section
char prname[20];
char productno[8];

exec sql end declare section

printf("Input producno:");
scanf("%s",&producno);

exec sql select prname
from product
where productno=:productno
into :prname;

printf("Productname: %s\n",prname);

exit();

}

注意sql语句中调用c的变量前要加:
用exec sql来表示将执行sql语句



你在SQL 2000的帮助里找ESQL/C就可以找到。
有例程
我贴一点给你
int main(int argc, char *argv[])
{
LOGINREC* login; // login rec pointer
DBPROCESS* dbproc; // SQL Server connection structure pointer

char cmd[150]; // command buffer
char server[30]; // server name buffer
int x = 1; // command line counter
STATUS retc; // return code

const char* sqlversion; // pointer for version string

*server = '\0'; // null start these two buffers
*cmd = '\0';

if(argc == 1) // if no server name, request it
{
printf("Enter Server Name: ");
gets(server);
}
else // else it was input as first arg
strcpy(server,argv[1]);

if(argc < 2) // if no login id, request it
{
printf("Enter User Name: ");
gets(cmd);
}
else // otherwise it was input as second arg.
strcpy(cmd,argv[2]);

// check to see if communications layer was loaded (DOS ONLY)
if((sqlversion = dbinit()) == (BYTE *)NULL)
{
// DOS TSR (DBNMPIPE.EXE) is not loaded, don't bother going any farther
printf("Error in DB-Library initialization, exiting\n");
return 1;
}
else
printf("DB-Library version: %s\n",sqlversion); // print dblib version

dbsettime(30); // set timeouts to 30 seconds

// set error/msg handlers for this program
dbmsghandle((DBMSGHANDLE_PROC)msg_handler);
dberrhandle((DBERRHANDLE_PROC)err_handler);

login = dblogin(); // get a login rec

DBSETLUSER(login,cmd); // set login id
DBSETLHOST(login,"SQL EXAMPLE"); // set host name for sp_who
DBSETLVERSION(login, DBVER60);
// To use secure, or trusted, connection, uncomment the following line.
// DBSETLSECURE (login);

// open connection to requested server. Pass null server name for local
// connection, if name not entered.
if((dbproc = dbopen(login,(*server) ? server : (char *)NULL)) == (DBPROCESS *)NULL)
{
// no one answered, so couldn't connect or error occurred
printf("Login failed\n");
return 1;
}
else
{
// loop on command input until quit or exit appears in first 4 bytes.
while((strnicmp(cmd,"quit",4) != 0) && (strnicmp(cmd,"exit",4)!=0))
{
printf("%d> ", x++); // print command prompt
gets(cmd); // get command
if(strnicmp(cmd,"go",2) == 0) // is it go
{
if(dbsqlexec(dbproc) == FAIL) // execute command
{
// problem occurred, just try another command
printf("Error in executing command batch!\n");
x = 1;
continue;
}
// command executed correctly, get results information
while((retc = dbresults(dbproc)) != NO_MORE_RESULTS)
{
if (retc == FAIL) // if error get out of loop
break;

// headers and data could be printed here with only two
// function calls, dbprhead(dbproc), and dbprrow(dbproc),
// which would output the headers, and all the data to
// standard output. However, that isn't very informative
// toward understanding how this data is obtained and
// processed, so I do it the hard way, one column at a time.

PrintHeaders(dbproc); // print header data

// loop on each row, until all read
while((retc= dbnextrow(dbproc))!=NO_MORE_ROWS)
{
if(retc == FAIL) // if fail, then clear
{ // connection completely, just
dbcancel(dbproc); // in case.
break;
}
else
PrintRow(dbproc); // else print the current row
}

if (DBCOUNT(dbproc) == 1L) // print the row count
printf("(1 row effected)\n");
else
printf("(%ld rows effected)\n",DBCOUNT(dbproc));

} // end while(dbresults())

x = 1; // reset command line counter
}
else
{
strcat(cmd," "); // go not detected, so put space
dbcmd(dbproc,cmd); // between each command and set in
} // dbproc.

} // end while()

dbclose(dbproc); // quit/exit input, close connection

// print adios and exit.
printf("SQL Server Connection to %s closed, bye bye.\n",server);
return 0;
}
}

/*

[happydreamer]

a good article in MSDN:
Setting Up the nsqlprep Precompiler
Before you run the nsqlprep precompiler, do the following:

The nsqlprep precompiler uses your compiler to process header files

Set the INCLUDE environment variable to include the full path where the Sqlca.h and Sqlda.h ESQL/C header files are located and set the LIB environment variable to include the full path where the library files are located. There are several ways to accomplish this, including either:
Issuing a SET statement at the command prompt, such as
SET INCLUDE = C\Mssql17\DevTools\INCLUSE; %include%

SET LIB = C\Mssql17\DevTools\LIB; %LIB%

At the command prompt, first running Vcvar32.bat (in the \Program Files\Microsoft Visual Studio\VC98\Bin directory) and then running setenv.bat (in \Mssql17\DevTools\Samples\Esqlc directory).
The nsqlprep precompiler automatically includes these header files in the C programs it creates. Do not explicitly include them (by using #include) in an Embedded SQL program.

To enable communication with Microsoft® SQL Server™ 2000, ensure that an appropriate Net-Library is loaded or available on the path when precompiling with the /DB and /PASS options. For example, the Named Pipes Net-Library for the Microsoft Windows NT® operating system (Intel® platform) is Dbnmpntw.dll, and the Named Pipes Net-Library for the Microsoft MS-DOS® operating system is the Dbnmpipe.exe TSR.

[saral]

虽然还没来的及消化，但对仁兄们的帮忙，万分感谢。我瞅瞅先

[saral]

ALTER TABLE Student drop not null(scome);
这句怎么改呢？

[CrazyFor]

alter table yourtable alter column colnmae 新的类型
具体看联机帮助.

[pengdali]

--加
ALTER TABLE table2 ADD row_id bigint

--删
ALTER TABLE table2 DROP COLUMN row_id

--改
ALTER TABLE 你的表 ALTER COLUMN 列名 你的类型 null

[saral]

麻烦各位，我是想删除学生姓名必须取唯一值的约束，不是删掉该列也不是更改类型怎么写？书中是
alter table student drop unique（sname）；
用drop在SQL Server2000中怎么改呢？