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

Text File | 1995-12-19 | 12.4 KB | 420 lines

///////////////////////////////////////////////////////////////////////////////
//
//  FILE: example7.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 read_authors();
void add_author();
void update_confirmation();
void update_remove_author();
void remove_authors();
void rollback_transaction();
void titles_trigger();
void current_users();
void stored_procedure();
void pause();

#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 first_name[41];    // authors.au_fname
char last_name[21];     // authors.au_lname
char phone[13];         // authors.phone
char addr[41];          // authors.address
char city[21];          // authors.city
char state[3];          // authors.state
char zip[6];            // authors.zip
int  contract;          // authors.contract
char title_id[7];       // titles.title_id
char title[81];         // titles.title
int  ytd_sales;         // titles.ytd_sales
char spid[7];           // sp_who.spid
char id_status[11];     // sp_who.status
char loginame[13];      // sp_who.loginame
char hostname[11];      // sp_who.hostname
char blk[6];            // sp_who.blk
char DBname[11];        // sp_who.dbname
char cmd[17];           // sp_who.cmd
char added_type[81];        // sp_addtype/sp_droptype
char prep[81];          // prepared statements
char server1[81];       // server.database
char server2[81];       // server.database
char user1[81];         // user.password
char user2[81];         // user.password
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);
    }

    // Cursor select with holdlock, begin/commit transaction
    read_authors();
    pause();

    // Static insert, multiple SQL statements in a single batch
    add_author();
    read_authors();
    pause();

    // Static update and delete, branching in static SQL statement
    update_remove_author();

    // Singleton select into
    update_confirmation();
    update_remove_author();
    pause();
    read_authors();
    pause();

    // Prepared delete
    remove_authors();

    // Static insert, multiple SQL statements in a single batch,
    // begin/rollback transaction
    rollback_transaction();
    read_authors();
    pause();

    // Execute immediate, trigger enforcement
    titles_trigger();

    // Stored procedure in cursor definition
    current_users();

    // Invoke a stored procedure
    stored_procedure();

    // 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);
}

void read_authors()
{
    /* Static cursor, non-updatable, holdlock keeps the table locked
     * until the transaction is completed.
     */
    printf( "View all the names from the authors table using a cursor select.\n" );
    EXEC SQL begin transaction;
    EXEC SQL declare stat_cur cursor for select_stat;

    strcpy( prep, "select au_fname, au_lname from authors holdlock");
    EXEC SQL prepare select_stat from :prep;

    EXEC SQL SET CURSORTYPE CUR_STANDARD;

    EXEC SQL open stat_cur;

    /* sqlcode = 100 means that now rows were retrieved */
    while (SQLCODE == 0) {
        /* fetch the next row from the table and display it. */
        EXEC SQL fetch stat_cur into :first_name, :last_name;

        if (SQLCODE == 0)
            printf( "%s %s\n", first_name, last_name );
        else if (SQLCODE != 100)
            printf("ERROR: Executing FETCH\n");
    }
    EXEC SQL close stat_cur;
    EXEC SQL commit transaction;
}



/* Static insert, multiple commands in a single batch, commit transaction. */
void add_author()
{
    printf( "Static insert into the authors table of Dybing, ");
    printf( "Fosse, Kothny, and Kermit T. Frog.\n");
    EXEC SQL begin transaction
        insert into authors values("123-45-6789", "Dybing", "Steve",
        "206 454-2030", "One Microsoft Way", "Redmond", "WA",
        "98052", 1)
        insert into authors values("234-56-7890", "Fosse", "Ernie",
        "206 454-2030", "One Microsoft Way", "Redmond", "WA",
        "98052", 1)
        insert into authors values("345-67-8901", "Kothny", "Lilian",
        "206 454-2030", "One Microsoft Way", "Redmond", "WA",
        "98052", 1)
        insert into authors values("456-78-9012", "Frog", "Kermit",
        "206-555-1212", "Sesame Street", "Anytown", "NY",
        "10019", 1)
        commit transaction
    ;
}


/* Singleton select into program variables. */
void update_confirmation()
{
    printf( "Singleton select to view an inserted author: Dybing\n");
    EXEC SQL
        select au_fname,au_lname
        into :last_name, :first_name
        from authors
        where au_lname = "Dybing"
    ;
    if (SQLCODE == 0) {
        printf( "%s %s\n", first_name, last_name);
    } else {
        printf("ERROR: Select failed\n");
    }
}


