/*
* 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
