class_odbc.prg

用VFP访问数据库的odbc代码

DEFINE CLASS ODBC as Custom   
   m_dbhandle=-1
   m_error=""
   m_trans=1
   m_cursor = ""
   m_db = ""
   FUNCTION INIT
   	  this.m_dbhandle = -1
   	  this.m_error = ""
   	  this.m_trans = 1
   ENDFUNC 
   FUNCTION destroy 
	  this.Close()
   ENDFUNC 
   FUNCTION close
   	  IF this.m_dbhandle!=-1
   	     IF this.m_trans = 2
   	     	this.Rollback()
   	     ENDIF 
   	  	 SQLDISCONNECT(this.m_dbhandle)
   	  ENDIF  
   	  m_dbhandle = -1
   ENDFUNC 
   FUNCTION open
      PARAMETERS m_dns, m_uid, m_pass
      this.close()
      this.m_dbhandle = SQLCONNECT(m_dns,m_uid,m_pass)
      IF this.m_dbhandle=-1
      	  AERROR(err)
      	  this.m_error = err[1,2]
      	  RETURN .F.
      ELSE 
      	  RETURN .T.
      ENDIF 
   ENDFUNC 
   FUNCTION prepare
   	  PARAMETERS m_sqlstr,m_cursor,m_to_db
   	  LOCAL m_result
   	  m_db = ""
   	  IF VARTYPE(m_cursor)!='C'
	   	  m_result = SQLPREPARE(this.m_dbhandle,m_sqlstr)
	  ELSE 
	  	  m_result = SQLPREPARE(this.m_dbhandle,m_sqlstr,m_cursor)
	  	  IF m_result!=-1 AND VARTYPE(m_to_db)='C'
	  	    this.m_cursor = m_cursor 
	  	  	this.m_db = m_to_db
	  	  ENDIF 
	  ENDIF 
      IF m_result=-1
      	  AERROR(err)
      	  this.m_error = err[1,2]
      	  RETURN .F.
      ELSE 
      	  RETURN .T.
      ENDIF 
   ENDFUNC 
   FUNCTION exec
   	  PARAMETERS m_sqlstr,m_cursor,m_to_db
   	  LOCAL m_result,m_tcur,m_tdb
   	  IF  VARTYPE(m_sqlstr)!='C'
        m_result = SQLEXEC(this.m_dbhandle)
   	    IF m_result!=-1 AND !EMPTY(this.m_db)
   	        m_tcur = this.m_cursor 
   	        m_tdb = this.m_db
   	        IF FILE("&m_tcur..dbf")
   	        	DROP TABLE &m_tcur.
   	        ENDIF 
   	    	COPY TO &m_tcur. DATABASE &m_tdb. 
   	    ENDIF 
   	  ELSE 
   	    IF VARTYPE(m_cursor)!='C'
	   	    m_result = SQLEXEC(this.m_dbhandle,m_sqlstr)
	    ELSE 
	  	    m_result = SQLEXEC(this.m_dbhandle,m_sqlstr,m_cursor)
	  	    IF m_result!=-1 AND VARTYPE(m_to_db)='C'
	   	        IF FILE("&m_cursor..dbf")
   		        	DROP TABLE &m_cursor.
   	    	    ENDIF 
	  	    	COPY TO &m_cursor. DATABASE &m_to_db. 
	  	    ENDIF 
	    ENDIF
	  ENDIF  
      IF m_result=-1
      	  AERROR(err)
      	  this.m_error = err[1,2]
      	  RETURN .F.
      ELSE 
      	  RETURN .T.
      ENDIF       
   ENDFUNC 
   FUNCTION AutoTrans
   	  SQLSETPROP(this.m_dbhandle,"Transactions",1)
   	  m_trans = 1
   ENDFUNC 
   FUNCTION beginTrans
      SQLSETPROP(this.m_dbhandle,"Transactions",2)
      m_trans = 2
   ENDFUNC 
   FUNCTION Commit
      SQLCOMMIT(this.m_dbhandle)
   ENDFUNC 
   FUNCTION Rollback
      SQLROLLBACK(this.m_dbhandle)
   ENDFUNC 
   FUNCTION CreateTable
      PARAMETERS m_tableName, m_alia
      PRIVATE m_result
      result = gen_table_sql_from_alia(m_tableName,m_alia)
      IF NOT this.exec(m_result)
      	RETURN .F.
      ELSE 
      	RETURN .T.
      ENDIF 
   ENDFUNC 
   FUNCTION AppendFrom
      PARAMETERS m_tableName, m_alia, m_trans_rows
      LOCAL m_sqlstr,m_nn 
      IF VARTYPE(m_trans_rows)!='N'
      	m_trans_rows = 0
      ENDIF 
      IF m_trans_rows>0
      	 this.beginTrans()
      ENDIF 
      SELECT &m_alia.
      IF NOT this.exec("select * from &m_tableName. where rownum<2")
      	RETURN .F.
      ENDIF 
      m_sqlstr = "insert into &m_tableName. values ("
   	  m_sqlstr = m_sqlstr + "?"+FIELD(m_nn)
      FOR m_nn = 2 TO FCOUNT()
      	m_sqlstr = m_sqlstr + ", ?"+FIELD(m_nn)
      ENDFOR 
      m_sqlstr = m_sqlstr +")"
      IF NOT this.prepare(m_sqlstr)
      	 RETURN .F.
      ENDIF 
      SCAN 
      		SCATTER MEMVAR 
      		IF NOT this.exec()
      			RETURN .F.
      		ENDIF 
      		IF m_trans_rows>0
      			IF RECNO()%m_trans_rows = 0
      				this.Commit()
      				WAIT WINDOW "Append:"+STR(RECNO()) NOWAIT 
      			ENDIF 
      		ELSE 
      			IF RECNO()%500 = 0
      				WAIT WINDOW "Append:"+STR(RECNO()) NOWAIT 
      			ENDIF 
      		ENDIF 
      ENDSCAN 
      IF m_trans_rows>0
      	this.Commit()
      ENDIF 
      RETURN .T.
   ENDFUNC 
   FUNCTION gen_table_sql_from_alia
		parameter m_table_name,m_alia
		private m_result
		select &m_alia.
		copy stru exten to _tmp_ext
		m_result = "create table &m_table_name. ("
		sele 0
		use _tmp_ext
		scan 
			m_result = m_result + alltrim(field_name)+" "
			do case
			case field_type='C'
				m_result = m_result + "char("+alltrim(str(field_len))+") "+iif(field_null," NULL ","")
			case field_type='N'
				m_result = m_result + "numeric("+alltrim(str(field_len))+","+alltrim(str(field_dec))+") "+iif(field_null," NULL ","")
			CASE field_type='I'
				m_result = m_result + "Integer "+IIF(field_null," NULL ", "")
			CASE field_type='B'
				m_result = m_result + "Double "+IIF(field_null," NULL ", "")
			CASE field_type='F'
				m_result = m_result + "Float "+IIF(field_null," NULL ", "")
			CASE field_type='D'
				m_result = m_result + "date " +IIF(field_null," NULL ", "")
			case field_type='T'
				m_result = m_result + "time "+iif(field_null," NULL ","")
			case field_type='M'
				m_result = m_result + "varchar(1000) "+iif(field_null," NULL ","")
			other 
				messagebox("error type:"+field_type)
				return ""
			endcase 
			if recno()<reccount()
				m_result = m_result +","
			endif 
		endscan 
		use
		erase _tmp_ext.dbf
		m_result = m_result+")"
		return m_result
   ENDFUNC 
ENDDEFINE