/* the implement of TOracleServer, TOracleResult. 2000,3,18 by T.GUO*/ #include "OracleServer.h" #include "OracleResult.h" #include "OracleRow.h" #include #include #include #include #include #include extern SQLDA *sqlald(); extern void sqlnul(); ClassImp(TOracleServer) //______________________________________________________________________________ TOracleServer::TOracleServer(const char *db, const char *uid, const char *pw) { // Open a connection to a Oracle DB server. The db arguments should be // of the form "oracle://[:][/]", e.g.: // "oracle://pcroot.cern.ch:3456/test". The uid is the username and pw // the password that should be used for the connection. char user_dbase[80]; EXEC ORACLE OPTION (ORACA=YES); EXEC ORACLE OPTION (RELEASE_CURSOR=YES); EXEC SQL INCLUDE sqlda; EXEC SQL INCLUDE sqlca; EXEC SQL BEGIN DECLARE SECTION; VARCHAR username[UNAME_LEN]; // VARCHAR is an ORACLE supplied struct varchar password[PWD_LEN]; // varchar can be in lower case also EXEC SQL END DECLARE SECTION; parse_flag = 0; TUrl url(db); printf("TOracleServer begin \n"); if (!url.IsValid()) { Error("TOracleServer", "malformed db argument %s", db); MakeZombie(); return; }else { if (strncmp(url.GetProtocol(), "oracle", 6)) { Error("TOracleServer", "protocol in db argument should be oracle it is %s", url.GetProtocol()); MakeZombie(); return; } else { const char *dbase = 0; if (strcmp(url.GetFile(), "/")) dbase = url.GetFile()+1; //skip leading / strcpy(user_dbase,uid); strcat(strcat(user_dbase,"@"),dbase); username.len = (unsigned short)strlen(strcpy((char *)username.arr,user_dbase)); password.len = (unsigned short)strlen(strcpy((char *)password.arr, pw)); (void) setjmp(jmp_continue1); if (parse_flag ) return; else/* allocate memory for the select and bind descriptors. */ if (alloc_descriptors(MAX_ITEMS, MAX_VNAME_LEN, MAX_INAME_LEN) != 0) return; else { EXEC SQL WHENEVER SQLERROR DO connect_error(); parse_flag = 1; EXEC SQL CONNECT :username IDENTIFIED BY :password; parse_flag = 0; fType = "Oracle"; fHost = url.GetHost(); fDB = dbase; fPort = url.GetPort(); return ; } } } } //______________________________________________________________________________ TOracleServer::~TOracleServer() { // Close connection to Oracle DB server. memory_free(); EXEC SQL WHENEVER SQLERROR DO sql_error(); EXEC SQL commit WORK RELEASE; Close(); } //______________________________________________________________________________ void TOracleServer::Close(Option_t *) { if (IsConnected()) fPort = -1; fDB=" "; } //______________________________________________________________________________ TSQLResult *TOracleServer::Query(const char *sql) { // Execute SQL command. Result object must be deleted by the user. // Returns a pointer to a TSQLResult object if successful, 0 otherwise. // The result object must be deleted by the user. Int_t fRowCount; TSQLResult *sqlrst=0; EXEC SQL BEGIN DECLARE SECTION; char dyn_statement[1024]; EXEC SQL VAR dyn_statement IS STRING(1024); EXEC SQL END DECLARE SECTION; if (!IsConnected()) { Error("Query", "not connected"); return 0; }else { strcpy(dyn_statement,sql); (void) setjmp(jmp_continue); if (parse_flag ) return 0; else { /* Process SQL statements. */ /* Prepare the statement and declare a cursor. */ EXEC SQL WHENEVER SQLERROR DO sql_error(); parse_flag = 1; /* Set a flag for sql_error(). */ EXEC SQL PREPARE S FROM :dyn_statement; parse_flag = 0; /* Unset the flag. */ EXEC SQL DECLARE C CURSOR FOR S; /* Open the cursor and execute the statement. * If the statement is not a query (SELECT), the * statement processing is completed after the * OPEN. */ EXEC SQL OPEN C USING DESCRIPTOR bind_dp; /* Call the function that processes the select-list. * If the statement is not a query, this function * just returns, doing nothing. */ if ((strncmp(dyn_statement, "SELECT", 6) != 0) && (strncmp(dyn_statement, "select", 6) != 0)) { select_dp->F = 0; fRowCount= sqlca.sqlerrd[2]; EXEC SQL WHENEVER SQLERROR CONTINUE; /* Close the cursor. */ EXEC SQL CLOSE C; EXEC SQL WHENEVER SQLERROR DO sql_error(); } else { process_select_list(); fRowCount=RowCount(); EXEC SQL OPEN C USING DESCRIPTOR bind_dp; sqlrst=new TOracleResult(select_dp,scale,fRowCount); } return sqlrst; } } } //______________________________________________________________________________ Int_t TOracleServer::RowCount() {// Get the count of processed rows. int fRowCount; for ( ; ;) { EXEC SQL WHENEVER NOT FOUND GOTO end_select_loop; EXEC SQL FETCH C USING DESCRIPTOR select_dp; fRowCount=sqlca.sqlerrd[2]; } end_select_loop: return fRowCount; } //______________________________________________________________________________ Int_t TOracleServer::SelectDataBase(const char *dbname) { // Select a database. Returns 0 if successful, non-zero otherwise. //For Oracle, only be used check dbname printf("SelectDataBasE------Just to check whether the database is connected !\n"); if (!IsConnected()) { Error("SelectDataBase", "not connected"); return 0; }else { if (strcmp(fDB,dbname)) { Error("SelectDataBase", "no such database"); return 0; }else return 1;//guo } } //______________________________________________________________________________ TSQLResult *TOracleServer::GetDataBases(const char *wild) { // List all available databases. Wild is for wildcarding "t%" list all // databases starting with "t". ////For Oracle,do nothing printf("GetDataBases----do nothing !\n"); if (!IsConnected()) { Error("GetDataBases", "not connected"); return 0; }else return 0;//guo } //______________________________________________________________________________ TSQLResult *TOracleServer::GetTables(const char *dbname, const char *wild) { // List all tables in the specified database. Wild is for wildcarding // "t%" list all tables starting with "t". // Returns a pointer to a TSQLResult object if successful, 0 otherwise. // The result object must be deleted by the user. if (!IsConnected()) { Error("GetTables", "not connected"); return 0; }else { if (strcmp(fDB,dbname)!=0) { Error("GetTables", "no such database %s", dbname); return 0; }else { char *sql; if (wild) sql = Form("select TABLE_NAME FROM sys.user_tables where TABLE_NAME LIKE %s", wild); else sql = Form("select TABLE_NAME FROM sys.user_tables"); return Query(sql); } } } //______________________________________________________________________________ TSQLResult *TOracleServer::GetColumns(const char *dbname, const char *table,const char *wild) { // List all columns in specified table in the specified database. // Wild is for wildcarding "t%" list all columns starting with "t". // Returns a pointer to a TSQLResult object if successful, 0 otherwise. // The result object must be deleted by the user. if (!IsConnected()) { Error("GetColumns", "not connected"); return 0; }else { if (SelectDataBase(dbname)==0) { Error("GetColumns", "no such database %s", dbname); return 0; }else { char *sql; if (wild) sql = Form("select column_name from sys.user_tab_columns where (table_name=%s) AND (column_name like %s)", table, wild); else sql = Form("select column_name from sys.user_tab_columns where table_name=%s",table); return Query(sql); } } } //______________________________________________________________________________ Int_t TOracleServer::CreateDataBase(const char *dbname) { // Create a database. Returns 0 if successful, non-zero otherwise. // for Oracle, this function do nothing. printf("CreateDataBase----do nothing !\n"); if (!IsConnected()) { Error("CreateDataBase", "not connected"); // return 0; } return 0;//guo } //______________________________________________________________________________ Int_t TOracleServer::DropDataBase(const char *dbname) { // Drop (i.e. delete) a database. Returns 0 if successful, non-zero // otherwise. // for Oracle, this function do nothing. printf("DropDataBase----do nothing !\n"); if (!IsConnected()) { Error("DropDataBase", "not connected"); // return 0; } return 0;//guo } //______________________________________________________________________________ Int_t TOracleServer::Reload() { // Reload permission tables. Returns 0 if successful, non-zero // otherwise. User must have reload permissions. // for Oracle, this function do nothing. printf("Reload----do nothing !\n"); if (!IsConnected()) { Error("Reload", "not connected"); // return 0; } return 0;//guo } //______________________________________________________________________________ Int_t TOracleServer::Shutdown() { // Shutdown the database server. Returns 0 if successful, non-zero // otherwise. User must have shutdown permissions. // for Oracle, this function do nothing. printf("Shoudown----do nothing !\n"); if (!IsConnected()) { Error("Shutdown", "not connected"); //return 0; } return 0;//guo } //______________________________________________________________________________ const char *TOracleServer::ServerInfo() { // Return server info. if (!IsConnected()) { Error("ServerInfo", "not connected"); return 0; }else { char info[256]; char *sql="select banner from sys.v_$version where banner like '%Oracle%'"; TSQLResult *res_info=Query(sql); TSQLRow *row_info=res_info->Next(); while(row_info) { strcpy(info, row_info->GetField(0)); row_info=res_info->Next(); } delete res_info; delete row_info; return info;//guo } } //-------------------------------------------------------------------- Int_t TOracleServer::alloc_descriptors(Int_t size, Int_t max_vname_len, Int_t max_iname_len) { /* * The first sqlald parameter determines the maximum number of * array elements in each variable in the descriptor. In * other words, it determines the maximum number of bind * variables or select-list items in the SQL statement. * * The second parameter determines the maximum length of * strings used to hold the names of select-list items * or placeholders. The maximum length of column * names in ORACLE is 30, but you can allocate more or less * as needed. * * The third parameter determines the maximum length of * strings used to hold the names of any indicator * variables. To follow ORACLE standards, the maximum * length of these should be 30. But, you can allocate * more or less as needed. */ Int_t i; if ((bind_dp = sqlald(size, max_vname_len, max_iname_len)) == (SQLDA *) 0) { fprintf(stderr, "Cannot allocate memory for bind descriptor."); return -1; /* Have to exit in this case. */ }else { if ((select_dp = sqlald (size, max_vname_len, max_iname_len)) == (SQLDA *) 0) { fprintf(stderr, "Cannot allocate memory for select descriptor."); return -1; }else { select_dp->N = MAX_ITEMS; /* Allocate the pointers to the indicator variables, and the actual data. */ for (i = 0; i < MAX_ITEMS; i++) { bind_dp->I[i] = (short *) malloc(sizeof (short)); select_dp->I[i] = (short *) malloc(sizeof(short)); bind_dp->V[i] = (char *) malloc(1); select_dp->V[i] = (char *) malloc(1); } return 0; } } } //----------------------------------------------------------------- void TOracleServer::process_select_list() { Int_t i, null_ok, precision; /* If the SQL statement is a SELECT, describe the select-list items. The DESCRIBE function returns their names, datatypes, lengths (including precision and scale), and NULL/NOT NULL statuses. */ select_dp->N = MAX_ITEMS; EXEC SQL DESCRIBE SELECT LIST FOR S INTO select_dp; /* If F is negative, there were more select-list items than originally allocated by sqlald(). */ if (select_dp->F < 0) { printf ("\nToo many select-list items (%d), maximum is %d\n", -(int)(select_dp->F),MAX_ITEMS); return; }else { /* Set the maximum number of array elements in the descriptor to the number found. */ select_dp->N = select_dp->F; /* Allocate storage for each select-list item. sqlprc() is used to extract precision and scale from the length (select_dp->L[i]). sqlnul() is used to reset the high-order bit of the datatype and to check whether the column is NOT NULL. CHAR datatypes have length, but zero precision and scale. The length is defined at CREATE time. NUMBER datatypes have precision and scale only if defined at CREATE time. If the column definition was just NUMBER, the precision and scale are zero, and you must allocate the required maximum length. DATE datatypes return a length of 7 if the default format is used. This should be increased to 9 to store the actual date character string. If you use the TO_CHAR function, the maximum length could be 75, but will probably be less (you can see the effects of this in SQL*Plus). ROWID datatype always returns a fixed length of 18 if coerced to CHAR. LONG and LONG RAW datatypes return a length of 0 (zero), so you need to set a maximum. In this example, it is 240 characters. */ for (i = 0; i < select_dp->F; i++) { /* Turn off high-order bit of datatype (in this example, it does not matter if the column is NOT NULL). */ sqlnul ((unsigned short *)&(select_dp->T[i]), (unsigned short *)&(select_dp->T[i]), &null_ok); switch (select_dp->T[i]) { case 1 : /* CHAR datatype: no change in length needed, except possibly for TO_CHAR conversions (not handled here). */ break; case 2 : /* NUMBER datatype: use sqlprc() to extract precision and scale. */ sqlprc ((unsigned long *)&(select_dp->L[i]), &precision, &scale); /* Allow for maximum size of NUMBER. */ if (precision == 0) precision = 40; /* Also allow for decimal point and possible sign. */ /* convert NUMBER datatype to FLOAT if scale > 0, INT otherwise. */ if (scale > 0) select_dp->L[i] = sizeof(float); else select_dp->L[i] = sizeof(Int_t); break; case 8 : /* LONG datatype */ select_dp->L[i] = 240; break; case 11 : /* ROWID datatype */ select_dp->L[i] = 18; break; case 12 : /* DATE datatype */ select_dp->L[i] = 9; break; case 23 : /* RAW datatype */ break; case 24 : /* LONG RAW datatype */ select_dp->L[i] = 240; break; } /* Allocate space for the select-list data values. sqlald() reserves a pointer location for V[i] but does not allocate the full space for the pointer. */ if (select_dp->T[i] != 2) select_dp->V[i] = (char *) realloc(select_dp->V[i], select_dp->L[i] + 1); else select_dp->V[i] = (char *) realloc(select_dp->V[i], select_dp->L[i]); /* Coerce ALL datatypes except for LONG RAW and NUMBER to character. */ if (select_dp->T[i] != 24 && select_dp->T[i] != 2) select_dp->T[i] = 1; /* Coerce the datatypes of NUMBERs to float or Int_t depending on the scale. */ if (select_dp->T[i] == 2) if (scale > 0) select_dp->T[i] = 4; /* float */ else select_dp->T[i] = 3; /* Int_t */ } return; } } //------------------------------------------------------------ void TOracleServer:: sql_error() { error_info(); EXEC SQL WHENEVER SQLERROR CONTINUE; EXEC SQL ROLLBACK WORK; longjmp(jmp_continue, 1); } //------------------------------------------------------------ void TOracleServer:: connect_error() { error_info(); EXEC SQL WHENEVER SQLERROR CONTINUE; longjmp(jmp_continue1, 1); } //---------------------------------------------------- void TOracleServer:: error_info() { /* ORACLE error handler */ printf ("\n%.70s\n",sqlca.sqlerrm.sqlerrmc); if (parse_flag) printf ("Parse error at character offset %d in SQL statement.\n", (int)sqlca.sqlerrd[4]); } //---------------------------------------------------- void TOracleServer:: memory_free() { /* When done, free the memory allocated for pointers in the bind and select descriptors. */ Int_t i; for (i = 0; i < MAX_ITEMS; i++) { free(bind_dp->V[i]); free(bind_dp->I[i]); /* MAX_ITEMS were allocated. */ free(select_dp->V[i]); free(select_dp->I[i]); /* MAX_ITEMS were allocated. */ } /* Free space used by the descriptors themselves. */ /* sqlclu(bind_dp); sqlclu(select_dp);*/ } /* * The next part is the implement of TOracleResult */ ClassImp(TOracleResult) //______________________________________________________________________________ TOracleResult::TOracleResult(SQLDA *result,Int_t scale,Int_t RowCount) { // Oracle query result. fResult=(SQLDA*)result; fScale=scale; fRowCount = RowCount; } //______________________________________________________________________________ TOracleResult::~TOracleResult() { // Cleanup Oracle query result. if (fResult) Close(); } //______________________________________________________________________________ void TOracleResult::Close(Option_t *) { // Close query result. if (!fResult) return; else { fResult = 0; fRowCount = 0; return; } } //______________________________________________________________________________ Bool_t TOracleResult::IsValid(Int_t field) { // Check if result set is open and field index within range. if (!fResult) { Error("IsValid", "result set closed"); return kFALSE; }else { if (field < 0 || field >= GetFieldCount()) { Error("IsValid", "field index out of bounds"); return kFALSE; }else return kTRUE; } } //______________________________________________________________________________ Int_t TOracleResult::GetFieldCount() { // Get number of fields in result. if (!fResult) { Error("GetFieldCount", "result set closed"); return 0; }else return fResult->F;//guo } //______________________________________________________________________________ const char *TOracleResult::GetFieldName(Int_t field) {// Get name of specified field. char fieldname[1201]; //shoul be MAX_ITEMS*MAX_VNAME_LEN char *chartemp; int position; int ch=' '; if (!IsValid(field)) return 0;else { chartemp = strchr( fResult->S[field], ch ); position=chartemp-fResult->S[field]; strcpy(fieldname,fResult->S[field]); fieldname[position]='\0'; return fieldname; } } //______________________________________________________________________________ TSQLRow *TOracleResult::Next() { // Get next query result row. The returned object must be // deleted by the user. if (!fResult) { Error("Next", "result set closed"); return 0; } else { /* FETCH each row selected */ EXEC SQL WHENEVER NOT FOUND GOTO end_select_loop; EXEC SQL FETCH C USING DESCRIPTOR fResult; return new TOracleRow(fResult); end_select_loop: EXEC SQL WHENEVER SQLERROR CONTINUE; EXEC SQL CLOSE C; EXEC SQL WHENEVER SQLERROR DO sql_error(); return 0; } }