PROC＋＋批量导入导出ORACLE数据库表

  1<vcl.h> #include<windows.h> #include<stdio.h> #include<stdlib.h> #include<string.h>   
  2  
  3#include<time.h> #include<math.h> #include<fcntl.h> #include<io.h> #include<sys tat.h="">   
  4  
  5//说明DLL的输出函数   
  6  
  7extern "C" _declspec(dllexport) int _stdcall ConnectDB(const char *Username,   
  8  
  9const char *Password, const char *Dbname);   
 10  
 11extern "C" _declspec(dllexport) int _stdcall ImportTxtfile(TList *LengthArray,   
 12  
 13String *FieldArray, const char *TableName,   
 14  
 15const char *FileName);   
 16  
 17extern "C" _declspec(dllexport) int _stdcall ExportTxtfile(const char *Sql,   
 18  
 19const char *FileName);   
 20  
 21#pragma hdrstop   
 22  
 23//----------------------------------------------------------------------------   
 24  
 25#define MAX_ITEMS 20 //定义最大字段数   
 26  
 27#define MAX_VNAME_LEN 30 //定义选择表项最大长度   
 28  
 29#define MAX_INAME_LEN 30 //定义指示器变量名字的最大长度   
 30  
 31  
 32  
 33EXEC SQL INCLUDE sqlca; //说明SQL通讯区   
 34  
 35EXEC SQL INCLUDE oraca; //说明ORACLE通讯区   
 36  
 37EXEC SQL INCLUDE sqlda; //说明SQL语句描述结构/*SQLDA结构体请查相关资料*/   
 38  
 39  
 40  
 41EXEC ORACLE OPTION (ORACA = YES);   
 42  
 43EXEC ORACLE OPTION (RELEASE_CURSOR = YES);   
 44  
 45  
 46  
 47//说明ORACLE外部函数   
 48  
 49extern "C" _declspec(dllimport) void _stdcall sqlclu(SQLDA*);   
 50  
 51extern "C" _declspec(dllimport) void _stdcall sqlnul(short*, short*, int*);   
 52  
 53extern "C" _declspec(dllimport) void _stdcall sqlprc(int*, int*, int*);   
 54  
 55extern "C" _declspec(dllimport) struct SQLDA * _stdcall sqlald(int, unsigned int, unsigned int);   
 56  
 57  
 58  
 59SQLDA *SelectUnit; //定义选择项描述   
 60  
 61SQLDA *BindUnit; //定义输入项空间   
 62  
 63//定义变量，以存放连接数据库的参数   
 64  
 65EXEC SQL BEGIN DECLARE SECTION;   
 66  
 67char User[20];//用户名   
 68  
 69char Pwd[20];//密码   
 70  
 71char DB[20];//数据库服务名   
 72  
 73EXEC SQL END DECLARE SECTION;   
 74  
 75  
 76  
 77bool bConnect = false;//是否连接标志   
 78  
 79#pragma hdrstop   
 80  
 81  
 82  
 83#pragma argsused   
 84  
 85//C++ Builder DLL的主函数   
 86  
 87BOOL WINAPI DllMain(HINSTANCE hinstDLL, DWORD fwdreason, LPVOID lpvReserved)   
 88  
 89{   
 90  
 91return 1;   
 92  
 93}   
 94  
 95  
 96  
 97/*---------------------------------------------------------------------------   
 98  
 99连接数据库   
100  
101\---------------------------------------------------------------------------*/   
102  
103int _stdcall ConnectDB(const char *Username, const char *Password,   
104  
105const char *Dbname)   
106  
107{   
108  
109strcpy(User, Username);   
110  
111strcpy(Pwd, Password);   
112  
113strcpy(DB, Dbname);   
114  
115  
116  
117EXEC SQL CONNECT :User IDENTIFIED BY :Pwd USING :DB;   
118  
119  
120  
121if (sqlca.sqlcode &lt; 0)   
122  
123return -1;   
124  
125  
126  
127bConnect = true;   
128  
129return 0;   
130  
131}   
132  
133/*---------------------------------------------------------------------------   
134  
135导出文本函数   
136  
137因为不确定SELECT语句的表及字段，所以我使用动态语句(ORACLE DYNAMIC SQL)的//第四种方式。动态SQL方法四是在不确定SQL语句的选择项与输入项，且不知个数与数据类型的情况下使用的一种复杂程序设计技术。   
138  
139\---------------------------------------------------------------------------*/   
140  
141int _stdcall ExportTxtfile(const char *Sql/*SQL选择语句*/, const char FileName/*导出目标文本文件名*/)   
142  
143{   
144  
145int null_ok, precision, scale;   
146  
147  
148  
149int handle;   
150  
151  
152  
153if ((handle = open(FileName, O_CREAT|O_TEXT|O_APPEND|O_RDWR, S_IREAD|S_IWRITE)) == -1)   
154  
155{   
156  
157//文件打开出错   
158  
159return -1;   
160  
161}   
162  
163//定义变量，以存放SQL语句   
164  
165EXEC SQL BEGIN DECLARE SECTION;   
166  
167char sqlstr[256];   
168  
169EXEC SQL END DECLARE SECTION;   
170  
171  
172  
173//检查是否连接数据库   
174  
175if (bConnect == false) return -2;   
176  
177  
178  
179strcpy(sqlstr/*.arr*/, Sql);   
180  
181// sqlstr.len = strlen(sql);   
182  
183  
184  
185//给描述区分配空间   
186  
187if ((SelectUnit = sqlald(MAX_ITEMS, MAX_VNAME_LEN, MAX_INAME_LEN)) == (SQLDA *)NULL)   
188  
189{   
190  
191//空间分配失败   
192  
193return -3;   
194  
195}   
196  
197  
198  
199if ((BindUnit = sqlald(MAX_ITEMS, MAX_VNAME_LEN, MAX_INAME_LEN)) == (SQLDA *)NULL)   
200  
201{   
202  
203//空间分配失败   
204  
205return -3;   
206  
207}   
208  
209//给查询返回值存储区分配空间   
210  
211SelectUnit-&gt;N = MAX_ITEMS;   
212  
213for (int i=0; i &lt; MAX_ITEMS; i++)   
214  
215{   
216  
217BindUnit-&gt;I[i] = (short *)malloc(sizeof(short *));   
218  
219BindUnit-&gt;V[i] = (char *)malloc(MAX_VNAME_LEN);   
220  
221}   
222  
223for (int i=0; i &lt; MAX_ITEMS; i++)   
224  
225{   
226  
227SelectUnit-&gt;I[i] = (short *)malloc(sizeof(short *));   
228  
229SelectUnit-&gt;V[i] = (char *)malloc(MAX_VNAME_LEN);   
230  
231}   
232  
233  
234  
235EXEC SQL WHENEVER SQLERROR GOTO sqlerr;//DO sql_error("导出出错");   
236  
237//设置ＳＱＬ语句   
238  
239EXEC SQL PREPARE SQLSA FROM :sqlstr;   
240  
241EXEC SQL DECLARE Cursorbase CURSOR FOR SQLSA;   
242  
243  
244  
245//输入描述处理   
246  
247BindUnit-&gt;N = MAX_ITEMS;   
248  
249EXEC SQL DESCRIBE BIND VARIABLES for SQLSA INTO BindUnit;   
250  
251  
252  
253if (BindUnit-&gt;F &lt; 0)   
254  
255{   
256  
257return -4;   
258  
259//输入项过多   
260  
261}   
262  
263BindUnit-&gt;N = BindUnit-&gt;F;   
264  
265//打开光标   
266  
267EXEC SQL OPEN Cursorbase USING DESCRIPTOR BindUnit;   
268  
269  
270  
271//选择项处理   
272  
273EXEC SQL DESCRIBE SELECT LIST for SQLSA INTO SelectUnit;   
274  
275  
276  
277if (SelectUnit-&gt;F &lt; 0)   
278  
279{   
280  
281return -4;   
282  
283//选择表项过多   
284  
285}   
286  
287SelectUnit-&gt;N = SelectUnit-&gt;F;   
288  
289//因为所有格式，类型都是不确定的，所以要得到正确的返回值就要处理格式   
290  
291for (int i=0; i &lt; SelectUnit-&gt;F; i++)   
292  
293{   
294  
295sqlnul(&amp;(SelectUnit-&gt;T[i]), &amp;(SelectUnit-&gt;T[i]), &amp;null_ok);   
296  
297switch (SelectUnit-&gt;T[i])   
298  
299{   
300  
301case 1://CHAR   
302  
303break;   
304  
305case 2://NUMBER   
306  
307sqlprc(&amp;(SelectUnit-&gt;L[i]), &amp;precision, &amp;scale);   
308  
309if (precision == 0)   
310  
311precision = 40;   
312  
313SelectUnit-&gt;L[i] = precision + 2;   
314  
315break;   
316  
317case 8://LONG   
318  
319SelectUnit-&gt;L[i] = 240;   
320  
321break;   
322  
323case 11://ROWID   
324  
325SelectUnit-&gt;L[i] = 18;   
326  
327break;   
328  
329case 12://DATE   
330  
331SelectUnit-&gt;L[i] = 9;   
332  
333break;   
334  
335case 23://RAW   
336  
337break;   
338  
339case 24://LONGRAW   
340  
341SelectUnit-&gt;L[i] = 240;   
342  
343break;   
344  
345}   
346  
347  
348  
349SelectUnit-&gt;V[i] = (char *)realloc(SelectUnit-&gt;V[i], SelectUnit-&gt;L[i]+1);   
350  
351  
352  
353SelectUnit-&gt;T[i] = 1;//把所有类型转换为字符型   
354  
355}   
356  
357  
358  
359EXEC SQL WHENEVER NOT FOUND goto EndFor;   
360  
361  
362  
363for (;;)   
364  
365{   
366  
367EXEC SQL FETCH Cursorbase USING DESCRIPTOR SelectUnit;   
368  
369  
370  
371//输出各字段   
372  
373for (int i=0; i &lt; SelectUnit-&gt;F; i++)   
374  
375{   
376  
377char buffer[256];   
378  
379  
380  
381if (i != SelectUnit-&gt;F-1)   
382  
383sprintf(buffer, "%s", SelectUnit-&gt;V[i]);   
384  
385else sprintf(buffer, "%s\r\n", SelectUnit-&gt;V[i]);   
386  
387  
388  
389int length = strlen(buffer);   
390  
391  
392  
393if (write(handle, buffer, length) != length)   
394  
395{   
396  
397return -5;   
398  
399//写文件失败 exit(1);   
400  
401}   
402  
403}   
404  
405  
406  
407}   
408  
409  
410  
411EndFor:   
412  
413  
414  
415close(handle);   
416  
417  
418  
419for (int i=0; i &lt; MAX_ITEMS; i++)   
420  
421{   
422  
423if (SelectUnit-&gt;V[i] != (char *)NULL)   
424  
425free(SelectUnit-&gt;V[i]);   
426  
427  
428  
429free(SelectUnit-&gt;I[i]);   
430  
431}   
432  
433  
434  
435for (int j=0; j &lt; MAX_ITEMS; j++)   
436  
437{   
438  
439if (BindUnit-&gt;V[j] != (char *)NULL)   
440  
441free(BindUnit-&gt;V[j]);   
442  
443  
444  
445free(BindUnit-&gt;I[j]);   
446  
447}   
448  
449  
450  
451sqlclu(SelectUnit);   
452  
453sqlclu(BindUnit);   
454  
455  
456  
457EXEC SQL CLOSE Cursorbase;   
458  
459  
460  
461return 0;   
462  
463  
464  
465sqlerr:   
466  
467return -6;   
468  
469}   
470  
471  
472  
473/*----------------------------------------------------------------------------   
474  
475导入文本   
476  
477为了批量导入，在此我调用的sqlldr工具   
478  
479首先生成SQL*Loader控制文件，后运行sqlldr   
480  
481\----------------------------------------------------------------------------*/   
482  
483int _stdcall ImportTxtfile(TList LengthArray/*导入文本的字段长度链表*/,   
484  
485String *FieldArray/*数据库表的了段名数组*/, const char TableName/*导入的目标表*/, const char FileName/*导入的源文本文件*/)   
486  
487{   
488  
489//产生SQL*Loader控制文件   
490  
491FILE *fout, *fp;   
492  
493char Execommand[256];   
494  
495  
496  
497char sqlload[] = ".\ qlload.ctl";   
498  
499  
500  
501//检查是否连接数据库   
502  
503if (bConnect == false) return -2;   
504  
505  
506  
507if ((fout=fopen(sqlload, "w")) == NULL)   
508  
509{   
510  
511//建立控制文件出错   
512  
513return -1 ;   
514  
515}   
516  
517  
518  
519fprintf(fout, "LOAD DATA\n");   
520  
521fprintf(fout, "INFILE '%s'\n", FileName);   
522  
523fprintf(fout, "APPEND INTO TABLE %s (\n", TableName);   
524  
525  
526  
527int iStart = 1;   
528  
529for(int i=0; i &lt; LengthArray-&gt;Count; i++)   
530  
531{   
532  
533fprintf(fout, "%11s POSITION(%d:%d)", FieldArray[i], iStart, *(int*)LengthArray-&gt;Items[i]+iStart-1);   
534  
535iStart += *(int*)LengthArray-&gt;Items[i];   
536  
537fprintf(fout, " CHAR");   
538  
539  
540  
541if(i &lt; LengthArray-&gt;Count-1)   
542  
543fprintf(fout, ",\n");   
544  
545}   
546  
547fprintf(fout, ")\n");   
548  
549fclose(fout);   
550  
551  
552  
553sprintf(Execommand, "sqlldr.exe userid=%s/%s@%s control=%s",   
554  
555User, Pwd, DB, sqlload);   
556  
557  
558  
559if (system(Execommand) == -1)   
560  
561{   
562  
563//SQL*Loader执行错误   
564  
565return -1;   
566  
567}   
568  
569  
570  
571return 0 ;   
572  
573}   
574  
575  
576  
577  
578  
579//----------------------------------------------------------------------------   
580  
581三、编译   
582  
583用ORACLE的PROC预编译器预编后，放入C++ Builder中联编。联编时需加入前面生成的sqlora9.lib。联编时还要注意，所有PROC生成的ORACLE内部函数调用都要说明为extern "C" _declspec(dllexport) TYPE _stdcall类型。   
584  
585  
586  
587水平有限还请见谅！！！请多多指点。ＱＱ：5005647</sys></io.h></fcntl.h></math.h></time.h></string.h></stdlib.h></stdio.h></windows.h></vcl.h>