动态报表实现中的建表问题

[shurr]

，怎样用sql语句保存用户自定义表头的表

[shurr]

这段代码是否可行？
String ls_sql
//若与SQL SERVER、Sybase 11、ODBC数据库相连，在运行CREATE前，
//必须置系统对象属性AUTOCOMMIT为TRUE
SQLCA.AUTOCOMMIT=TRUE
ls_sql="CREATE TABLE newtable(nt_id char(6) NOT NULL,"+&
"f01 NOT NULL,f02 vachar(12),"+&
"f03 vachar(50),PRIMARY KEY(nt_id))"
EXECUTE IMMEDIATE :ls_sql USING SQLCA;
SQLCA.AUTOCOMMIT=FALSE

[xyzliuin]

应该是可以的，给你一个参考
------------------------------
SQLCA.AUTOCOMMIT=TRUE
SQLCA.AUTOCOMMIT=false
这是必须的
------------------------------
动态SQL语句：
动态SQL四种类型的语句格式　

1.Dynamic SQL format 1

EXECUTE IMMEDIATE SQLStatement {USING TransactionObject} ;

eg:
string Mysql
Mysql = "CREATE TABLE Employee "&
+"(emp_id integer not null,"&
+"dept_id integer not null, "&
+"emp_fname char(10) not null, "&
+"emp_lname char(20) not null)"
EXECUTE IMMEDIATE :Mysql ;

2.Dynamic SQL format 2

PREPARE DynamicStagingArea FROM SQLStatement {USING TransactionObject} ;
EXECUTE DynamicStagingArea USING {ParameterList} ;

eg:
INT Emp_id_var = 56
PREPARE SQLSA
FROM "DELETE FROM employee WHERE emp_id=?" ;
EXECUTE SQLSA USING :Emp_id_var ;


3.Dynamic SQL format 3

DECLARE Cursor | Procedure DYNAMIC CURSOR | PROCEDURE FOR DynamicStagingArea ;
PREPARE DynamicStagingArea FROM SQLStatement {USING TransactionObject} ;
OPEN DYNAMIC Cursor {USING ParameterList} ;
EXECUTE DYNAMIC Procedure {USING ParameterList} ;
FETCH Cursor | Procedure INTO HostVariableList ;
CLOSE Cursor | Procedure ;
eg:
integer Emp_id_var

DECLARE my_cursor DYNAMIC CURSOR FOR SQLSA ;
PREPARE SQLSA FROM "SELECT emp_id FROM employee" ;
OPEN DYNAMIC my_cursor ;
FETCH my_cursor INTO :Emp_id_var ;
CLOSE my_cursor ;


4.Dynamic SQL format 4

DECLARE Cursor | Procedure DYNAMIC CURSOR | PROCEDURE FOR DynamicStagingArea ;
PREPARE DynamicStagingArea FROM SQLStatement {USING TransactionObject} ;
DESCRIBE DynamicStagingArea INTO DynamicDescriptionArea ;
OPEN DYNAMIC Cursor | Procedure USING DESCRIPTOR DynamicDescriptionArea ;
EXECUTE DYNAMIC Cursor | Procedure USING DESCRIPTOR DynamicDescriptionArea ;
FETCH Cursor | Procedure USING DESCRIPTOR DynamicDescriptionArea ;
CLOSE Cursor | Procedure ;

eg:

string Stringvar, Sqlstatement
integer Intvar
Sqlstatement = "SELECT emp_id FROM employee"
PREPARE SQLSA FROM :Sqlstatement ;
DESCRIBE SQLSA INTO SQLDA ;
DECLARE my_cursor DYNAMIC CURSOR FOR SQLSA ;
OPEN DYNAMIC my_cursor USING DESCRIPTOR SQLDA ;
FETCH my_cursor USING DESCRIPTOR SQLDA ;

// If the FETCH is successful, the output
// descriptor array will contain returned
// values from the first row of the result set.
// SQLDA.NumOutputs contains the number of
// output descriptors.
// The SQLDA.OutParmType array will contain
// NumOutput entries and each entry will contain
// an value of the enumerated data type ParmType
// (such as TypeInteger!, or TypeString!).

CHOOSE CASE SQLDA.OutParmType[1]
CASE TypeString!
Stringvar = GetDynamicString(SQLDA, 1)
CASE TypeInteger!
Intvar = GetDynamicNumber(SQLDA, 1)