/* static update and delete, branching in a static sql batch. */
void update_remove_author()
{
    printf( "Branching in a static SQL batch.  If the inserted ");
    printf( "author, Steve Dybing, has not been modified, change his ");
    printf( "first name to Stephen.  If already done, delete him.\n");
    EXEC SQL begin transaction
        if exists (select * from authors
            where au_fname = "Steve" and au_lname = "Dybing")
            update authors
            set au_fname = "Stephen"
            where au_lname = "Dybing"
        else
            delete from authors
                where au_lname = "Dybing"
        commit transaction
    ;
}


/* Prepared delete */
void remove_authors()
{
    printf( "Remove the rest of the inserted authors.\n" );
    strcpy( prep, "delete from authors where au_lname = ?");
    EXEC SQL prepare prepared_delete from :prep;

    if (SQLCODE == 0) {
        strcpy( last_name, "Kothny");
        EXEC SQL execute prepared_delete using :last_name;

        strcpy( last_name, "Fosse");
        EXEC SQL execute prepared_delete using :last_name;

        strcpy( last_name, "Frog" );
        EXEC SQL execute prepared_delete using :last_name;
    }
}


/* Static insert, multiple commands in a single batch, rollback transaction. */
void rollback_transaction()
{
    printf( "Insert four new authors, Gates, Ballmer, Shirley, and ");
    printf( "Gaudette, into the authors table.  Then rollback the ");
    printf( "transaction to remove them.\n");
    EXEC SQL
    BEGIN TRANSACTION rollback_authors
    insert into authors values("111-11-1111", "Gates", "William",
    "206 882-8080", "One Microsoft Way", "Redmond", "WA",
    "98052", 1)
    insert into authors values("222-22-2222", "Ballmer", "Steve",
    "206 882-8080", "One Microsoft Way", "Redmond", "WA",
    "98052", 1)
    insert into authors values("333-33-3333", "Shirley", "John",
    "206 882-8080", "One Microsoft Way", "Redmond", "WA",
    "98052", 1)
    insert into authors values("444-44-4444", "Gaudette", "Frank",
    "206-882-8080", "One Microsoft Way", "Redmond", "WA",
    "98052", 1)
    ROLLBACK TRANSACTION rollback_authors
    ;
}


/* Execute immediate, foreign key constraint prevents deletion. */
void titles_trigger()
{
    printf( "Try to delete a title, BU1111, from the titles table\n");
    printf( "to show that referntial constraints are enforced.  \n");
    strcpy( prep, "delete titles where title_id = 'BU1111'");
    EXEC SQL execute immediate :prep;

    if (SQLCODE == 0) {
        EXEC SQL
        select title_id,  title,  ytd_sales
        into  :title_id, :title, :ytd_sales
        from titles
        where title_id = "BU1111"
        ;
        if (SQLCODE == 0 || SQLCODE == 1) {
            printf( "Deltitle did not allow the deletion of: %s\n", title_id);
            printf( "%s ", title);
            printf( "since it has sold %d copies this year.\n", ytd_sales );
        }
    }
}


/* Stored procedure in a cursor definition. */
void current_users()
{
    printf( "View the current users via a stored procedure, sp_who,\n");
    printf( "in a cursor definition.\n");
    EXEC SQL declare stat_cur2 cursor for who;

    strcpy(prep, "sp_who");
    EXEC SQL prepare who from :prep;

    EXEC SQL SET CURSORTYPE CUR_BROWSE;
    EXEC SQL open stat_cur2;
    if (SQLCODE != 0 && SQLCODE != 1) {
        printf("ERROR: Opening cursor\n");
    }

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

    while (SQLCODE == 0) {
        /* Fetch the next row from the table and display it. */
        EXEC SQL
            fetch stat_cur2 into :spid, :id_status, :loginame,
            :hostname, :blk, :DBname, :cmd
        ;
        if (SQLCODE == 0) {
            printf(" %s %s %s %s %s %s %s\n",
            spid, id_status, loginame, hostname, blk, DBname, cmd);
        } else if (SQLCODE != 100) {
            printf("ERROR: Executing fetch\n");
        }
    }
    EXEC SQL close stat_cur2;
}

/* Use the exec command to execute a stored procedure. */
void stored_procedure()
{
    printf( "Using the stored procedures sp_addtype and sp_droptype\n");
    printf( "to add and drop the datatype: ");

    EXEC SQL exec master..sp_addtype demo_new_type, int;

    if (SQLCODE == 0) {
        EXEC SQL
            select name into :added_type
            from master..systypes
            where name = "demo_new_type"
        ;
        if (SQLCODE == 0 || SQLCODE == 1) {
            printf( "%s\n", added_type  );
            EXEC SQL
                exec master..sp_droptype demo_new_type
            ;
        }
    }
}

/* Allow the user to set the pace */
void pause()
{
    char junk[80];

    printf( "Press ENTER to continue" );
    gets( junk );
}