adocore.java

vc ADO 连接数据库

            Cmd1.putActiveConnection( Conn1 );

            // Dump Command Properties
            List1.addItem( "*** *** *** *** *** Dumping contents of the Properties Collection for the Command object *** *** *** *** ***" );
            Cmd1.putCommandText( "SELECT * FROM Authors" );
            Utl1.DumpProperty( List1, Conn1, Cmd1.getProperties() );

            //------------------
            // Recordset Object
            //------------------
            vtEmpty.noParam();      // Hmm...
            vtEmpty2.noParam();

            // Create Recordset Object
            Rs1 = Cmd1.Execute( vtEmpty, vtEmpty2, msado15.CommandTypeEnum.adCmdText );

            // Dump Recordset Properties
            List1.addItem( "*** *** *** *** *** Dumping contents of the Properties Collection for the Recordset object *** *** *** *** ***" );
            Utl1.DumpProperty( List1, Conn1, Rs1.getProperties() );

            //--------------
            // Field Object
            //--------------

            // Create Field Object (if necessary)
            // Dump Field Properties
            List1.addItem( "*** *** *** *** *** Dumping contents of the Properties Collection for the Field object *** *** *** *** ***" );
            varTemp.putInt( 0 );
            Utl1.DumpProperty( List1, Conn1, Rs1.getFields().getItem( varTemp ).getProperties() );

            // Successful Shutdown
            List1.addItem( "*** Success! ***" );
        }
        // Catch Blocks
        catch (com.ms.com.ComFailException e)    
        {
            Utl1.LogException( e, List1, Conn1 );
        }
        catch(Exception e) 
        {
            Utl1.LogException( e, List1, Conn1 );
        }

        //----------------------------------
        // Miscellaneous (graceful) Cleanup
        // with quiet error trapping
        //----------------------------------

        if ( Rs1    != null ) Rs1    = null;
        if ( Cmd1   != null ) Cmd1   = null;
        if ( Conn1  != null ) Conn1  = null;
    }

    public void CodeTemplate( List List1 )
    {  
       // Using the helper routines, this shows what a typical
       // code fragment using ADO would need in order to provide
       // accurate error handling.  

        msado15._Connection  Conn1 = new msado15.Connection();
   
        // Trap any error/exception
        try
        {
            //----------------------------------
            // (Fail To) Open Connection Object
            //----------------------------------
            
            // Warm & Fuzzy for user
            List1.clear();
            List1.addItem( "ADO Code Template..." );
            List1.addItem( "    ...Deliberately opening conneciton object with no connection information to generate an error" );
   
            // Create Connection Object(1.5 Version)
            Conn1.Open( bstrEmpty, bstrEmpty, bstrEmpty, -1 );

            //----------------------
            // YOUR CODE GOES HERE!
            //----------------------

            // Successful Shutdown
            List1.addItem( "*** (Unexpected) Success! ***" );
        }
        // Catch Blocks
        catch (com.ms.com.ComFailException e)    
        {
            Utl1.LogException( e, List1, Conn1 );
        }
        catch(Exception e) 
        {
            Utl1.LogException( e, List1, Conn1 );
        }

        //----------------------------------
        // Miscellaneous (graceful) Cleanup
        // with quiet error trapping
        //----------------------------------

        // Close any open objects
        if ( Conn1    != null ) Conn1   = null;
    }

    public void InputOutputReturnParams( List List1 )
    {  
        msado15._Connection  Conn1   = new msado15.Connection();
        msado15._Command     Cmd1    = null;
        msado15._Recordset   Rs1     = new msado15.Recordset();

        Variant              v1 = new Variant();    
        Variant              v2 = new Variant();    
        boolean              bEof;
        String               SQLServerName        = new String();
        String               bstrSQLServerConnect = new String();

        // Trap any error/exception
        try
        {
            //-------------------------------------
            // Determine SQL Server to Connect too
            //-------------------------------------

            SQLServerName        = "Scep";
            bstrSQLServerConnect = "driver={sql server};server=" + SQLServerName + ";Database=pubs;UID=sa;PWD=;";

            List1.addItem( bstrSQLServerConnect );

            //------------------------
            // Open Connection Object
            //------------------------
            // Warm & Fuzzy for user
            List1.clear();
            List1.addItem( "Demonstrating Return, Input and Output parameters..." );
            List1.addItem( "    ...Assumes SQL Server named " + SQLServerName );
            List1.addItem( "    ...With Error Handling Using Connection Object" );
            List1.addItem( "    ...uses stored procedure sp_AdoTest" );

            // Create Connection Object (1.5 Version)
            Conn1.putConnectionString( bstrSQLServerConnect );
            Conn1.Open( bstrEmpty, bstrEmpty, bstrEmpty, -1 );

           //--------------------------
           // Create Stored Procedures
           //--------------------------

            List1.addItem( "Creating Stored Procedure..." );

            // Drop Procedure
            vtEmpty.noParam();      // Hmm...
            Conn1.Execute( bstrSQLDrop, vtEmpty, msado15.CommandTypeEnum.adCmdText);
        
            // Create Procedure
            vtEmpty.noParam();      // Hmm...
            Conn1.Execute( bstrSQLCreate, vtEmpty, msado15.CommandTypeEnum.adCmdText);
        
            //-----------------------------------
            // Open Parameterized Command Object
            //-----------------------------------

            List1.addItem( "Opening a ForwardOnly Recordset from a Parameterized Command Object..." );
            List1.addItem( "...creating command object" );

            // Create Command Object
            Cmd1= new msado15.Command();
            Cmd1.putActiveConnection( Conn1 );
            Cmd1.putCommandText     ( bstrStoredProc );
            Cmd1.putCommandType     ( msado15.CommandTypeEnum.adCmdStoredProc );

            // Fill Parameters Collection
            List1.addItem( "...refreshing parameters collection off stored procedure" );
            Cmd1.getParameters().Refresh();
            v1.putInt( 1 );
            v2.putInt( 10 );
            Cmd1.getParameters().getItem( v1 ).putValue( v2 );

            // Limit scope of Recordset object (so later we can easily fetch ret/out params)
            List1.addItem( "...opening Recordset" );

            // Open Recordset
            vtEmpty.noParam();      // Hmm...
            vtEmpty2.noParam();     // Hmm...
            Rs1 = Cmd1.Execute( vtEmpty, vtEmpty2, msado15.CommandTypeEnum.adCmdUnknown );

            // Dump the recordset
            List1.addItem( "...Dumping Recordset" );

            bEof = Rs1.getEOF();
            while ( bEof == false )
            {
                String s;
                String strTmp;

                strTmp = "    ";

                // Dump Fields Collection
                for( int l = 0; l < Rs1.getFields().getCount(); l++ )
                {
                    v1.putInt( l );
                    strTmp += Rs1.getFields().getItem( v1 ).getValue();
                }

                // Add record
                List1.addItem( strTmp );

                // MoveNext
                Rs1.MoveNext();
                bEof = Rs1.getEOF();
            }

            Rs1.Close();
        
            // Display Parameters Collection (with caveat for user)
            List1.addItem( "...It is strictly Driver/Provider dependent whether you have to close" );
            List1.addItem( "the recordset to retrieve output/return parameters.  With the release of" );
            List1.addItem( "of the SQL Server ODBC Driver with ODBC 3.X, you have to close the recordset." );
            List1.addItem( "Previous versions of this driver did not have this requirement, which itself" );
            List1.addItem( "came about as part of a bug fix in previous versions of the driver." );
  
            // Get first parameter
            v1.putInt( 0 );
            List1.addItem( "    RetVal Param = " + Cmd1.getParameters().getItem( v1 ).getValue() );

            // Get second parameter
            v1.putInt( 1 );
            List1.addItem( "    Input  Param = " + Cmd1.getParameters().getItem( v1 ).getValue() );

            // Get third parameter
            v1.putInt( 2 );
            List1.addItem( "    Output Param = " + Cmd1.getParameters().getItem( v1 ).getValue() );

            // Successful Shutdown
            List1.addItem( "*** Success! ***" );
        }
        // Catch Blocks
        catch (com.ms.com.ComFailException e)    
        {
            Utl1.LogException( e, List1, Conn1 );
        }
        catch(Exception e) 
        {
            Utl1.LogException( e, List1, Conn1 );
        }

        //----------------------------------
        // Miscellaneous (graceful) Cleanup
        // with quiet error trapping
        //----------------------------------

        // Close any open objects
        if ( Rs1      != null ) Rs1     = null;
        if ( Cmd1     != null ) Cmd1    = null;
        if ( Conn1    != null ) Conn1   = null;
    }
}