END CHOOSE
CLOSE my_cursor ;










昕晨工作室[e-mail]





晕？晕！
在pb 中利用execute immediate :ssql，即可执行后台数据库的语句，本人通过查阅资料及通过不断的实验，获得了一种通过读取*.sql文 件生成 sql server 7 的数据库的方法，现公布如下，以对大家起到抛砖引玉的作用：
因为pb的限制，不能一次性读入大于32KB的sql脚本文件，需要修改fileread代码，利用循环将整个文件读入，
f_readfile的代码
参数：string str_file[]为实例变量
参数：str_filenameinteger li_FileNum, loops,i
long flen, bytes_read, new_pos
blob b
string str_sqlexecute
SetPointer(HourGlass!)
flen = FileLength(str_filename)
li_FileNum = FileOpen(str_filename, StreamMode!, Read!, LockRead!)
IF flen > 32765 THEN
IF Mod(flen, 32765) = 0 THEN
loops = flen/32765
ELSE
loops = (flen/32765) + 1
END IF
ELSE
loops = 1
END IF
new_pos = 1
FOR i = 1 to loops
bytes_read = FileRead(li_FileNum, b)
str_file[i]=string(b)
NEXT
FileClose(li_FileNum)
利用ms sql sever 7.0 enterprise manager 生成sql文件（注意要将options->file format 选为ms-dos text(oem),默认格式是 internation text(unicode),此格式不能被pb 的 fileread函数正确读出。）
再用以下代码生成数据库的对象：
integer i,li_postion,l_upper
l_upper=upperbound(str_file)
for i=1 to l_upper
li_postion=pos(str_file[i],"GO",1)
do while li_postion<>0
str_sqlexecute=trim(mid(str_file[i],1,li_postion - 1))
execute immediate :str_sqlexecute;
str_file[i]=mid(str_file[i],li_postion+2)
li_postion=pos(str_file[i],"GO",1)
loop
if i<l_upper then
str_file[i+1]=str_file[i]+str_file[i+1]
end if
next
///////////////////
只要是生成了*.sql文件， 数据库中的任何对象都可以生成

[xyzliuin]

这个是我用的,sqlserver7.0 测试没问题
SQLCA.AutoCommit = true
//创建连锁店库存表
ls_lb =' CREATE TABLE ld.' + ls_db + '_j_kc ('+&
'id numeric(18, 0) IDENTITY (1, 1) NOT NULL ,'+&
'company_id varchar (10) NOT NULL ,'+&
'cp_no varchar (15) NOT NULL ,'+&
'crkbm varchar (8) NOT NULL ,'+&
'kf varchar (10) NOT NULL ,'+&
'hwgz varchar (10) NULL ,'+&
'ph varchar (20) NOT NULL ,'+&
'yxqz datetime NOT NULL ,'+&
'zb int NOT NULL ,'+&
'bz int NULL ,'+&
'scrq datetime NULL ,'+&
'cpmc varchar (40) NULL ,'+&
'gg varchar (20) NULL ,'+&
'cd varchar (60) NULL ,'+&
'jldw varchar (4) NULL ,'+&
'yplb varchar (8) NULL ,'+&
'pym varchar (8) NULL ,'+&
'cptm varchar (15) NULL ,'+&
'pfj numeric(13, 5) NULL ,'+&
'lsj numeric(13, 5) NULL ,'+&
'xsjbl_1 numeric(13, 5) NULL ,'+&
'xsjbl_2 numeric(13, 5) NULL ,'+&
'xsjbl_3 numeric(13, 5) NULL ,'+&
'xsj numeric(13, 5) NULL ,'+&
'zdj numeric(13, 5) NULL ,'+&
'zk numeric(13, 5) NULL ,'+&
'scjj numeric(13, 5) NULL ,'+&
'zdxl int NULL ,'+&
'jhdw varchar (60) NULL ,'+&
'zgxl int NULL ,'+&
'sl numeric(12, 3) NULL ,'+&
'js numeric(12, 3) NULL ,'+&
'dj numeric(13, 5) NULL ,'+&
'sfts varchar (1) NULL ,'+&
'byzd_c varchar (40) NULL ,'+&
'byzd_n numeric(13, 5) NULL ,'+&
'byzd_d datetime NULL ,'+&
'if_cs int NULL ) '
execute immediate :ls_lb;
SQLCA.AutoCommit = false