void addData(PGconn *conn){ //dodanie przykladowych danych doSQL(conn, "INSERT INTO cars (make, model, price, capacity, yop) values('Audi', '80', 6000.0, 1.8, '04.05.1991')"); doSQL(conn, "INSERT INTO cars (make, model, price, capacity, yop) values('Volkswagen', 'Sharan', 8000.0, 2.0, '16.03.1997')"); doSQL(conn, "INSERT INTO cars (make, model, price, capacity, yop) values('Audi', 'A3', 10000.0, 1.6, '25.12.1997')"); doSQL(conn, "INSERT INTO cars (make, model, price, capacity, yop) values('Seat', 'Ibiza', 6000.0, 1.9, '01.09.2001')"); doSQL(conn, "INSERT INTO cars (make, model, price, capacity, yop) values('Renault', 'Megane', 15000.0, 2.2, '10.02.2006')"); }
int main() { PGresult *result; PGconn *conn; conn = PQconnectdb(""); if(PQstatus(conn) == CONNECTION_OK) { printf("connection made\n"); doSQL(conn, "DROP TABLE number"); doSQL(conn, "CREATE TABLE number ( value INTEGER, name VARCHAR )"); doSQL(conn, "INSERT INTO number values(42, 'The Answer')"); doSQL(conn, "INSERT INTO number values(29, 'My Age')"); doSQL(conn, "INSERT INTO number values(29, 'Anniversary')"); doSQL(conn, "INSERT INTO number values(66, 'Clickety-Click')"); doSQL(conn, "SELECT * FROM number WHERE value = 29"); doSQL(conn, "UPDATE number SET name = 'Zaphod' WHERE value = 42"); doSQL(conn, "DELETE FROM number WHERE value = 29"); } else printf("connection failed: %s\n", PQerrorMessage(conn)); PQfinish(conn); return EXIT_SUCCESS; }
int SetupDocument::GetActiveDocuments(TDocument*& docs, int & count) { int state; char *str; str = (char *) calloc(strlen(GET_ACTIVE_DOCUMENTS) + 2, sizeof(char)); if(str == NULL) printf("NULL CALLOC!!! parameter str\n"); strcat(str, GET_ACTIVE_DOCUMENTS); state = doSQL(myconnection, str); free(str); if ( state < 0 ) return -1; count = PQntuples(result); if ( count == 0 ) { PQclear(result); return 0; } docs = (TDocument *) calloc(count, sizeof(TDocument)); for ( int i = 0; i < count; i++ ) { docs[i].id = atoi(PQgetvalue(result, i ,0)); strcpy( docs[i].name, PQgetvalue(result, i ,1) ); strcpy( docs[i].path, PQgetvalue(result, i ,2) ); } PQclear(result); return 0; }
int SetupSamplebarcode::query_Update(TSampleBarcode* sam) { //printf("SetupSamplebarcode::UpdateSamples(%s)\n", sam->prefix); int state; char * str; str = (char *) calloc(strlen(UPDATE_SAMPLE) + strlen(UPDATE_SAMPLE2) + 60, sizeof(char)); if(str == NULL) printf("NULL CALLOC!!! parameter str\n"); strcat(str, UPDATE_SAMPLE); strcat(str, sam->prefix); strcat(str, ", pattern = '"); strcat(str, sam->sample), strcat(str, "', multiplier = "); strcat(str, sam->multiplier); strcat(str, UPDATE_SAMPLE2); strcat(str, sam->prefix); //printf("str = %s\n", str); state = doSQL(myconnection, str); free(str); return state; }
void searchBook(char dataPar[50]){ char sqlsearchscript[300]="SELECT * FROM ksiazka WHERE tytul='"; strcat(sqlsearchscript,dataPar); strcat(sqlsearchscript,"';"); doSQL(conn,sqlsearchscript); }
void deleteAutor(char pom[50]){ char sqlsearchscript[300]="DELETE FROM autor WHERE id_autor='"; strcat(sqlsearchscript,pom); strcat(sqlsearchscript,"';"); doSQL(conn,sqlsearchscript); printf("Usunieto pomyslnie !! \n\n"); }
void deleteBook(char pom[50]){ char sqlsearchscript[300]="DELETE FROM ksiazka WHERE id_ksiazki='"; strcat(sqlsearchscript,pom); strcat(sqlsearchscript,"';"); doSQL(conn,sqlsearchscript); printf("Usunieto pomyslnie !! \n\n"); }
//dodanie do bazy void add(PGconn *conn){ char zapytanie[200]; char make[20], model[20], yop[10]; double price, capacity; printf("Set make: "); scanf("%s", make); printf("Set model: "); scanf("%s", model); printf("Set price: "); scanf("%lf", &price); printf("Set capacity: "); scanf("%lf", &capacity); printf("Set year of production: "); scanf("%s", yop); printf("Your data: %s, %s, %lf, %lf, %s", make, model, price, capacity, yop); sprintf(zapytanie, "INSERT INTO cars (make, model, price, capacity, yop) VALUES(\'%s\', \'%s\', %.2lf, %.1lf, \'%s\')", make, model, price, capacity, yop); doSQL(conn, zapytanie); }
//wyszukiwanie w bazie void search(PGconn *conn){ int id; char zapytanie[200]; printf("Set id car to search: "); scanf("%d", &id); sprintf(zapytanie, "SELECT * FROM cars WHERE id_car = %d", id); doSQL(conn, zapytanie); }
int SetupSamplebarcode::query_Insert(const char* scheme_name, char prefix[3], char sample[28], char multiplier[22]) { //printf("SetupSamplebarcode::InsertSample(%s)\n", prefix); int state; char * str; char * scheme_id; str = (char *) calloc(strlen(GET_SCHEME_ID) +strlen(scheme_name) + 3, sizeof(char)); if(str == NULL) printf("NULL CALLOC!!! parameter str\n"); strcat(str, GET_SCHEME_ID); strcat(str, scheme_name); strcat(str, "'"); state = doSQL(myconnection, str); free(str); if ( state < 0 ) return state; if ( PQntuples(result) != 1) { printf("Scheme_name not valid or scheme name was not found!!!\n"); return -4;} scheme_id = (char*) calloc(PQgetlength(result, 0, 0) + 1, sizeof(char)); strcpy(scheme_id, PQgetvalue(result, 0, 0)); PQclear(result); str = (char *) calloc(strlen(INSERT_SAMPLE) + strlen(scheme_id) + 160, sizeof(char)); if(str == NULL) printf("NULL CALLOC!!! parameter str\n"); strcat(str, INSERT_SAMPLE); strcat(str, scheme_id); strcat(str, ", "); strcat(str, prefix); strcat(str, ", '"); strcat(str, sample); strcat(str, "', "); strcat(str, multiplier); strcat(str, ")"); //printf("str = %s\n", str); state = doSQL(myconnection, str); free(str); return state; }
int SetupDocument::AddDocument(const char* scheme_name, const char* name, const char* path) { int state; char * str; char * scheme_id; str = (char *) calloc(strlen(GET_SCHEME_ID) + 2*strlen(scheme_name) + 3, sizeof(char)); if(str == NULL) printf("NULL CALLOC!!! parameter str\n"); strcat(str, GET_SCHEME_ID); strcat(str, scheme_name); strcat(str, "'"); state = doSQL(myconnection, str); free(str); if ( state < 0 ) return -1; if ( PQntuples(result) != 1) { printf("Scheme_name not valid or scheme name was not found!!!\n"); return -4;} scheme_id = (char*) calloc(PQgetlength(result, 0, 0) + 1, sizeof(char)); strcpy(scheme_id, PQgetvalue(result, 0, 0)); PQclear(result); str = (char *) calloc(strlen(ADD_DOCUMENT) + 2*strlen(scheme_id) + 2*strlen(name) + 2*strlen(path) + 10, sizeof(char)); if(str == NULL) printf("NULL CALLOC!!! parameter str\n"); strcat(str, ADD_DOCUMENT); strcat(str, scheme_id); strcat(str, ", '"); strcat(str, name); strcat(str, "', '"); strcat(str, path); strcat(str, "')"); //printf("str = %s\n", str); state = doSQL(myconnection, str); free(str); return state > 0 ? 0 : -1; }
//==================================================================================// int SetupSamplebarcode::SaveInScheme(const char * scheme_name, TSampleBarcode* sam, int sam_count) { //printf("SetupSamplebarcode:: SaveInScheme(%s)\n", scheme_name); int state = doSQL(myconnection, "BEGIN WORK"); if ( state < 0 ) return state; state = query_DeleteSamples(scheme_name); if ( state < 0 ) { state = doSQL(myconnection, "ROLLBACK WORK"); printf("Insert discount was not!!\n"); return -1; } for (int i = 0; i < sam_count; i++ ) { state = AddNewSample(scheme_name, &sam[i]); if ( state < 0 ) { state = doSQL(myconnection, "ROLLBACK WORK"); printf("Update discount was not!!\n"); return -1; } } state = doSQL(myconnection, "COMMIT WORK"); return state; }
//usuniecie elementy z bazy void drop(PGconn *conn){ int id; char zapytanie[200]; printf("Set id car to delete: "); scanf("%d", &id); searchTMP(conn, id); if(searchT){ sprintf(zapytanie, "DELETE FROM cars WHERE id_car = %d", id); doSQL(conn, zapytanie); } searchT = 1; }
int SetupSamplebarcode::query_Delete(TSampleBarcode* sam) { //printf("SetupSamplebarcode::DeleteSample(%s)\n", sam->prefix); int state; char * str; str = (char *) calloc(strlen(DELETE_SAMPLE) + 5, sizeof(char)); if(str == NULL) printf("NULL CALLOC!!! parameter str\n"); strcat(str, DELETE_SAMPLE); strcat(str, sam->prefix); //printf("str = %s\n", str); state = doSQL(myconnection, str); free(str); return state; }
//==================================================================================// int SetupSamplebarcode::query_DeleteSamples(const char*scheme_name) { //printf("SetupSamplebarcode::query_DeleteSamples(const char*scheme_name = %s)\n", scheme_name); int state; char * str; str = (char *) calloc(strlen(DELETE_SAMPLES) + strlen(scheme_name) +5, sizeof(char)); strcat(str, DELETE_SAMPLES); strcat(str, scheme_name); strcat(str, "'"); strcat(str, ")"); //printf("str = %s\n", str); state = doSQL(myconnection, str); free(str); return state; }
//==================================================================================// int SetupDocument::DeleteDocuments(const char*scheme_name) { int state; char * str; str = (char *) calloc(strlen(DELETE_DOCUMENTS) + 2*strlen(scheme_name) +5, sizeof(char)); strcat(str, DELETE_DOCUMENTS); strcat(str, scheme_name); strcat(str, "'"); strcat(str, ")"); //printf("str = %s\n", str); state = doSQL(myconnection, str); free(str); return state > 0 ? 0 : -1; }
int main() { PGresult *result; PGconn *conn; char odp, *name, *zapytanie; int value; conn = PQconnectdb(""); if(PQstatus(conn) == CONNECTION_OK) { printf("connection made\n"); doSQL(conn, "DROP TABLE number"); doSQL(conn, "CREATE TABLE number ( value INTEGER PRIMARY KEY, name VARCHAR )"); printf("\n\n ---- czy wpisujemy co¶ do tabeli? (t/n) "); while( (odp = getchar()) == 't' || (odp == 'T') ) { getchar(); printf(" numer = "); scanf("%i",&value); printf(" nazwa = "); scanf("%s",name); getchar(); sprintf(zapytanie, "INSERT INTO number VALUES(%d, \'%s\')", value, name); doSQL(conn, zapytanie); printf("\n\n ---- czy wpisujemy co¶ do tabeli? (t/n) "); } doSQL(conn, "SELECT * FROM number"); doSQL(conn, "UPDATE number SET name = 'Zaphod' WHERE value = 1"); doSQL(conn, "DELETE FROM number WHERE value = 2"); doSQL(conn, "SELECT * FROM number"); } else printf("connection failed: %s\n", PQerrorMessage(conn)); PQfinish(conn); return EXIT_SUCCESS; }
int SetupSamplebarcode::GetSamples(const char* scheme_name, TSampleBarcode* sambar, int sam_count) { //printf("SetupSamplebarcode::GetSamples(%s)\n", scheme_name); int state; char *str; str = (char *) calloc(strlen(GET_SAMPLES) + strlen(scheme_name) + 2, sizeof(char)); if(str == NULL) printf("NULL CALLOC!!! parameter str\n"); strcat(str, GET_SAMPLES); strcat(str, scheme_name); strcat(str, "'"); state = doSQL(myconnection, str); if ( state < 0 ) return state; if ( PQntuples(result) != sam_count ) { printf("Error: definition discounts count!!\n"); return -4; } //printf("samP_count = %d \n", sam_count); int i; for ( i = 0; i < sam_count; i++ ) { sambar[i].id = i; // если в бд текст большей длины, томожет быть ошибка, проверить strcpy( sambar[i].prefix, PQgetvalue(result, i ,0) ); strcpy( sambar[i].sample, PQgetvalue(result, i ,1) ); strcpy( sambar[i].multiplier, PQgetvalue(result, i ,2) ); //printf("sambar[%d] prefix = <%s>, name = <%s>, multi = <%s>\n", i, sambar[i].prefix, sambar[i].sample, sambar[i].multiplier); } free(str); PQclear(result); //printf("was get %d discounts \n", i); return state; }
int SetupSamplebarcode::GetCountSamples(const char * scheme_name, int& sam_count) { int state; char * str; str = (char *) calloc(strlen(GET_COUNT_SAMPLES) + strlen(scheme_name) + 2, sizeof(char)); if(str == NULL) printf("NULL CALLOC!!! parameter str\n"); strcat(str, GET_COUNT_SAMPLES); strcat(str, scheme_name); strcat(str, "'"); state = doSQL(myconnection, str); free(str); if ( state < 0 ) {printf("Error: state = %d\n", state); return state;} if ( PQntuples(result) != 1) return -4; sam_count = atoi(PQgetvalue(result, 0, 0) ); PQclear(result); //printf("dis count = %d\n", (*dis_count)); return state; }
int main(void){ PGresult *result; PGconn *conn; int z; conn = PQconnectdb("host=localhost port=5432 dbname=dsienkiewicz user=dsienkiewicz password=aplikacje"); if(PQstatus(conn) == CONNECTION_OK) { printf("connection made\n"); //czyszczenie ekranu system("clear"); //menu do{ printf("\n 1 - Create table"); printf("\n 2 - Add sample data"); printf("\n 3 - Print"); printf("\n 4 - Add"); printf("\n 5 - Drop from table"); printf("\n 6 - Update"); printf("\n 7 - Search"); printf("\n 8 - Add Trigger"); printf("\n 9 - Drop Trigger"); printf("\n 10 - Drop Table"); printf("\n 11 - Exit"); printf("\n choice: "); scanf("%d", &z); switch(z){ case 1: doSQL(conn, "CREATE TABLE cars(id_car SERIAL PRIMARY KEY, make VARCHAR(20), model VARCHAR(20), price money, capacity numeric(8,1), yop date);"); break; case 2: addData(conn); break; case 3: print(conn); break; case 4: add(conn); break; case 5: drop(conn); break; case 6: update(conn); break; case 7: search(conn); break; case 8: createTrigger(conn); break; case 9: delTrigger(conn); break; case 10: dropTable(conn); break; } }while(z != 11); printf("Bye, bye ...\n"); } else printf("connection failed: %s\n", PQerrorMessage(conn)); PQfinish(conn); return EXIT_SUCCESS; }
void doSQL(PGconn *conn, char *command) { PGresult *result; printf("%s\n", command); result = PQexec(conn, command); printf("status is : %s\n", PQresStatus(PQresultStatus(result))); printf("#rows affected: %s\n", PQcmdTuples(result)); printf("result message: %s\n", PQresultErrorMessage(result)); int dalej; while(dalej=0) { switch(PQresultStatus(result)) { case PGRES_TUPLES_OK: { int n = 0, r = 0; int nrows = PQntuples(result); int nfields = PQnfields(result); printf("number of rows returned = %d\n", nrows); printf("number of fields returned = %d\n", nfields); for(r = 0; r < nrows; r++) { for(n = 0; n < nfields; n++) printf(" %s = %s", PQfname(result, n),PQgetvalue(result,r,n)); printf("\n"); } } } PQclear(result); } int main() { PGresult *result; PGconn *conn; char odp, nazwa[20], miasto[20],przydomek[20],data[20],zapytanie[50],wartosc[20]; int id,budzet; const char *connection_str = "host=localhost port=5432 dbname=pzynis user=pzynis password=alamakota"; conn = PQconnectdb(connection_str); if (PQstatus(conn) == CONNECTION_BAD) { fprintf(stderr, "Connection to %s failed, %s", connection_str, PQerrorMessage(conn)); } else { printf("Connected OK\n"); doSQL(conn, "SELECT * FROM number"); // doSQL(conn, "UPDATE number SET name = 'Zaphod' WHERE value = 1"); // doSQL(conn, "DELETE FROM number WHERE value = 2"); doSQL(conn, "SELECT * FROM number"); } int wybor; printf("Podaj opcje : \n 1- stworz tabele \n 2- usun tabele \n 3- dodaj rekordy \n 4-Zawartosc tabeli \n 5-podyfikuj dane \n 6-usun rekord\n 7-stworz wyzwalacz\n 8-usun wyzwalacz\n "); scanf("%i",&wybor); switch(wybor) { case 1: doSQL(conn, "DROP TABLE druzyny_pilkarskie"); doSQL(conn, "CREATE TABLE druzyny_pilkarskie (id INTEGER PRIMARY KEY, nazwa VARCHAR,miasto VARCHAR,przydomek VARCHAR,rok_zal DATE,budzet MONEY);"); break; case 2: doSQL(conn, "DROP TABLE druzyny_pilkarskie"); printf("Usunieto Tabele \n"); break; case 3: printf("\n\n ---- czy wpisujemy co do tabeli? (t/n) "); scanf("%s",&odp); while( (odp == 't') || (odp == 'T') ) { getchar(); printf(" id = "); scanf("%i",&id); printf(" nazwa = "); scanf("%s",nazwa); printf(" miasto = "); scanf("%s",miasto); printf(" przydomek = "); scanf("%s",przydomek); printf(" data (dd-mm-rrrr)= "); scanf("%s",data); printf(" budzet = "); scanf("%i",&budzet); sprintf(zapytanie, "INSERT INTO druzyny_pilkarskie VALUES(%d, \'%s\',\'%s\',\'%s\',\'%s\',%d)", id, nazwa,miasto,przydomek,data,budzet); printf ("zapytanie= %s\n ",zapytanie); doSQL(conn, zapytanie); printf("\n\n ---- czy wpisujemy co do tabeli? (t/n) "); scanf("%s",&odp); } break; case 4: printf("Zawartosc tabeli :"); doSQL(conn, "SELECT* FROM druzyny_pilkarskie"); break; case 5: printf(" Modyfikowanie rekordów: \n\n"); printf(" Podaj id rekodu= "); scanf("%i",&id); sprintf(zapytanie, "SELECT * FROM druzyny_pilkarskie WHERE id=%i", id); doSQL(conn,zapytanie); printf("\n\nCo chcesz zmienic(id,nazwa,miasto,przydomek,data,budzet)? \n"); scanf("%s",nazwa); printf("Podaj wartosc dla %s \n",nazwa); scanf("%s",wartosc); if((strncmp( nazwa, "id", 2 ) == 0 )||(strncmp( nazwa, "budzet", 6 ) == 0 )) sprintf(zapytanie, "UPDATE druzyny_pilkarskie SET %s=%s where id=%i", nazwa,wartosc,id); else sprintf(zapytanie, "UPDATE druzyny_pilkarskie SET %s='%s' where id=%i", nazwa,wartosc,id); doSQL(conn, zapytanie); break; case 6: printf(" Podaj id rekordu do usuniecia: \n\n"); printf(" Podaj id rekodu= "); scanf("%i",&id); sprintf(zapytanie, " DELETE FROM druzyny_pilkarskie WHERE id=%i", id); doSQL(conn, zapytanie); break; case 7: doSQL(conn, "DROP TRIGGER wyzwalacz ON druzyny_pilkarskie "); printf("Wyszukaj dane: \n\n"); printf(" Podaj dane do znalezienia= "); scanf("%s",nazwa); sprintf(zapytanie, " CREATE OR REPLACE FUNCTION funkcja() RETURNS TRIGGER AS $$ BEGIN IF NEW.budzet>=0 THEN NEW.budzet := now(); END IF ;RETURN NEW; END; $$ LANGUAGE 'plpgsql';"); doSQL(conn, zapytanie); sprintf(zapytanie, "CREATE TRIGGER wyzwalacz AFTER INSERT OR UPDATE ON druzyny_pilkarskie FOR EACH ROW EXECUTE PROCEDURE funkcja();"); doSQL(conn, zapytanie); break; case 8: doSQL(conn, "DROP TRIGGER wyzwalacz ON druzyny_pilkarskie "); break; case 9: dalej=1; break; } }
void menu(){ int wyb=0; int end=1; char pom[20]; while(end){ char pomInsert[50]; char pomInsert2[50]; char pomInsert3[50]; char sqlscript[300]="INSERT INTO ksiazka(id_autor,tytul,ksiazka_gatunek,cena,rabat)VALUES ("; char sqlscriptAut[300]="INSERT INTO autor(imie,nazwisko,rok_ur,pochodzenie)VALUES ('"; char sqlscriptUp[300]="UPDATE autor SET "; char sqlscriptUpKs[300]="UPDATE ksiazka SET "; char sqldeletescript[300]=""; printf("Co chcesz zrobic ?\n"); printf("[1] dodaj rekord\n");//jest printf("[2] usun rekord\n");//jest printf("[3] wyswietl wszystko\n");//jest printf("[4] modyfikuj rekord\n");//brak pomyslu printf("[5] szukaj\n");//jest printf("[6] generuj html\n");//jest printf("[7] zakoncz\n"); printf("Wybierasz : "); scanf("%d",&wyb); switch(wyb){ case 1: printf("Wybierz tabele\n"); printf("[1] autor\n"); printf("[2] ksiazka\n"); printf("Wybierasz : "); scanf("%d",&wyb); switch(wyb){ case 1: printf("Podaj imie autora : "); scanf("%s",pomInsert); strcat(sqlscriptAut,pomInsert); strcat(sqlscriptAut,"','"); printf("Podaj nazwisko autora : "); scanf("%s",pomInsert); strcat(sqlscriptAut,pomInsert); strcat(sqlscriptAut,"','"); printf("Podaj rok urodzenia : "); scanf("%s",pomInsert); strcat(sqlscriptAut,pomInsert); strcat(sqlscriptAut,"','"); printf("Podaj pochodzenie : "); scanf("%s",pomInsert); strcat(sqlscriptAut,pomInsert); strcat(sqlscriptAut,"');"); doSQL(conn,sqlscriptAut); continue; case 2: printf("Podaj id autora : "); scanf("%s",pomInsert); strcat(sqlscript,pomInsert); strcat(sqlscript,",'"); printf("Podaj tytul ksiazki uzwyajac _ zamiast spacji : "); scanf("%s",pomInsert); strcat(sqlscript,pomInsert); strcat(sqlscript,"','"); printf("Podaj gatunek : "); scanf("%s",pomInsert); strcat(sqlscript,pomInsert); strcat(sqlscript,"','"); printf("Podaj cene : "); scanf("%s",pomInsert); strcat(sqlscript,pomInsert); strcat(sqlscript,"','"); printf("Podaj rabat : "); scanf("%s",pomInsert); strcat(sqlscript,pomInsert); strcat(sqlscript,"');"); doSQL(conn,sqlscript); continue; } case 2: printf("Wybierz tabele\n"); printf("[1] autor\n"); printf("[2] ksiazka\n"); printf("Wybierasz : "); scanf("%d",&wyb); switch(wyb){ case 1: printf("podaj id autora : "); scanf("%s",pomInsert); deleteAutor(pomInsert); continue; case 2: printf("podaj id ksiazki : "); scanf("%s",pomInsert); deleteBook(pomInsert); continue; } case 3: printf("\n[KSIAZKA]\n\n"); doSQL(conn,"SELECT * FROM ksiazka;"); printf("\n[AUTOR]\n\n"); doSQL(conn,"SELECT * FROM autor;"); continue; case 4: printf("Wybierz tabele\n"); printf("[1] autor\n"); printf("[2] ksiazka\n"); printf("Wybierasz : "); scanf("%d",&wyb); switch(wyb){ case 1: printf("Podaj nazwisko autora ktorego chcesz edytowac : "); scanf("%s",pomInsert2); printf("\nCzy zmieniamy imie? Jesli nie wpisz 0 jesli tak podaj nowe imie : "); scanf("%s",pomInsert); if(strcmp(pomInsert,"0")!=0){ strcat(sqlscriptUp,"imie='"); strcat(sqlscriptUp,pomInsert); strcat(sqlscriptUp,"'"); } strcpy(pomInsert3, pomInsert); printf("\nCzy zmieniamy nazwisko? Jesli nie wpisz 0 jesli tak podaj nowe nazwisko : "); scanf("%s",pomInsert); if(strcmp(pomInsert,"0")!=0){ if(strcmp(pomInsert3,"0")!=0) strcat(sqlscriptUp,","); strcat(sqlscriptUp," nazwisko='"); strcat(sqlscriptUp,pomInsert); strcat(sqlscriptUp,"'"); strcpy(pomInsert3, pomInsert); } printf("\nCzy zmieniamy rok_urodzenia? Jesli nie wpisz 0 jesli tak podaj nowy rok_rodzenia : "); scanf("%s",pomInsert); if(strcmp(pomInsert,"0")!=0){ if(strcmp(pomInsert3,"0")!=0) strcat(sqlscriptUp,","); strcat(sqlscriptUp," rok_ur='"); strcat(sqlscriptUp,pomInsert); strcat(sqlscriptUp,"'"); strcpy(pomInsert3, pomInsert); } printf("\nCzy zmieniamy pochodzenie? Jesli nie wpisz 0 jesli tak podaj nowe pochodzenie : "); scanf("%s",pomInsert); if(strcmp(pomInsert,"0")!=0){ if(strcmp(pomInsert3,"0")!=0) strcat(sqlscriptUp,","); strcat(sqlscriptUp," pochodzenie='"); strcat(sqlscriptUp,pomInsert); strcat(sqlscriptUp,"'"); } strcat(sqlscriptUp," WHERE nazwisko='"); strcat(sqlscriptUp,pomInsert2); strcat(sqlscriptUp,"';"); doSQL(conn,sqlscriptUp); continue; case 2: printf("Podaj tytul ksiazki ktora chcesz edytowac : "); scanf("%s",pomInsert2); printf("\nCzy zmieniamy tytul? Jesli nie wpisz 0 jesli tak podaj nowy tytul : "); scanf("%s",pomInsert); if(strcmp(pomInsert,"0")!=0){ strcat(sqlscriptUpKs,"tytul='"); strcat(sqlscriptUpKs,pomInsert); strcat(sqlscriptUpKs,"'"); } strcpy(pomInsert3, pomInsert); printf("\nCzy zmieniamy gatunek? Jesli nie wpisz 0 jesli tak podaj nowy gatunek : "); scanf("%s",pomInsert); if(strcmp(pomInsert,"0")!=0){ if(strcmp(pomInsert3,"0")!=0) strcat(sqlscriptUpKs,","); strcat(sqlscriptUpKs," ksiazka_gatunek='"); strcat(sqlscriptUpKs,pomInsert); strcat(sqlscriptUpKs,"'"); strcpy(pomInsert3, pomInsert); } printf("\nCzy zmieniamy cene? Jesli nie wpisz 0 jesli tak podaj nowa cene : "); scanf("%s",pomInsert); if(strcmp(pomInsert,"0")!=0){ if(strcmp(pomInsert3,"0")!=0) strcat(sqlscriptUpKs,","); strcat(sqlscriptUpKs," cena='"); strcat(sqlscriptUpKs,pomInsert); strcat(sqlscriptUpKs,"'"); strcpy(pomInsert3, pomInsert); } printf("\nCzy zmieniamy rabat? Jesli nie wpisz 0 jesli tak podaj nowy rabat : "); scanf("%s",pomInsert); if(strcmp(pomInsert,"0")!=0){ if(strcmp(pomInsert3,"0")!=0) strcat(sqlscriptUpKs,","); strcat(sqlscriptUpKs," rabat='"); strcat(sqlscriptUpKs,pomInsert); strcat(sqlscriptUpKs,"'"); } strcat(sqlscriptUpKs," WHERE tytul='"); strcat(sqlscriptUpKs,pomInsert2); strcat(sqlscriptUpKs,"';"); doSQL(conn,sqlscriptUpKs); continue; } continue; case 5: printf("Wybierz tabele\n"); printf("[1] autor\n"); printf("[2] ksiazka\n"); printf("Wybierasz : "); scanf("%d",&wyb); switch(wyb){ case 1: printf("podaj nazwisko autora : "); scanf("%s",pomInsert); searchAutor(pomInsert); continue; case 2: printf("podaj tytul : "); scanf("%s",pomInsert); searchBook(pomInsert); continue; } case 6: generateHTML(); continue; case 7: end=0; printf("\n\t\tKoniec tego dobrego :)\n\n"); break; default: printf("\nBledny wybor sprobuj jeszcze raz.\n"); } } }
//usuniecie tabeli void dropTable(PGconn *conn){ doSQL(conn, "DROP TABLE cars"); }
//update elementu w bazie void update(PGconn *conn){ int id; char zapytanie[200]; char make[20], model[20], yop[10]; double price, capacity; char odp; printf("Set id car to update: "); scanf("%d", &id); searchTMP(conn, id); if(searchT){ sprintf(zapytanie, "SELECT * FROM cars WHERE id_car = %d", id); doSQL(conn, zapytanie); printf("Would you like to change make? Y/N: "); getchar(); scanf("%c", &odp); if(odp == 89 || odp == 121){ printf("Set new make: "); scanf("%s", make); sprintf(zapytanie, "UPDATE cars SET make = \'%s\' WHERE id_car = %d", make, id); doSQL(conn, zapytanie); odp = 0; } printf("Would you like to change model? Y/N: "); getchar(); scanf("%c", &odp); if(odp == 89 || odp == 121){ printf("Set new model: "); scanf("%s", model); sprintf(zapytanie, "UPDATE cars SET model = \'%s\' WHERE id_car = %d", model, id); doSQL(conn, zapytanie); odp = 0; } printf("Would you like to change price? Y/N: "); getchar(); scanf("%c", &odp); if(odp == 89 || odp == 121){ printf("Set new price: "); scanf("%lf", &price); sprintf(zapytanie, "UPDATE cars SET price = %.2lf WHERE id_car = %d", price, id); doSQL(conn, zapytanie); odp = 0; } printf("Would you like to change capacity? Y/N: "); getchar(); scanf("%c", &odp); if(odp == 89 || odp == 121){ printf("Set new capacity: "); scanf("%lf", &capacity); sprintf(zapytanie, "UPDATE cars SET capacity = %.1lf WHERE id_car = %d", capacity, id); doSQL(conn, zapytanie); odp = 0; } printf("Would you like to change year of production? Y/N: "); getchar(); scanf("%c", &odp); if(odp == 89 || odp == 121){ printf("Set new year of production: "); scanf("%s", yop); sprintf(zapytanie, "UPDATE cars SET yop = \'%s\' WHERE id_car = %d", yop, id); doSQL(conn, zapytanie); odp = 0; } } searchT = 1; }
void searchAutor(char dataPar[50]){ char sqlsearchscript[300]="SELECT * FROM autor WHERE nazwisko='"; strcat(sqlsearchscript,dataPar); strcat(sqlsearchscript,"';"); doSQL(conn,sqlsearchscript); }
void delTrigger(PGconn *conn){ doSQL(conn, "DROP TRIGGER car_insert on cars;"); doSQL(conn, "DROP FUNCTION car_wpis();"); }
//wyszukiwanie w bazie void searchTMP(PGconn *conn, int id){ char zapytanie[200]; sprintf(zapytanie, "SELECT * FROM cars WHERE id_car = %d", id); doSQL(conn, zapytanie); }
//strowrzenie triggera w bazie void createTrigger(PGconn *conn){ doSQL(conn, "CREATE FUNCTION car_wpis() RETURNS trigger AS $$ BEGIN IF character_length(NEW.make) < 2 THEN RAISE EXCEPTION 'Wrong make'; END IF; IF character_length(NEW.model) < 2 THEN RAISE EXCEPTION 'Wrong model'; END IF; IF NEW.price < 1000 THEN RAISE EXCEPTION 'Price to small'; END IF; IF NEW.capacity > 5 THEN RAISE EXCEPTION 'Wrong capacity'; END IF; RETURN NEW; END; $$ LANGUAGE plpgsql;"); doSQL(conn, "CREATE TRIGGER car_insert BEFORE INSERT OR UPDATE ON cars FOR EACH ROW EXECUTE PROCEDURE car_wpis();"); }