void sqlConnect(SQLHENV *henv, SQLHDBC *hdbc, SQLHSTMT *hstmt) { // sql vars SQLRETURN rc; // allocate environment handle rc = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, henv); if (!checkReturnCode("SQLAllocHandle [henv]", rc, *henv, SQL_HANDLE_ENV)){ freeSQLHandles(henv, hdbc, SQL_NULL_HSTMT); exit(0); } rc = SQLSetEnvAttr(*henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER*)SQL_OV_ODBC3, 0); if (!checkReturnCode("SQLSetEnvAttr [henv]", rc, henv, SQL_HANDLE_ENV)){ freeSQLHandles(henv, hdbc, SQL_NULL_HSTMT); exit(0); } // allocate connection handle rc = SQLAllocHandle(SQL_HANDLE_DBC, *henv, hdbc); if (!checkReturnCode("SQLAllocHandle [hdbc]", rc, *hdbc, SQL_HANDLE_DBC)){ freeSQLHandles(henv, hdbc, SQL_NULL_HSTMT); exit(0); } // set login timeout to 5 seconds rc = SQLSetConnectAttr(*hdbc, SQL_LOGIN_TIMEOUT, (SQLPOINTER)5, 0); if (!checkReturnCode("SQLSetConnectAttr [hdbc]", rc, *hdbc, SQL_HANDLE_DBC)){ freeSQLHandles(henv, hdbc, SQL_NULL_HSTMT); exit(0); } // linux // connect to data source char* server = "MSSQLSERVER"; char* login = "******"; char* password = "******"; rc = SQLConnect(*hdbc, (SQLCHAR*) server, SQL_NTS, (SQLCHAR*) login, SQL_NTS, (SQLCHAR*) password, SQL_NTS); if (!checkReturnCode("SQLConnect [hdbc]", rc, *hdbc, SQL_HANDLE_DBC)) { freeSQLHandles(henv, hdbc, hstmt); exit(0); } // windows // sql driver connect /*SQLCHAR retconstring[1024]; char* connStr = "DRIVER={SQL Server};SERVER=141.56.2.45, 1433;DATABASE=Verkauf;UID=stud01;PWD=stud01;"; rc = SQLDriverConnect(*hdbc, NULL, (SQLCHAR*)connStr, SQL_NTS, retconstring, 1024, NULL, SQL_DRIVER_NOPROMPT); if (!checkReturnCode("SQLDriverConnect [hdbc]", rc, *hdbc, SQL_HANDLE_DBC)){ freeSQLHandles(henv, hdbc, SQL_NULL_HSTMT); exit(0); }*/ // allocate statement handle rc = SQLAllocHandle(SQL_HANDLE_STMT, *hdbc, hstmt); if (!checkReturnCode("SQLAllocHandle [hstmt]", rc, *hstmt, SQL_HANDLE_STMT)) { freeSQLHandles(henv, hdbc, hstmt); exit(0); } }
void freeSQLHandles(SQLHENV *henv, SQLHDBC *hdbc, SQLHSTMT *hstmt) { SQLRETURN rc; // free handles // statement if (hstmt != SQL_NULL_HSTMT) { rc = SQLFreeHandle(SQL_HANDLE_STMT, *hstmt); checkReturnCode("SQLFreeHandle [hstmt]", rc, *hstmt, SQL_HANDLE_STMT); } // connection if (hdbc != SQL_NULL_HDBC) { rc = SQLDisconnect(*hdbc); checkReturnCode("SQLDisconnect [hdbc]", rc, *hdbc, SQL_HANDLE_DBC); rc = SQLFreeHandle(SQL_HANDLE_DBC, *hdbc); checkReturnCode("SQLFreeHandle [hdbc]", rc, *hdbc, SQL_HANDLE_DBC); } // environment if (henv != SQL_NULL_HENV) { rc = SQLFreeHandle(SQL_HANDLE_ENV, *henv); checkReturnCode("SQLFreeHandle [henv]", rc, *henv, SQL_HANDLE_ENV); } }
int getBerufe(SQLHENV *henv, SQLHDBC *hdbc, SQLHSTMT *hstmt, char *beruf) { // sql vars SQLCHAR kBeruf[BERUFSIZE + 1]; SQLLEN bindLen; SQLRETURN rc; // input buffer char vBuf[VBUFSIZE]; // SQL statement char *sqlStatement = "SELECT DISTINCT(Beruf) FROM Mitarbeiter"; if (DEBUGINFO) printf("sqlStatement: \"%s\"\n", sqlStatement); // perform the SQL statement rc = SQLExecDirect(*hstmt, (SQLCHAR *)sqlStatement, SQL_NTS); if (!checkReturnCode("SQLExecDirect [hstmt, sqlStatement]", rc, *hstmt, SQL_HANDLE_STMT)) { freeSQLHandles(henv, hdbc, hstmt); exit(0); } // bind columns rc = SQLBindCol(*hstmt, 1, SQL_C_CHAR, kBeruf, BERUFSIZE + 1, &bindLen); checkReturnCode("SQLBindCol [hstmt, 1, kOrt]", rc, *hstmt, SQL_HANDLE_STMT); puts(""); // fetch and print each row of data int i = 0; while ((rc = SQLFetch(*hstmt)) != SQL_NO_DATA) { // check error and warning if (rc == SQL_ERROR) { printf("SQLFetch [hstmt]: SQL_ERROR\n"); break; } if (rc == SQL_SUCCESS_WITH_INFO) { printf("SQLFetch [hstmt]: SQL_SUCCESS_WITH_INFO\n"); } // process data printf(" %-*s\n", BERUFSIZE, kBeruf); i++; } // cancel statement handle rc = SQLCancel(*hstmt); if (!checkReturnCode("SQLCancel [hstmt]", rc, *hstmt, SQL_HANDLE_STMT)) { freeSQLHandles(henv, hdbc, hstmt); exit(0); } if (i == 0) { printf(" Keine Berufe vorhanden.\n"); } // get input printf("\n Beruf ? "); fgets(vBuf, VBUFSIZE, stdin); vBuf[strlen(vBuf) - 1] = 0; strncpy_s(beruf, BERUFSIZE, vBuf, VBUFSIZE); return strlen(beruf); }
int getProjektDetails(SQLHENV *henv, SQLHDBC *hdbc, SQLHSTMT *hstmt, char *beruf, char *mitID) { // sql vars SQLINTEGER proNr; SQLCHAR proName[PRONAMESIZE + 1]; SQLDOUBLE istAnt; SQLDOUBLE planAnt; SQLCHAR istAnt2[4]; SQLCHAR planAnt2[4]; SQLLEN bindLen; SQLRETURN rc = 0; // input buffer char vBuf[VBUFSIZE]; // SQL statement char *part1 = "SELECT p.ProNr, p.ProName, z.Istanteil, z.Plananteil FROM Zuordnung z JOIN Projekt p ON p.ProNr = z.ProNr WHERE z.MitID ='"; char *part2 = "'"; int size = strlen(part1) + strlen(mitID) + strlen(part1) + 1; char *sqlStatement = malloc(sizeof(char)* size); sprintf_s(sqlStatement, sizeof(char) * size, "%s%s%s", part1, mitID, part2); if (DEBUGINFO) printf("sqlStatement: \"%s\"\n", sqlStatement); // perform the SQL statement rc = SQLExecDirect(*hstmt, (SQLCHAR *)sqlStatement, SQL_NTS); if (!checkReturnCode("SQLExecDirect [hstmt, sqlStatement2]", rc, *hstmt, SQL_HANDLE_STMT)) { freeSQLHandles(henv, hdbc, hstmt); exit(0); } // bind columns rc = SQLBindCol(*hstmt, 1, SQL_C_LONG, &proNr, 0, &bindLen); checkReturnCode("SQLBindCol [hstmt, 1, proNr]", rc, *hstmt, SQL_HANDLE_STMT); rc = SQLBindCol(*hstmt, 2, SQL_C_CHAR, proName, PRONAMESIZE + 1, &bindLen); checkReturnCode("SQLBindCol [hstmt, 2, proName]", rc, *hstmt, SQL_HANDLE_STMT); rc = SQLBindCol(*hstmt, 3, SQL_C_DOUBLE, &istAnt, 1024, &bindLen); checkReturnCode("SQLBindCol [hstmt, 3, &istAnt]", rc, *hstmt, SQL_HANDLE_STMT); rc = SQLBindCol(*hstmt, 4, SQL_C_DOUBLE, &planAnt, 1024, &bindLen); checkReturnCode("SQLBindCol [hstmt, 4, &planAnt]", rc, *hstmt, SQL_HANDLE_STMT); //rc = SQLBindCol(*hstmt, 3, SQL_C_FLOAT, &istAnt, 0, &bindLen); //checkReturnCode("SQLBindCol [hstmt, 3, &istAnt]", rc, *hstmt, SQL_HANDLE_STMT); //rc = SQLBindCol(*hstmt, 4, SQL_C_FLOAT, &planAnt, 0, &bindLen); //checkReturnCode("SQLBindCol [hstmt, 4, &planAnt]", rc, *hstmt, SQL_HANDLE_STMT); // fetch and print each row of data int i = 0; while ((rc = SQLFetch(*hstmt)) != SQL_NO_DATA) { // check error and warning if (rc == SQL_ERROR) { printf("SQLFetch [hstmt]: SQL_ERROR\n"); break; } if (rc == SQL_SUCCESS_WITH_INFO) { //printf("SQLFetch [hstmt]: SQL_SUCCESS_WITH_INFO\n"); } // process data printf(" %ld %-*s %.2f %.2f \n", proNr, PRONAMESIZE, proName, istAnt, planAnt); i++; } // cancel statement handle rc = SQLCancel(*hstmt); if (!checkReturnCode("SQLCancel [hstmt]", rc, *hstmt, SQL_HANDLE_STMT)) { freeSQLHandles(henv, hdbc, hstmt); exit(0); } if (i == 0) { printf(" '%s' hat keine Projekte.\n", mitID); printf("\n ? "); fgets(vBuf, sizeof(vBuf), stdin); printf("----------------------------------------------------------------------------\n"); return 0; } // wait printf("\n ? "); fgets(vBuf, sizeof(vBuf), stdin); printf("----------------------------------------------------------------------------\n"); return 1; }
int getMitDetails(SQLHENV *henv, SQLHDBC *hdbc, SQLHSTMT *hstmt, char *beruf, char *mitID) { // sql vars SQLCHAR mitNName[VNAMESIZE + 1]; SQLCHAR mitVName[NNAMESIZE + 1]; SQLCHAR ort[ORTSIZE + 1]; SQLCHAR gebDat[GEBDATSIZE + 1]; SQLCHAR telNr[TELNRSIZE + 1]; SQLLEN bindLen; SQLRETURN rc = 0; // input buffer char vBuf[VBUFSIZE]; // SQL statement char *part1 = "SELECT Nachname, Vorname, Ort, Beruf, Gebdat, Telnr FROM Mitarbeiter WHERE Beruf = '"; char *part2 = "' AND MitID='"; char *part3 = "'"; int size = strlen(part1) + strlen(beruf) + strlen(part2) + strlen(mitID) + strlen(part3) + 1; char *sqlStatement = malloc(sizeof(char)* size); sprintf_s(sqlStatement, sizeof(char) * size, "%s%s%s%s%s", part1, beruf, part2, mitID, part3); if (DEBUGINFO) printf("sqlStatement: \"%s\"\n", sqlStatement); // perform the SQL statement rc = SQLExecDirect(*hstmt, (SQLCHAR *)sqlStatement, SQL_NTS); if (!checkReturnCode("SQLExecDirect [hstmt, sqlStatement]", rc, *hstmt, SQL_HANDLE_STMT)) { freeSQLHandles(henv, hdbc, hstmt); exit(0); } // bind columns rc = SQLBindCol(*hstmt, 1, SQL_C_CHAR, mitNName, NNAMESIZE + 1, &bindLen); checkReturnCode("SQLBindCol [hstmt, 1, mitNName]", rc, *hstmt, SQL_HANDLE_STMT); rc = SQLBindCol(*hstmt, 2, SQL_C_CHAR, mitVName, VNAMESIZE + 1, &bindLen); checkReturnCode("SQLBindCol [hstmt, 2, mitVName]", rc, *hstmt, SQL_HANDLE_STMT); rc = SQLBindCol(*hstmt, 3, SQL_C_CHAR, ort, ORTSIZE + 1, &bindLen); checkReturnCode("SQLBindCol [hstmt, 3, ort]", rc, *hstmt, SQL_HANDLE_STMT); rc = SQLBindCol(*hstmt, 4, SQL_C_CHAR, beruf, BERUFSIZE + 1, &bindLen); checkReturnCode("SQLBindCol [hstmt, 4, beruf]", rc, *hstmt, SQL_HANDLE_STMT); rc = SQLBindCol(*hstmt, 5, SQL_C_CHAR, gebDat, GEBDATSIZE + 1, &bindLen); checkReturnCode("SQLBindCol [hstmt, 5, gebDat]", rc, *hstmt, SQL_HANDLE_STMT); rc = SQLBindCol(*hstmt, 6, SQL_C_CHAR, telNr, TELNRSIZE + 1, &bindLen); checkReturnCode("SQLBindCol [hstmt, 6, telNr]", rc, *hstmt, SQL_HANDLE_STMT); puts(""); // fetch and print each row of data int i = 0; while ((rc = SQLFetch(*hstmt)) != SQL_NO_DATA) { // check error and warning if (rc == SQL_ERROR) { printf("SQLFetch [hstmt]: SQL_ERROR\n"); break; } if (rc == SQL_SUCCESS_WITH_INFO) { printf("SQLFetch [hstmt]: SQL_SUCCESS_WITH_INFO\n"); } // process data printf(" %-*s %-*s %-*s %-*s %-*s %-*s\n", NNAMESIZE, mitNName, VNAMESIZE, mitVName, ORTSIZE, ort, BERUFSIZE, beruf, GEBDATSIZE, gebDat, TELNRSIZE, telNr); i++; } // cancel statement handle rc = SQLCancel(*hstmt); if (!checkReturnCode("SQLCancel [hstmt]", rc, *hstmt, SQL_HANDLE_STMT)) { freeSQLHandles(henv, hdbc, hstmt); exit(0); } if (i == 0) { printf(" '%s' ist in dem Kontext keine gueltige Mitarbeiter-ID.\n", mitID); printf("\n ? "); fgets(vBuf, sizeof(vBuf), stdin); printf("----------------------------------------------------------------------------\n"); return 0; } return 1; }
int getMitarbeiter(SQLHENV *henv, SQLHDBC *hdbc, SQLHSTMT *hstmt, char *beruf, char *mitID) { // sql vars SQLCHAR mitVName[VNAMESIZE + 1]; SQLCHAR mitNName[NNAMESIZE + 1]; SQLLEN bindLen; SQLRETURN rc = 0; // input buffer char vBuf[VBUFSIZE]; // SQL statement char *part1 = "SELECT MitID, Nachname, Vorname FROM Mitarbeiter WHERE Beruf = '"; char *part2 = "'"; int size = strlen(part1) + strlen(beruf) + strlen(part2) + 1; char *sqlStatement = malloc(sizeof(char)* size); sprintf_s(sqlStatement, sizeof(char) * size, "%s%s%s", part1, beruf, part2); if (DEBUGINFO) printf("sqlStatement: \"%s\"\n", sqlStatement); // perform the SQL statement rc = SQLExecDirect(*hstmt, (SQLCHAR *)sqlStatement, SQL_NTS); if (!checkReturnCode("SQLExecDirect [hstmt, sqlStatement]", rc, *hstmt, SQL_HANDLE_STMT)) { freeSQLHandles(henv, hdbc, hstmt); exit(0); } // bind columns rc = SQLBindCol(*hstmt, 1, SQL_C_CHAR, mitID, MITIDSIZE + 1, &bindLen); checkReturnCode("SQLBindCol [hstmt, 1, mitID]", rc, *hstmt, SQL_HANDLE_STMT); rc = SQLBindCol(*hstmt, 2, SQL_C_CHAR, mitNName, NNAMESIZE + 1, &bindLen); checkReturnCode("SQLBindCol [hstmt, 2, mitNName]", rc, *hstmt, SQL_HANDLE_STMT); rc = SQLBindCol(*hstmt, 3, SQL_C_CHAR, mitVName, VNAMESIZE + 1, &bindLen); checkReturnCode("SQLBindCol [hstmt, 3, mitVName]", rc, *hstmt, SQL_HANDLE_STMT); puts(""); // fetch and print each row of data int i = 0; while ((rc = SQLFetch(*hstmt)) != SQL_NO_DATA) { // check error and warning if (rc == SQL_ERROR) { printf("SQLFetch [hstmt]: SQL_ERROR\n"); break; } if (rc == SQL_SUCCESS_WITH_INFO) { printf("SQLFetch [hstmt]: SQL_SUCCESS_WITH_INFO\n"); } // process data printf(" %-*s %-*s %-*s\n", MITIDSIZE, mitID, NNAMESIZE, mitNName, VNAMESIZE, mitVName); i++; } // cancel statement handle rc = SQLCancel(*hstmt); if (!checkReturnCode("SQLCancel [hstmt]", rc, *hstmt, SQL_HANDLE_STMT)) { freeSQLHandles(henv, hdbc, hstmt); exit(0); } if (i == 0) { printf(" Beruf '%s' ist nicht vorhanden.\n", beruf); printf("\n ? "); fgets(vBuf, sizeof(vBuf), stdin); printf("----------------------------------------------------------------------------\n"); return 0; } // wait printf("\n Mitarbeiter-ID ? "); fgets(vBuf, VBUFSIZE, stdin); vBuf[strlen(vBuf) - 1] = 0; //strncpy_s(mitID, MITIDSIZE + 1 , vBuf, VBUFSIZE); strncpy_s(mitID, MITIDSIZE + 1, vBuf, MITIDSIZE); return 1; }
void sqlDisconnect(SQLHENV *henv, SQLHDBC *hdbc, SQLHSTMT *hstmt) { SQLRETURN rc = SQLCancel(*hstmt); checkReturnCode("SQLCancel [hstmt]", rc, hstmt, SQL_HANDLE_STMT); freeSQLHandles(henv, hdbc, SQL_NULL_HSTMT); }