Microsoft Internet Business Development Kit/PRODUCT_CD.iso/sqlsvr/esqlc/esql/samples.c/example6.sqc

Text File | 1995-11-21 | 8.7 KB | 327 lines

///////////////////////////////////////////////////////////////////////////////
//
//  FILE: example6.sqc
//
//      Sample Embedded SQL for C application
//
//  FUNCTIONS:
//
//      main() - Main program
//      ErrorHandler - Embedded SQL for C error handler
//
//  COMMENTS:
//
//      Copyright (C) 1992 - 1994 Microsoft Corporation
//
///////////////////////////////////////////////////////////////////////////////

// function prototypes (instead of header file)
void ErrorHandler (void);
void multiple_statements();
void cursor_sp();
void invoke_sp();
void select_into();
void branch();

#include <stddef.h>         // standard C run-time header
#include <stdio.h>          // standard C run-time header
#include "gcutil.h"         // utility header

// GLOBAL VARIABLES
EXEC SQL BEGIN DECLARE SECTION;
char prep[200];    // prepared statements
EXEC SQL END DECLARE SECTION;

///////////////////////////////////////////////////////////////////////////////
//
//  FUNCTION: main()
//
//      Main application
//
//  PARAMETERS:
//
//      argc - count of command line args
//      argv - array of command line argument strings
//      envp - array of environment strings
//
//  RETURNS: 0 if successful, 1 if error
//
//  COMMENTS:
//
///////////////////////////////////////////////////////////////////////////////

int main (
    int argc,
    char** argv,
    char** envp)
{
    int nRet;           // for return values

    EXEC SQL BEGIN DECLARE SECTION;
    // for CONNECT TO
    char szServerDatabase[(SQLID_MAX * 2)+2] = "";
    char szLoginPassword[(SQLID_MAX * 2)+2] = "";
    EXEC SQL END DECLARE SECTION;

    // install Embedded SQL for C error handler
    EXEC SQL WHENEVER SQLERROR CALL ErrorHandler();
    // set Embedded SQL for C options
    EXEC SQL SET OPTION LOGINTIME 10;
    EXEC SQL SET OPTION QUERYTIME 100;

    // display logo
    printf("Sample Embedded SQL for C application\n");

    // get info for CONNECT TO statement
    nRet = GetConnectToInfo(argc, argv,
        szServerDatabase,
        szLoginPassword);
    if (!nRet)
    {
        return (1);
    }

    // attempt connection to SQL Server
    EXEC SQL CONNECT TO :szServerDatabase
        USER :szLoginPassword;
    if (SQLCODE == 0)
    {
        printf("Connection to SQL Server established\n");
    }
    else
    {
        // problem connecting to SQL Server
        printf("ERROR: Connection to SQL Server failed\n");
        return (1);
    }

    EXEC SQL SET CURSORTYPE CUR_BROWSE;

    // Demonstrates multiple T-SQL statements in a SQL batch
    multiple_statements();

    // Invoke a stored procedure
    invoke_sp();

    // Use a stored procedure in a cursor definition
    cursor_sp();

    // Select into a temporary table, then use a singleton select
    // on that table
    select_into();

    // Branching in a static SQL batch
    branch();

    // disconnect from SQL Server
    EXEC SQL DISCONNECT ALL;

    return (0);
}

///////////////////////////////////////////////////////////////////////////////
//
//  FUNCTION: ErrorHandler()
//
//      Called on Embedded SQL for C error, displays fields from SQLCA
//
//  PARAMETERS: none
//
//  RETURNS: none
//
//  COMMENTS:
//
///////////////////////////////////////////////////////////////////////////////

void ErrorHandler (void)
{
    // display error information from SQLCA
    printf("Error Handler called:\n");
    printf("    SQL Code = %li\n", SQLCODE);
    printf("    SQL Server Message %li: '%Fs'\n", SQLERRD1, SQLERRMC);
}

/*
** This procedure demonstrates multiple SQL statements in a
** SQL batch.  It also uses BEGIN/COMMIT TRANSACTION inside a
** static SQL batch.
*/

void multiple_statements()
{
    EXEC SQL BEGIN TRANSACTION
        INSERT INTO authors VALUES('123-45-6789', 'Bear', 'Fozzy',
        '206 555-1212', 'Muppet Show', 'Bellevue', 'WA','98005', 1)
        INSERT INTO authors VALUES('234-56-7890', 'Frog', 'Kermit',
        '206 555-1212', 'Sesame Street', 'Redmond', 'WA','98052', 1)
    COMMIT TRANSACTION
    ;

    if (SQLCODE == 0) {
        EXEC SQL DELETE FROM authors
            WHERE au_lname = 'Frog' or au_lname = 'Bear';

        if (SQLCODE == 0) {
            printf("The authors have been inserted and deleted.\n");
        }
    }

    /* Test SQLCODE again since the DELETE might have failed. */
    if (SQLCODE != 0)
    {
        printf ("ERROR: Delete may have failed\n");
    }
}

