sql_LV-1.01.zip_Danny Holstein_labview_labview http_labview web_

/* * Written by: Danny Holstein * Web Site: http://performancemicrowave.com * Description: Library to enable Labview to access SQL database */ #include <stdlib.h> #include <stdio.h> #include <string.h> #include <math.h> #include <mysql.h> #ifdef WIN #define HAVE_ODBC #ifdef BCC #define MSEXPORT #define EXPORT _export #else #define MSEXPORT __declspec(dllexport) #define EXPORT #endif char msg_debug[1024]; #else #define MSEXPORT #define EXPORT #endif #ifdef HAVE_ODBC #include <sql.h> #include <sqlext.h> int idx_find(char *idx_field, char *table); void date_manipulate(char* field); HENV henv; HDBC hdbc; HSTMT hstmt; SQLSMALLINT *StringLength; SQLCHAR ColumnName[256]; SQLSMALLINT *NameLength; SQLSMALLINT DataType; SQLULEN *ColumnSize; SQLSMALLINT *DecimalDigits; SQLSMALLINT *Nullable; static char **sql_row, *cache_LV; enum {odbc_mysql=0, odbc_oracle=1, odbc_db2=2, odbc_fb=3}; int odbc_driver=odbc_oracle; #define SQL_FIELD 256 #endif MYSQL mysql; MYSQL *sock; MYSQL_RES *query_results; MYSQL_ROW row; enum {mysql_api=0, odbc=1}; unsigned short int db_type; #define LOCKED 0 #define UNLOCKED 1 #define QUERY_LEN 4096 int connect_lock = UNLOCKED; /* Typedefs */ typedef int int32; typedef char* CStr; typedef char uChar; typedef struct { long cnt; /* number of bytes that follow */ char str[1]; /* cnt bytes */ } Lstr, **LStrHandle; typedef struct { LStrHandle db; LStrHandle host; LStrHandle user; LStrHandle password; unsigned short int db_type; } DB_LOGIN; typedef struct { int32 dimSize; LStrHandle String[1]; } TD2; typedef TD2 **TD2Hdl; typedef struct { int32 dimSizes[2]; LStrHandle String[1]; } TD4; typedef TD4 **TD4Hdl; MSEXPORT long sql_insert(char *return_id, DB_LOGIN *db, char table[], TD4Hdl values, TD2Hdl fields, long ids[], char debug[]); MSEXPORT long sql_insert_b(char *return_id, DB_LOGIN *db, char table[], TD4Hdl values, TD2Hdl fields, long ids[], char debug[]); MSEXPORT long sql_update(DB_LOGIN *db, char table[], TD4Hdl values, TD2Hdl fields, TD2Hdl where, char debug[], long *num_rows); MSEXPORT long sql_delete(DB_LOGIN *db, char table[], TD2Hdl where, char debug[], long *num_rows); MSEXPORT long sql_cmd(DB_LOGIN *db, TD2Hdl sql_cmds, char debug[]); MSEXPORT long sql_tables(DB_LOGIN *db, CStr schema, TD2Hdl values, CStr query); MSEXPORT long sql_cols(DB_LOGIN *db, char table[], TD2Hdl values, CStr query); void StringAdd(TD2Hdl in_array, char *string); void StringRowAdd(TD4Hdl in_array, char *string[], int cols); void StringRowAdd_b(TD4Hdl in_array, char *string[], int cols, int *lengths); int open_db(DB_LOGIN *login); void close_db(); char shell_cmd[1024]; char *error_str; long DSNewHandle(int32); void DSSetHandleSize(TD4Hdl, int32); MSEXPORT long EXPORT sql_select(DB_LOGIN *db, CStr table, CStr condition, TD2Hdl fields, char *distinct, TD4Hdl values, CStr debug) { /* open db and count the SELECT quantity. */ char sql_query[QUERY_LEN]; int i, l, m, num_cols; #ifdef HAVE_ODBC SQLLEN *k, j=0; #else int j=0; #endif num_cols = (**fields).dimSize; #ifdef HAVE_ODBC k = malloc(sizeof(SQLLEN) * num_cols); sql_row = malloc(sizeof(char*) * num_cols); for (i=0; i< num_cols; i++) {sql_row[i] = malloc(SQL_FIELD);} #endif db_type = (*db).db_type; if (open_db(db) == -1) {sprintf(debug,"Error: %s", error_str);return (-1);}; strcpy(sql_query, ""); /* * build SQL "SELECT" and read from DB */ if (!*distinct) {strcat(sql_query, "SELECT ");} else {strcat(sql_query, "SELECT DISTINCT ");} switch (num_cols) { case 0: return (0); case 1: strncat(sql_query, (**(**fields).String[0]).str, (**(**fields).String[0]).cnt); break; default: strncat(sql_query, (**(**fields).String[0]).str, (**(**fields).String[0]).cnt); for (i=1; i<num_cols ; i++) { strcat(sql_query, ", "); strncat(sql_query, (**(**fields).String[i]).str, (**(**fields).String[i]).cnt);} break; } strcat(sql_query, " FROM "); strcat(sql_query, table); if (strlen(condition) > 0) { strcat(sql_query, " WHERE "); strcat(sql_query, condition);} switch (db_type) { case mysql_api: if (mysql_query(&mysql, sql_query) != 0) { sprintf(debug,"%s\nError: %s", sql_query, mysql_error(&mysql)); return (-2);} query_results = mysql_store_result(&mysql); if (query_results == NULL) {MessageBox(0, "query_results NULL", "debug", 0);} while ((row = mysql_fetch_row(query_results))) { StringRowAdd(values, row, num_cols); j++; } mysql_free_result(query_results); (void) close_db(); return ((long) j); #ifdef HAVE_ODBC case odbc: SQLAllocStmt(hdbc, &hstmt); if (SQLExecDirect(hstmt, sql_query, SQL_NTS) != SQL_SUCCESS) { SQLGetDiagRec(SQL_HANDLE_STMT, hstmt, 1, 0, 0, msg_debug, 1024, 0); MessageBox(0, msg_debug, "error", 0); sprintf(debug,"Error: %s", sql_query); return (-2);} for (i=0; i<num_cols ; i++) { SQLBindCol(hstmt, i+1, SQL_CHAR, sql_row[i], SQL_FIELD-1, &k[i]);} while (SQLFetchScroll(hstmt, SQL_FETCH_NEXT, 0) != SQL_NO_DATA) { StringRowAdd(values, sql_row, num_cols); j++;} SQLFreeStmt(hstmt, SQL_CLOSE); (void) close_db(); for (i=0; i< num_cols; i++) {free(sql_row[i]);} free(sql_row); return ((long) j); #endif default: break; } (void) close_db(); return (-2); } MSEXPORT long EXPORT sql_select_b(DB_LOGIN *db, CStr table, CStr condition, TD2Hdl fields, char *distinct, TD4Hdl values, CStr debug) { /* open db and count the SELECT quantity. */ char sql_query[QUERY_LEN]; int i, l, m, num_cols, *lengths; #ifdef HAVE_ODBC SQLLEN *k, j=0; #else int j=0; #endif num_cols = (**fields).dimSize; #ifdef HAVE_ODBC k = malloc(sizeof(SQLLEN) * num_cols); sql_row = malloc(sizeof(char*) * num_cols); #endif db_type = (*db).db_type; if (open_db(db) == -1) {sprintf(debug,"Error: %s", error_str);return (-1);}; strcpy(sql_query, ""); /* * build SQL "SELECT" and read from DB */ if (!*distinct) {strcat(sql_query, "SELECT ");} else {strcat(sql_query, "SELECT DISTINCT ");} switch (num_cols) { case 0: return (0); case 1: strncat(sql_query, (**(**fields).String[0]).str, (**(**fields).String[0]).cnt); break; default: strncat(sql_query, (**(**fields).String[0]).str, (**(**fields).String[0]).cnt); for (i=1; i<num_cols ; i++) { strcat(sql_query, ", "); strncat(sql_query, (**(**fields).String[i]).str, (**(**fields).String[i]).cnt);} break; } strcat(sql_query, " FROM "); strcat(sql_query, table); if (strlen(condition) > 0) { strcat(sql_query, " WHERE "); strcat(sql_query, condition);} switch (db_type) { case mysql_api: if (mysql_real_query(&mysql, sql_query, strlen(sql_query)) != 0) { sprintf(debug,"%s\nError: %s", sql_query, mysql_error(&mysql)); return (-2);} query_results = mysql_store_result(&mysql); if (query_results == NULL) {MessageBox(0, "query_results NULL", "debug", 0);} while ((row = mysql_fetch_row(query_results))) { lengths = mysql_fetch_lengths(query_results); StringRowAdd_b(values, row, num_cols, lengths); j++; } mysql_free_result(query_results); (void) close_db(); return ((long) j); #ifdef HAVE_ODBC case odbc: SQLAllocStmt(hdbc, &hstmt); lengths = malloc(sizeof(int) * num_cols); if (SQLExecDirect(hstmt, sql_query, SQL_NTS) != SQL_SUCCESS) { SQLGetDiagRec(SQL_HANDLE_STMT, hstmt, 1, 0, 0, msg_debug, 1024, 0); MessageBox(0, msg_debug, "error", 0); sprintf(debug,"Error: %s", sql_query); return (-2);} while (SQLFetchScroll(hstmt, SQL_FETCH_NEXT, 0) != S