3. ΠΡΟΓΡΑΜΜΑΤΙΣΜΟΣ ΜΕ ΒΑΣΕΙΣ ΔΕΔΟΜΕΝΩΝ

download 3. ΠΡΟΓΡΑΜΜΑΤΙΣΜΟΣ ΜΕ ΒΑΣΕΙΣ ΔΕΔΟΜΕΝΩΝ

of 56

description

Programming with databases

Transcript of 3. ΠΡΟΓΡΑΜΜΑΤΙΣΜΟΣ ΜΕ ΒΑΣΕΙΣ ΔΕΔΟΜΕΝΩΝ

  • SQL

    (Jdbc) .

    . 2

  • SQL SQL.

    SQL SQL .

    DBMS

    .

    . 3

  • (API). DBMS, .

    . 4

  • B i D l S ti Begin Declare Section

    End Declare Section SQL :

    SQL

    (sqlca.h)y SQLSTATEy SQLCODE

    . 6

  • SQL

    C EXEC SQLEXEC SQL ;

    Java SQLJ Java #SQL

    . 7

  • . SQL C Q

    . 8

  • #include #include #i l d t i h#include #include

    EXEC SQL BEGIN DECLARE SECTION;EXEC SQL BEGIN DECLARE SECTION; char database[6]; char userid[9];char passwd[19];char passwd[19];

    EXEC SQL END DECLARE SECTION;

    . 9

  • int main(int argc, char *argv[]) {if (argc == 4) {

    strcpy (database, argv[1]); strcpy (userid, argv[2]);strcpy (passwd, argv[3]);EXEC SQL CONNECT TO :database

    USER :userid using :passwd;} else {

    printf ("\nUSAGE: 1 [database] [userid] [passwd]\n\n");return 1;

    }

    . 10

  • if (sqlca.sqlcode != 0 ){( q q ){printf("Error during logon.\n");

    it(2)exit(2);} else {} {

    printf("log on successful. \n");}}

    . 11

  • EXEC SQL WHENEVER SQLERROR goto error;EXEC SQL WHENEVER SQLERROR goto error;

    EXEC SQL CREATE TABLE liEXEC SQL CREATE TABLE supplier(id CHAR(5) not null primary key,name CHAR(20),status INTEGER,status INTEGER,city CHAR(15));

    EXEC SQL COMMIT;EXEC SQL DISCONNECT;exit(0);exit(0);

    . 12

  • error:error:printf("SQL error:%ld\n",sqlca.sqlcode);printf("\n%.70s\n",sqlca.sqlerrm.sqlerrmc); EXEC SQL ROLLBACK WORK;Q ;exit(1);

    }}

    . 13

  • . 14

  • . 15

  • . 16

  • SQL C Q

    . 17

  • #include #include #include #include #include

    EXEC SQL BEGIN DECLARE SECTION;char supp id[5];char supp_id[5];char supp_name[20];long int supp status;long int supp_status;char supp_city[15];char database[6];char database[6];char userid[9];char passwd[19];char passwd[19];

    EXEC SQL END DECLARE SECTION;

    . 18

  • int main(int argc, char *argv[]){if (argc == 4) {

    strcpy (database argv[1]); strcpy (userid argv[2]); strcpy (passwd argv[3]);strcpy (database, argv[1]); strcpy (userid, argv[2]); strcpy (passwd, argv[3]);EXEC SQL CONNECT TO :database USER :userid USING :passwd;

    }else {

    printf ("\nUSAGE: 2 [database] [userid] [passwd]\n\n");return 1;;

    } /* endif */if (sqlca.sqlcode != 0 ){

    printf("Error during logon \n");printf( Error during logon.\n );exit(2);}

    else {printf("log on successful. \n");}}

    . 19

  • EXEC SQL WHENEVER SQLERROR goto errorwhile (1) {while (1) {

    printf("Enter the Supplier's code (or X to end): ");scanf("%s" supp id);scanf( %s ,supp_id);

    if (supp_id[0] == 'X' || supp_id[0] == 'x) {printf("Supposed to terminate now...\n");p ( pp );break; }

    printf("Supplier's name: ");scanf("%s",supp_name);printf("Supplier's status: ");scanf("%ld",&supp_status);

    i tf("S li ' it ")printf("Supplier's city: ");scanf("%s",supp_city);

    . 20

  • EXEC SQL INSERT INTO supplier (id,name,status,city) Q pp ( , , , y)VALUES (:supp_id,:supp_name,:supp_status,:supp_city);

    printf("SQLCA.Error.Code: %ld\n", sqlca.sqlcode);

    EXEC SQL COMMIT WORK;printf("Record %s added.\n\n",supp id);p ( , pp_ );

    }EXEC SQL COMMIT WORK;Q ;

    EXEC SQL CONNECT RESET;exit(0);exit(0);

    . 21

  • error:printf("SQL error:%ld\n",sqlca.sqlcode);printf("\n% 70s\n" sqlca sqlerrm sqlerrmc);printf( \n%.70s\n ,sqlca.sqlerrm.sqlerrmc);

    EXEC SQL ROLLBACK WORK;EXEC SQL DISCONNECT;C SQ SCO C ;exit(1);

    }}

    . 22

  • ()

    . 23

  • #include #include #include #include #include #include

    EXEC SQL BEGIN DECLARE SECTION;char supp_id[5];char supp_name[20];long int supp_status;char supp city[15];char supp_city[15];char database[6];char userid[9];[ ];char passwd[19];

    EXEC SQL END DECLARE SECTION;

    . 24

  • int main(int argc, char *argv[]) {if (argc == 4) {if (argc == 4) {

    strcpy (database, argv[1]); strcpy (userid, argv[2]);strcpy (passwd argv[3]);strcpy (passwd, argv[3]);EXEC SQL CONNECT TO :database USER :userid USING :passwd;

    }else {else {

    printf ("\nUSAGE: 3 [database] [userid] [passwd]\n\n");return 1;

    }}if (sqlca.sqlcode != 0 ){

    printf("Error during logon.\n");it(2)exit(2);

    }else {

    i tf("l f l \ ")printf("log on successful. \n");};

    . 25

  • EXEC SQL WHENEVER SQLERROR goto error; printf("Enter the id number of the Supplier you wish to retrieve: ");scanf("%s",supp_id);

    EXEC SQL SELECT id,name,status,cityINTO :supp id :supp name :supp status :supp cityINTO :supp_id,:supp_name,:supp_status,:supp_cityFROM supplierWHERE id = :supp_id;

    f( S % \ ) f( % \ )printf("Supplier ID: %s\n",supp_id); printf("Name : %s\n",supp_name);printf("Status : %ld\n",supp_status); printf("City : %s\n",supp_city);

    . 26

  • error:i tf("SQL %ld\ " l l d )printf("SQL error:%ld\n",sqlca.sqlcode);

    printf("\n%.70s\n",sqlca.sqlerrm.sqlerrmc);EXEC SQL COMMIT WORK; ;EXEC SQL DISCONNECT;exit(0);

    }

    . 27

  • , .. C

    -- .

    . 28

  • DECLARE [INSESITIVE] [SCROLL][ ] [ ]

    CURSOR FOR [ORDER BY ][ORDER BY -][FOR READ ONLY | FOR UPDATE][ O O | O U ]

    . 29

  • OPENy

    FETCH FETCHy -

    CLOSE

    . 30

  • dy read only .y

    read only WHERE CURRENT OF CU O UPDATE DELETE . y byy by

    default (FOR UPDATE).

    . 31

  • DECLARE NewRatingCursor CURSOR FORSELECT Sname, rating FROM SailorsFOR UPDATE OF tiFOR UPDATE OF rating

    OPEN NewRatingCursor

    FETCH NewRatingCursorINTO :Sailors_name, :Sailors_rating

    . 32

  • RATING H N R ti :New_Rating.

    RATING RATING UPDATE SailorsSET rating = :New ratingSET rating = :New_ratingWHERE CURRENT OF NewRatingCursor

    CLOSE NewRatingCursor

    . 33

  • RATING H N R ti :New_Rating.

    RATING RATING UPDATE SailorsSET rating = :New rating

    SQL:2003

    SET rating = :New_ratingWHERE CURRENT OF NewRatingCursor

    CLOSE NewRatingCursor

    . 34

  • scroll

    EXEC SQL DECLARE cur_employeeSCROLL CURSOR FORSCROLL CURSOR FOR

    . 35

  • NEXT NEXT PRIOR FIRST FIRST LAST ABSOLUTE no ABSOLUTE no RELATIVE no FORWARD no FORWARD no BACKWARD no FETCH FORWARD 5 FROM NewRatingCursor FETCH FORWARD 5 FROM NewRatingCursor

    INTO :Sailors_Sname, :Sailors_rating;

    . 36

  • y

    . 37

  • () .

    . 38

  • #include #include #include #include #include #include

    EXEC SQL BEGIN DECLARE SECTION;char supp_id[5];char supp_name[20];long int supp_status;char supp city[15];char supp_city[15];char database[6];char userid[9];[ ];char passwd[19];

    EXEC SQL END DECLARE SECTION;

    . 39

  • int main(int argc, char *argv[]) {

    i t l thint a_length;char a_city[15];

    if (argc == 4) { strcpy (database, argv[1]); strcpy (userid, argv[2]);strcpy (passwd, argv[3]);EXEC SQL CONNECT TO :database USER :userid USING :passwd;

    }else {

    printf ("\nUSAGE: prog1 [database] [userid] [passwd]\n\n");return 1;

    }if (sqlca.sqlcode != 0 ){

    printf("Error during logon.\n");exit(2);}

    else {printf("log on successful. \n");p ( g )}

    EXEC SQL WHENEVER SQLERROR goto error;

    EXEC SQL WHENEVER NOT FOUND goto done;Q g ;

    . 40

  • EXEC SQL DECLARE cursor1 CURSOREXEC SQL DECLARE cursor1 CURSOR FOR SELECT id t t itSELECT id,name,status,cityFROM supplierppORDER BY name;

    EXEC SQL OPEN cursor1;C SQ O cu so ;

    . 41

  • for (;;)(;;){strcpy(supp_id," "); strcpy(supp_name," ");strcpy(supp city " "); supp status = 0;strcpy(supp_city, ); supp_status = 0;

    EXEC SQL FETCH cursor1EXEC SQL FETCH cursor1INTO

    :supp id :supp name :supp status :supp city;:supp_id,:supp_name,:supp_status,:supp_city;

    printf("-------------------------------------\n");printf( -------------------------------------\n );printf("Supplier ID: %s\n",supp_id);printf("Name : %s\n",supp_name);printf("Status : %ld\n" supp status);printf( Status : %ld\n ,supp_status);printf("City : %s\n",supp_city);

    }

    . 42

  • error:printf(" \n");printf( \n );printf(" \n");printf("SQL error:%ld\n",sqlca.sqlcode);printf("\n%.70s\n",sqlca.sqlerrm.sqlerrmc);( )EXEC SQL DISCONNECT;

    done:done:printf("no more rows found\n");

    EXEC SQL CLOSE cursor1;exit(0);exit(0);

    EXEC SQL COMMIT WORK;EXEC SQL DISCONNECT;EXEC SQL DISCONNECT;exit(0);

    }

    . 43

  • . Prepare Prepare Execute

    . 44

  • SQL

    . 45

  • #include #i l d t h#include #include #include #include #include

    EXEC SQL BEGIN DECLARE SECTION;char onebuffer[50]; h d t b [6]char database[6];

    char userid[9];char passwd[19];char passwd[19];EXEC SQL END DECLARE SECTION;

    . 46

  • int main(int argc, char *argv[]) {char onestring[10];char onestring[10]; int acounter;

    strcpy(onestring,"");strcpy(onebuffer "");strcpy(onebuffer, );

    if (argc == 4) { strcpy (database, argv[1]); strcpy (userid, argv[2]); strcpy (passwd, argv[3]);EXEC SQL CONNECT TO :database USER :userid USING :passwd;EXEC SQL CONNECT TO :database USER :userid USING :passwd;

    }else {

    printf ("\nUSAGE: 5 [database] [userid] [passwd]\n\n");return 1;

    } /* endif */

    if ( l l d ! 0 ){if (sqlca.sqlcode != 0 ){printf("Error during logon.\n");exit(2);}}

    else {printf("log on successful. \n");} }

    . 47

  • EXEC SQL WHENEVER SQLERROR GOTO error;

    printf("Enter the name of the table you wish to create: ");printf( Enter the name of the table you wish to create: );

    scanf("%s",onestring);strcpy(onebuffer,"CREATE TABLE ");strcpy(onebuffer, CREATE TABLE );strcat(onebuffer,onestring);strcat(onebuffer,"(child char(15), age integer)");strcat(onebuffer, (child char(15), age integer) );

    EXEC SQL EXECUTE IMMEDIATE :onebuffer;EXEC SQL EXECUTE IMMEDIATE :onebuffer;EXEC SQL COMMIT WORK;EXEC SQL DISCONNECT;exit(0);

    error:printf("SQL error: %ld\n",sqlca.sqlcode);printf("\n%.70s\n",sqlca.sqlerrm.sqlerrmc);EXEC SQL ROLLBACK WORK;

    Q OEXEC SQL DISCONNECT;exit(1);

    }

    . 48

  • SQL

    . 49

  • #include #i l d tdlib h#include #include #include #include #include

    EXEC SQL BEGIN DECLARE SECTION;char onebuffer[70]; /* communication buffer */h hild[15]char child[15];

    long int age;char database[6];char database[6];char userid[9];char passwd[19];EXEC SQL END DECLARE SECTION;

    . 50

  • int main(int argc, char *argv[]) {char onestring[10]; /* user states*/char onestring[10]; / user states /int acounter;

    strcpy(onestring,"");strcpy(onebuffer "");strcpy(onebuffer, );if (argc == 4) {

    strcpy (database, argv[1]); strcpy (userid argv[2]);strcpy (userid, argv[2]);strcpy (passwd, argv[3]);EXEC SQL CONNECT TO :database USER :userid USING :passwd;

    }else {else {

    printf ("\nUSAGE: 6 [database] [userid] [passwd]\n\n");return 1;

    }

    if (sqlca.sqlcode != 0 ){printf("Error during logon.\n");exit(2);}

    else {else {printf("log on successful. \n");}

    EXEC SQL WHENEVER SQLERROR GOTO error;EXEC SQL WHENEVER SQLERROR GOTO error;

    . 51

  • i tf("E t th f th t bl i h t d li t ")printf("Enter the name of the table you wish to append lines to: ");

    scanf("%s",onestring);strcpy(onebuffer,"INSERT INTO ");strcat(onebuffer,onestring);( , g);strcat(onebuffer,"(child, age) VALUES (?, ?)");EXEC SQL PREPARE s1 FROM :onebuffer;EXEC SQL PREPARE s1 FROM :onebuffer;

    . 52

  • while (1){ for (acounter=0;acounter
  • EXEC SQL COMMIT WORK;EXEC SQL DISCONNECTEXEC SQL DISCONNECT;exit(0);

    error:printf("SQL error: %ld\n",sqlca.sqlcode);printf("\n%.70s\n",sqlca.sqlerrm.sqlerrmc);EXEC SQL ROLLBACK WORK;EXEC SQL DISCONNECTEXEC SQL DISCONNECT;exit(1);

    }}

    . 54

  • EXEC SQL PREPARE employee_statement FROM 'SELECT emp_lname FROM employee';

    EXEC SQL DECLARE cur_employeeCURSOR FOR emplo ee statementCURSOR FOR employee_statement;

    . 55