/* This procedure demonstrates how to invoke stored procedures. */
void invoke_sp()
{
    EXEC SQL BEGIN DECLARE SECTION;
    char  added_type[9];
    EXEC SQL END DECLARE SECTION;

    /* Execute the stored procedure 'sp_addtype'. */
    EXEC SQL EXEC master..sp_addtype new_type, int;

    if (SQLCODE == 0) {
        EXEC SQL
            SELECT name INTO :added_type FROM master..systypes
            WHERE name = 'new_type';

        if (SQLCODE == 0) {
            printf("The type '%s' was added successfully.\n",
                added_type);

            EXEC SQL exec master..sp_droptype new_type;
            if (SQLCODE == 0)
                printf("%s", "It was then deleted successfully.\n");
        }
    }

}


/*
** This procedure uses a stored procedure in a cursor definition.
** Declare the cursor, prepare the stored procedure call, open
** the cursor, fetch all the rows, then close the cursor.
*/

void cursor_sp()
{
    EXEC SQL BEGIN DECLARE SECTION;
    char  spid[6];      /* sp_who.spid */
    char  status[12];   /* sp_who.status */
    char  login_id[13]; /* sp_who.login_id */
    char  hostname[11]; /* sp_who.hostname */
    char  blk[6];       /* sp_who.blk */
    char  dbname[11];   /* sp_who.dbname */
    char  cmd[20];      /* sp_who.cmd */
    EXEC SQL END DECLARE SECTION;

    /* Declare the cursor for a prepared statement BEFORE the
     * cursor is declared.
     */
    EXEC SQL DECLARE stat_cur CURSOR FOR who;

    strcpy(prep, "sp_who");
    EXEC SQL PREPARE who FROM :prep;

    /* Check result of prepare. No point continuing if it failed. */
    if (SQLCODE != 0)
    {
        return;
    }

        EXEC SQL OPEN stat_cur;

    // ignore warnings about truncated character strings
    if (SQLCODE != 0 && SQLCODE != 1)
    {
        return;
    }

    printf("The following are the active processes on this SQL Server:\n");

    printf("spid   status     loginname     hostname   blk   dbname     cmd\n");
    printf("------ ---------- ------------- ---------- ----- ---------- ----------------\n");

    // When no more rows are returned, SQLCODE == 100
    while (SQLCODE == 0) {
        // fetch rows
        EXEC SQL FETCH stat_cur INTO
            :spid, :status, :login_id,
            :hostname, :blk, :dbname, :cmd;

          if (SQLCODE == 0) {
            printf("%6s %10s %-13s %10s %5s %-10s %16s\n",
            spid, status, login_id, hostname, blk, dbname, cmd);
        }

    }

    // SQLCODE should be 100 at this point (no more rows), error if not.
    if (SQLCODE != 100)
    {
        printf("ERROR: SQL Code should be 100;  it is %d\n",SQLCODE);
    }


    EXEC SQL CLOSE stat_cur;

    if (SQLCODE != 0) // Test result of closing the cursor
    {
        printf("ERROR: Closing cursor\n");
    }
}



/*
** Select into a temporary table, then use a singleton select to
** prove it worked.  The holdlock keeps the authors table from
** being modified until we are through.
*/

void select_into()
{
    strcpy(prep, "SELECT * INTO #temp FROM authors holdlock ");
    strcat(prep, "SELECT * FROM #temp WHERE au_fname = 'Reginald'");
    EXEC SQL EXECUTE IMMEDIATE :prep;

    if (SQLCODE != 0) {
        return;
    }

    printf( "A temporary copy of authors table has been created ");
    printf( "and one of the authors was successfully selected.\n");
}

/* This procedure demonstrates branching in a static SQL batch. */
void branch()
{
    EXEC SQL
        DECLARE @looper int
        SELECT @looper = 1
        while @looper < 11
        begin
            if exists (SELECT * FROM authors WHERE au_lname = 'Frog')
            begin
                DELETE FROM authors WHERE au_lname = 'Frog'
            end
            else
            begin
                INSERT INTO authors VALUES('123-45-6789','Frog','Kermit',
                '206 555-1212','Sesame Street','Redmond','WA','98052',1)
            end
            SELECT @looper = @looper + 1
        end
    ;

    if (SQLCODE == 0)
    {
        printf( "A branching and looping statement inside a SQL batch, caused Kermit T. Frog\n");
        printf( "to be inserted and deleted from authors five times.\n");
    }
}