Source code 4d-extract

string createSelect(string table)
{
    std::string str("");
    int array_size = 8192;
    char * array = new char[array_size];
    int position = 0;
    ifstream fin("tables\\"+table+".tables");
    if(fin.is_open())
    {
        str = "SELECT ";
        while(!fin.eof() && position < array_size)
        {
            fin.get(array[position]); //reading one character from file to array
            position++;
        }
        array[position-1] = '\0'; //placing character array terminating character
        //this loop display all the charaters in array till \0
        for(int i = 0; array[i] != '\0'; i++)
        {
            str += array[i];
        }
        str += " FROM " + table;
    }
    else
    {
        str = "";
    }
    return str;
}

string createInsert(string table)
{
    std::string str("");
    int array_size = 8192;
    char * array = new char[array_size];
    int position = 0;
    ifstream fin("tables\\"+table+".tables");

    if(fin.is_open())
    {
        str += "INSERT INTO "+table+" (";
        while(!fin.eof() && position < array_size)
        {
            fin.get(array[position]);
            position++;
        }
        array[position-1] = '\0';
        for(int i = 0; array[i] != '\0'; i++)
        {
            str += array[i];
        }
        str += ") VALUES (";
    }
    else
    {
        str = "";
    }
    return str;
}

bool createSQL(string table)
{
    ofstream newFile;
    ofstream newFile2;
    newFile.open ("imports\\VUL_"+table+".sql");
    newFile2.open ("output.txt");
    std::string select("");
    std::string insert("");

  SQLHANDLE hEnv ;
  SQLRETURN retCode ;
  SQLRETURN retCode2 ;

  retCode = SQLAllocHandle( SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hEnv ) ;

  CHECK( retCode, "allocate environment handle" ) ;

  retCode = SQLSetEnvAttr( hEnv, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0 ) ;

  CHECK( retCode, "setting the environment attribute setting to ODBC version 3" ) ;

  SQLHANDLE hConn ;

  CHECK( SQLAllocHandle( SQL_HANDLE_DBC, hEnv, &hConn ), "allocate handle" ) ;





  SQLCHAR* dsnName = (SQLCHAR*)"REMOVED" ;
  SQLCHAR* userid = (SQLCHAR*)"REMOVED";
  SQLCHAR* password = (SQLCHAR*)"REMOVED";


  retCode = SQLConnectA(

    hConn,

    dsnName,  // name of data source we are connecting to,

    SQL_NTS,  // the DSN name is a NULL TERMINATED STRING, so "count it yourself"

    userid,
    SQL_NTS,  // userid is a null-terminated string

    password,
    SQL_NTS   // password is a null terminated string

  ) ;
  if( !CHECK( retCode, "SqlConnectA", false ) )
  {
    // if this fails, I want that extra status
    // information about WHY the failure happened.
    // status function is defined above.

    status( SQL_HANDLE_DBC, hConn, __LINE__ ) ;
  }


  // 6.  Create and allocate a statement
  SQLHANDLE hStmt ;
  CHECK( SQLAllocHandle( SQL_HANDLE_STMT, hConn, &hStmt ), "allocate handle for statement" ) ;


  // 7.  Form a query to run and attach it to the hStmt
  // this basically connects the hStmt up with
  // some results.

  select = createSelect(table);
  const char *cstr = select.c_str();
  newFile2 << cstr;
  SQLCHAR* query = (SQLCHAR*)cstr;

  CHECK( SQLExecDirectA( hStmt, query, SQL_NTS ), "execute query" ) ;


  // 8.  Read data results that are now in the hStmt.
  retCode = SQLFetch( hStmt ) ;

  CHECK( retCode, "first sqlFetch" ) ;

  // How many rows got returned?
  SQLLEN numRows ;
  retCode = SQLRowCount( hStmt, &numRows ) ;
  printf( "%d regels zijn verwerkt.\n", numRows ) ;

  // With a query like the one we wrote (SELECT *),
  // we don't know how many columsn should be in
  // the result set at this point.
  // So we ask ODBC to tell us!
  SQLSMALLINT numCols ;
  retCode = SQLNumResultCols( hStmt, &numCols ); // SqlNumResultCols

  // Now print the column names.
  // SQLDescribeCol function
  SQLCHAR colName[ 256 ] ;

  SQLSMALLINT colNameLen, dataType, numDecimalDigits, allowsNullValues ;
  SQLUINTEGER columnSize ;

  for( int i = 1 ; i <= numCols ; i++ )
  {
    retCode = SQLDescribeColA( hStmt, i, colName, 255, &colNameLen, &dataType, &columnSize, &numDecimalDigits, &allowsNullValues ) ;
    if( CHECK( retCode, "SQLDescribeCol" ) )
    {
      printf( "Column #%d: '%s', datatype=%d size=%d decimaldigits=%d nullable=%d\n",
                       i,colName,   dataType, columnSize,  numDecimalDigits, allowsNullValues ) ;
    }
  }
  newFile << "TRUNCATE TABLE "+table+";";
  for( int i = 1 ; i <= numRows ; i++ )
  {
    // Datatypes
    // SQLGetData

    char buf[256];
    SQLINTEGER numBytes ;
    newFile << createInsert(table);
    for( int j = 1 ;
      j <= numCols ;
      j++ )
    {

      retCode = SQLGetData(

        hStmt,
        j,           // COLUMN NUMBER of the data to get
        SQL_C_CHAR,  // the data type that you expect to receive
        buf,         // the place to put the data that you expect to receive
        255,         // the size in bytes of buf (-1 for null terminator)
        &numBytes    // size in bytes of data returned

      ) ;

        if( CHECK( retCode, "SqlGetData", false ) )
        {
            retCode2 = SQLDescribeColA( hStmt, j, colName, 255, &colNameLen, &dataType, &columnSize, &numDecimalDigits, &allowsNullValues ) ;
            if( CHECK( retCode2, "SQLDescribeCol" ) )
            {
                //cout << dataType << endl;
                if(dataType != 91) {
                    newFile << "'" << removeSlashes(removeSpecials(buf)) << "'";
                }
                else if (dataType == 91) {
                    newFile << "date_format(str_to_date('" << fixDate(buf) << "', '%d-%m-%Y'),'%Y-%m-%d')";
                }
            }
        //Sleep(50);
        }
        if(j != numCols) {
            newFile << ",";
        }

    }
    newFile << ");\n";
    cout << "Regel #" << i <<  " van tabel " << table << " is verwerkt." << endl;

    retCode = SQLFetch( hStmt ) ;
    if( !SQL_SUCCEEDED( retCode ) )
    {
      cout << "Tabel "+table+" is verwerkt." << endl;
      printf( "Regel %d is de laatste regel.\n", i ) ;
    }
  }
  newFile.close();
  SQLFreeHandle( SQL_HANDLE_STMT, hStmt ) ;
  SQLFreeHandle( SQL_HANDLE_DBC, hConn ) ;
  SQLFreeHandle( SQL_HANDLE_ENV, hEnv ) ;
  return true;
}