[ROOT] TOracleServer: problem with `describe' query

From: Pasha Murat (630)840-8237@169G ((630)840-8237@169G)
Date: Wed May 09 2001 - 02:21:46 MEST


Dear Michael and ROOTers,

I've copied from http://www.gsi.de/computing/root/OracleAccess.htm 
and after struggling a little bit successfully built libOracle.so - it is a real 
pleasure to work with it. Thanks! I hit a couple of minor problems, one
of which I was able to resolve myself. The second problem, however , needs more 
expertise that I have. 

Let me first report a couple of glitches in the installation procedure, may be this
will be useful for the others (I apologize in advance if my problems are trivially 
due to my complete ignorance of ORACLE/SQL)

- I had to modify `pro_cmd' (1-line long installation script) to add one more 
  `include=$GCC_DIR/lib/gcc-lib/i686-pc-linux-gnu/2.95.2/include' statement. I did it
   driven by intuition, it worked, however it was not obvious at all how one adds 
   one more  `-I', so I just got a bit of luck

- Original TOracleServer::GetTables assumes that all the tables are described in 
  the table called `sys.usr_tables'. Its equivalent in CDF Oracle DB is calles 
  `all_tables'. May be the name of this table needs to be made a configurable 
  parameter?

- now - the problem. below you find slightly modified source of the test_sql example, 
  which comes with the original distribution. I found, that I can successfully issue 
  `select' query, however `describe' query fails.

  Wondering if somebody by chance has a clue about what I'm doing wrong...

							-thanks, Pasha

-----------------------------------------------------
root [15] test_sql("select TABLE_NAME FROM all_tables where TABLE_NAME LIKE 'L3%'")
TOracleServer begin 
L3_CALIBS                     
L3_CUT_AND_PARAMETERS         
L3_EXES                       
L3_OPTION_CLASSES             
L3_OPTION_CLASSES_L3_TALKTOS  
L3_OPTION_INSTANCES           
L3_OPTION_SEQUENCES           
L3_TALKTOS                    
L3_TCLS                       
L3PCS                         
L3PCUPDATES                   
end 
root [16] test_sql("describe all_tables")                                          
TOracleServer begin 

ORA-00900: invalid SQL statement
                                     
Parse error at character offset 0 in SQL statement.
end 

------------------------------------------------------------------------------
void test_sql(const char* query) {
					// any legal SQL statement, for example

  char * sql="select table_name from all_tables";

					/*connect with oracle server */

  TSQLServer *db=TSQLServer::Connect("oracle://cdfora.fnal.gov:1521/cdf.fnal.gov",
				     "hod","konem");
  TSQLRow*     row1;
  TSQLResult*  res=db->Query(query);
  if (! res) goto EXIT;
					/*print rows one by one */
  while (row1 = res->Next()) {
    TOracleRow*  row2 = (TOracleRow*)row1;   
    for (int i=0; i<res->GetFieldCount();i++) {
      int j=row2->GetFieldType(i);
      switch(j) {
      case 3:
	printf("%*d ",row2->GetFieldLength(i),*(int*)row2->GetFieldData(i));
	break;
      case 4:
	printf("%*.2f",row2->GetFieldLength(i),*(float*)row2->GetFieldData(i));
  	break;
      default:
	printf("%*.*s",
	       row2->GetFieldLength(i),
	       row2->GetFieldLength(i),
	       row2->GetFieldData(i));  	
      }
    }	
    cout<<endl;
    delete row1;
  }

 EXIT:;
  printf("end \n");
  delete res;
  delete db;
}
------------------------------------------------------------------------



This archive was generated by hypermail 2b29 : Tue Jan 01 2002 - 17:50:44 MET