Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- #include <stdlib.h>
- #include <libpq-fe.h>
- #include <unistd.h>
- #include <string.h>
- #include <stdio.h>
- #define LOW 25
- #define HIGH 225
- 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 aff : %s\n", PQcmdTuples(result));
- printf("result msg : %s\n", PQresultErrorMessage(result));
- switch(PQresultStatus(result)){
- case PGRES_TUPLES_OK:
- {
- int n=0, m=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(n=0; n < nfields; n++){
- printf("%7s ", PQfname(result, n));
- }
- printf("\n");
- for(m=0; m < nrows; m++){
- for(n=0; n < nfields; n++){
- printf("%7s ", PQgetvalue(result,m,n));
- }
- printf("\n");
- }
- }
- }
- PQclear(result);
- }
- void HTMLprint(PGconn * conn, char *tabela, char *out)
- {
- PGresult *result;
- char stmt[150];
- sprintf(stmt,"SELECT * FROM %s",tabela);
- result = PQexec(conn, stmt);
- switch(PQresultStatus(result))
- {
- case PGRES_TUPLES_OK:
- {
- int n = 0, m = 0;
- int nrows = PQntuples(result); //wiersze
- int nfields = PQnfields(result); //kolumny
- FILE * druk = fopen(out, "w+"); //plik
- fprintf(druk,"<!DOCTYPE html> \n <html lang=\"pl\"> \n <head> \n <meta charset=\"utf-8\"> \n <meta name=\"viewport\" content=\"width=device-width, initial-scale=1.0\"> \n <title>Wynik</title> \n <style> \n"); //header
- //tutaj style:
- fprintf(druk,"table,td,th{ border: 5px solid red; background:pink; border-radius: 5px;} \n table {border-collapse: collapse: width: 100} \n th{ text-align: left; font-family: Comic Sans MS} \n body{background-color: teal} </style></head>\n");
- fprintf(druk,"<body> \n <table> \n");
- fprintf(druk,"<tr>\n");
- for(n=0;n<nfields;n++)
- fprintf(druk,"<th>%s</th>\n",PQfname(result, n));
- fprintf(druk,"</tr>\n");
- for(m = 0; m < nrows; m++)
- {
- fprintf(druk,"<tr>\n");
- for(n = 0; n < nfields; n++)
- fprintf(druk,"<th>%s</th>\n",PQgetvalue(result,m,n));
- fprintf(druk,"</tr>\n");
- }
- fprintf(druk,"</table> \n </body> \n </html>");
- fclose(druk);
- }
- }
- printf("\n");
- PQclear(result);
- }
- int main(int argc, char *argv[]){
- int i=0;
- int rows=0, columns=0;
- char file[LOW], fileName[LOW], fileExt[LOW]=".csv";
- char *table[LOW];
- char line[LOW], sqlCommand[HIGH], columnNames[HIGH][HIGH];
- FILE *fileContents;
- printf("Podaj nazwe pliku .csv:\n");
- scanf("%s", &file);
- strcpy(fileName,file);
- printf("%s",file);
- PGresult *result;
- PGconn *conn = PQconnectdb("host=localhost port=5432 dbname=nazwa user=pstankiewicz password=mokrawydra");
- if(PQstatus(conn) == CONNECTION_OK){
- printf("connection made\n");
- strcat(file,".csv");
- if((fileContents = fopen(file, "r")) == NULL){
- printf("Nie znaleziono pliku");
- return EXIT_SUCCESS;
- }
- fscanf(fileContents,"%s",line);
- // FILE *fileContents = fopen("koledzy.csv", "r");
- table[i] = strtok(line,";");
- while(table[i]!=NULL)
- {
- table[++i] = strtok(NULL,";");
- columns++;
- }
- for(i=0;i<columns;i++)
- strcpy(columnNames[i],table[i]);
- for(i=1;table[i]!=NULL;i++){
- printf("\n>>>>>>>>>>>>>>%s\n",table[i]);
- // sprintf(sqlCommand,"ALTER TABLE %s ADD %s VARCHAR(20)", fileName, table[i]);
- // doSQL(conn,sqlCommand);
- }
- sprintf(sqlCommand,"DROP TABLE %s", fileName);
- doSQL(conn,sqlCommand);
- sprintf(sqlCommand,"CREATE TABLE %s(%s INTEGER UNIQUE)", fileName, table[0]);
- doSQL(conn,sqlCommand);
- for(i=1;table[i]!=NULL;i++){
- printf("\n>>>>>>>>>>>>>>%s\n",table[i]);
- // sprintf(sqlCommand,"ALTER TABLE %s ADD %s VARCHAR(20)", fileName, table[i]);
- // doSQL(conn,sqlCommand);
- }
- for(i=1;table[i]!=NULL;i++){
- printf("\n\n>>>>>>>>>>>>>>%s\n\n",table[i]);
- sprintf(sqlCommand,"ALTER TABLE %s ADD %s VARCHAR(20)", fileName, table[i]);
- doSQL(conn,sqlCommand);
- }
- while(fscanf(fileContents,"%s",line) == 1){
- i=0;
- table[i] = strtok(line,";");
- for(;i<columns;)
- table[++i] = strtok(NULL,";");
- for(i=0;i<columns;i++) // extend column size if needed
- {
- if(strlen(table[i])>20)
- {
- sprintf(sqlCommand,"ALTER TABLE %s ALTER COLUMN %s TYPE VARCHAR(%d)", fileName, columnNames[i], (int)strlen(table[i]));
- doSQL(conn, sqlCommand);
- }
- }
- sprintf(sqlCommand,"INSERT INTO %s VALUES(%s, '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')", fileName, table[0], table[1], table[2], table[3], table[4], table[5], table[6], table[7], table[8]);
- doSQL(conn, sqlCommand);
- }
- HTMLprint(conn, argv[1], argv[2]);
- } else printf("connection failed: %s\n", PQerrorMessage(conn));
- PQfinish(conn);
- return EXIT_SUCCESS;
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
