Software Collection (I)/TOOLS.iso/b18/1.img/EXAMPLE4.BA@/EXAMPLE4.bin

Text File | 1992-09-15 | 4.6 KB | 178 lines

'EXAMPLE4.BAS -- This example illustrates the use of browse-mode functions
'to determine the source of result columns from ad hoc queries.

'Copyright (c) 1990 by Microsoft Corp.  All rights reserved.


'$INCLUDE: 'sql.bi'

DECLARE SUB SendCmd (SqlConn&)
DECLARE SUB ExamineResults (SqlConn&)

MemLeft& = SETMEM(-16384)   'Reserve 16K of heap.
STACK 8192                  'Reserve 8K of stack space.

'Initialize BASIC DB-LIBRARY.
IF SqlInit$ = "" THEN
    PRINT "Can't start BASIC DB-LIBRARY."
    END
END IF

'Get a login record and fill it in with the necessary information.
Login& = SqlLogin&
Result% = SqlSetLUser%(Login&, "loginid")
Result% = SqlSetLPwd%(Login&, "passwd")
Result% = SqlSetLApp%(Login&, "example")

'Get a connection for communicating with SQL Server.
SqlConn& = SqlOpen&(Login&, "server")
IF SqlConn& = 0 THEN END

'Allow the user to type in queries.
DO
    'Send user-generated query.
    CALL SendCmd(SqlConn&)

    'Examine results of user's queries.
    CmdCount = 1
    Result% = SqlResults%(SqlConn&)
    DO
        IF Result% = FAIL THEN
            PRINT : PRINT "Command #"; CmdCount; " failed."
        ELSE
            IF SqlRows%(SqlConn&) = FAIL THEN
                PRINT : PRINT "Command #"; CmdCount; "returned no rows."
            ELSE

                'Print out results of query.
                PRINT : PRINT "Command #"; CmdCount; ":"
                CALL ExamineResults(SqlConn&)

                'Throw away all data rows.
                Result% = SqlCanQuery%(SqlConn&)

            END IF
        END IF
        Result% = SqlResults%(SqlConn&)
        CmdCount = CmdCount + 1
    LOOP UNTIL Result% = NO.MORE.RESULTS
LOOP


'Sample Output
'
'Msg No.: 5701  Severity:  2  Level:  0
'Message: Changed database context from 'master' to 'master'.
'
'Enter SQL query:
'1> select name, date = crdate from sysobjects
'2> for browse
'3> go
'
'Command # 1 :
'
'The following tables were used to generate these query results:
'sysobjects    (not browsable)
'
'These are the columns of the target list and their sources:
'  Result column:             Source:                       Browsable?
'  name                       sysobjects.name               no
'  date                       sysobjects.crdate             no
'
'Enter SQL query:
'1> exit
'

'This routine uses browse-mode functions to find information about the
'results of a query.
'
SUB ExamineResults (SqlConn&)
    PRINT
    TabCount% = SqlTabCount%(SqlConn&)
    IF TabCount% = 0 THEN
        PRINT "Query must contain 'FOR BROWSE'."
        EXIT SUB
    END IF
    PRINT "The following tables were used to generate these query results:"
    FOR TabNum% = 1 TO TabCount%
        Table$ = SqlTabName$(SqlConn&, TabNum%)
        IF Table$ <> "" THEN
            PRINT Table$, "(";
            IF SqlTabBrowse%(SqlConn&, TabNum%) = FAIL THEN PRINT "not ";
            PRINT "browsable)"
        END IF
    NEXT TabNum%

    PRINT
    IF TabCount% THEN
        PRINT "These are the columns of the target list and their sources:"
        ColCount% = SqlNumCols%(SqlConn&)
        PRINT "  Result column:"; TAB(30); "Source:"; TAB(60); "Browsable?"
        FOR ColNum% = 1 TO ColCount%
            TabNum% = 0
            Table$ = SqlTabSource$(SqlConn&, ColNum%, TabNum%)
            Source$ = SqlColSource$(SqlConn&, ColNum%)
            IF Table$ = "" THEN
                Source$ = "(result of expression)"
            ELSE
                Source$ = Table$ + "." + Source$
            END IF
            PRINT "  "; SqlColName$(SqlConn&, ColNum%);
            PRINT TAB(30); Source$; TAB(60);
            IF SqlColBrowse%(SqlConn&, ColNum%) = SUCCEED THEN
                PRINT "yes"
            ELSE
                PRINT "no"
            END IF
        NEXT ColNum%
    END IF
END SUB

'This routine sends an ad hoc query typed in by the user.
'
SUB SendCmd (SqlConn&)
    PRINT : PRINT "Enter SQL query:"
    Count% = 0
    DO
        Count% = Count% + 1
        PRINT CHR$(Count% + 48); "> ";
        LINE INPUT Query$

        IF Query$ = "go" THEN
            Result% = SqlExec%(SqlConn&)
            EXIT DO
        ELSEIF Query$ = "exit" THEN
            PRINT
            CALL SqlExit
            END
        ELSE
            'Keep reading SQL commands into buffer.
            Result% = SqlCmd%(SqlConn&, Query$ + " ")
        END IF
    LOOP
END SUB

'User-defined handlers.  BASIC DB-LIBRARY will call these procedures if
'there is an error or a message from the server.
'
FUNCTION UserSqlErrorHandler% (SqlConn&, State%, DbErr%, OsErr%, DbErrStr$, OsErrStr$)
    PRINT
    IF SqlDead%(SqlConn&) THEN
        PRINT "Connection to SQL Server failed."
        UserSqlErrorHandler% = INTEXIT
        EXIT FUNCTION
    END IF
    PRINT "State: "; State%; " Error No: "; DbErr%; " OS Error: "; OsErr%
    PRINT "DB-LIBRARY Error: "; DbErrStr$
    IF OsErr% <> DBNOERR THEN PRINT "Operating-System Error: "; OsErrStr$
    UserSqlErrorHandler% = INTCANCEL
END FUNCTION

SUB UserSqlMsgHandler (SqlConn&, MsgNo&, Severity%, Level%, Msg$)
    PRINT
    PRINT "Msg No.:"; MsgNo&; " Severity: "; Severity%; " Level: "; Level%
    PRINT "Message: "; Msg$
END SUB