Fetch all recordset from ODBC

samsam598 · Post by **samsam598** » Wed Dec 05, 2012 3:09 am

Greetings!

I am writing an ODBC wrapper class to read and write with MS Access Database file.Below function is to retrieve all record set from query.However it turned out it does not work properly.I think the main problem is I did something wrong with Array<String*> and dynamic array of array of char(*).Could you please figure me out the proper way to do so?Thanks.

Code: Select all

 
String**  fetchAll(const char* pszSql)
   {
      int i;
      array=Array<String*>{};
      if(!pszSql)
         return null;
      retcode=SQLExecDirectUTF8((SQLCHAR*)pszSql,SQL_NTS);
      if((retcode != SQL_SUCCESS) || (retcode != SQL_SUCCESS_WITH_INFO))
      {
         //dbError(format(retcode));
              return null;
      }
 
      retcode=SQLNumResultCols(hstmt,&col);
      if((retcode != SQL_SUCCESS) || (retcode != SQL_SUCCESS_WITH_INFO))
      {
         //dbError(format(retcode));
                 return null;
      }
      row=0;
 
    colLen = 0;
    buf_len = 0;
    colType = 0;
 
    while(true)
    {  
       char sz_buf[256];
       char* pszBuf;
       SQLINTEGER  buflen;
       char** rowData=new char*[col+1];  
       if(SQLFetch(hstmt)==SQL_NO_DATA)
       {
           break;
       }
       for(i=1;i<=col;i++)
       {
           SQLColAttribute(hstmt, i, SQL_DESC_NAME, sz_buf, 256, &buf_len, 0);
           SQLColAttribute(hstmt, i, SQL_DESC_TYPE, 0, 0, 0, &colType);
           SQLColAttribute(hstmt, i, SQL_DESC_LENGTH, NULL, 0, 0, &colLen);
           pszBuf=new char[colLen+1];
           pszBuf[0]='\0';
           SQLGetData(hstmt,i,SQL_C_WCHAR,pszBuf,50,&buflen);
           rowData[i-1]=CopyString(pszBuf);
        }
       array.Add(rowData);   
       row++;
   }
 
    SQLCancel(hstmt);
 
 
	SQLFreeStmt(hstmt, SQL_DROP);
	SQLDisconnect(hdbc);
	SQLFreeConnect(hdbc);
	SQLFreeEnv(henv);        
    return array;  
 }

Post by **jerome** » Wed Dec 05, 2012 3:32 am

Hi Sam!

You know it would be great if you could write an ODBC driver for EDA, using our existing drivers as examples...

Juan Sanchez started the work on the Oracle driver earlier this year (sdk/eda/drivers).

This would really be the proper way so that we can easily import for an ODBC database into EDA and vice-versa

Also you could use the eC active records and DB schema syntax.

Do you have access to IRC (#ecere on irc.freenode.net) ? This code seems a bit complex and I don't have a way to test so it would be easier to help you out there.

-Jerome

samsam598 · Post by **samsam598** » Wed Dec 05, 2012 4:41 am

Hi Jerome,

Thanks for the reply.I hope I can implemented in an eC way using the EDA technology but I am sorry I can't fully understand it at this moment.At the very first step I just tried to write a simple but workable wrapper to test the basic coding needed,like dynamic array etc,and trying to make it recognize Chinese character record properly.Next step I will study and try to re-implement with EDA technology.

Attached please find the GUI version of the test case.It can run but it will crash when tring to retreive the recordset.

Regards,
Sam

Post by **jerome** » Wed Dec 05, 2012 5:04 am

Hi Sam,

I don't have any ODBC connection to test with though...

Could you explain in more details what is going on what you think is the problem?

Post by **jerome** » Wed Dec 05, 2012 5:07 am

Ah, I just noticed.
I don't think Array<String *> will work.

There is a bug http://ecere.com/mantis/view.php?id=770

(Basically, at the moment template parameters must be either a basic data type e.g. int or an eC class/struct/enum/union. Pointers and 'C' style types (e.g. 'struct Bla' or typedefs) are not yet supported.)

Post by **jerome** » Wed Dec 05, 2012 5:08 am

You can work around it by making a class that contains a String *

Or you could do

Array<Array<String>>

samsam598 · Post by **samsam598** » Wed Dec 05, 2012 9:02 pm

jerome wrote:Hi Sam,

I don't have any ODBC connection to test with though...

Could you explain in more details what is going on what you think is the problem?

Hi Jerome,

My main problem is not knowing exactly how to write a function which return an array of array of String,the data is from a SQL record set,and the 2 dimensional array should properly deal with Chinese character.Below is the pseudo code:

Code: Select all

 
String** fromSQLquery(const char* sql)
{
   String** returnArray;//2 dimensional array of String* to hold the whole result set.
   char* buffer;//which to hold the value of each column in a row from the sql query
    foreach(RecordSet row rectrieved from sql query)
   { //so many rows retrieved from sql query
        for(i=0;i<columnsCount of each row;i++)
        {//columnsCount columns in a row
            SQLGetData(hstmt,i,SQL_C_WCHAR,buffer,50,&buflen);//retrieve each column and hold by buffer
           //And somebody told me SQL_C_WCHAR instead of SQL_C_CHAR should be used for Chinese
           //then what should I create to hold that,array of char* or array of wchar*?
            //------------1:save buffer to 1 dimentional array[0],array[1]...
        } 
         //--------------2:save 1 dimensional array to returnArray[0],returnArray[1]....
   }
   //--------3:Now we have a 2 dimensional Array of String to hold the whole result set from a SQL query.
   return resultArray;
}

Post by **jerome** » Wed Dec 05, 2012 9:35 pm

Hi Sam,

The ODBC SQLGetData with SQL_C_WCHAR is going to return you strings encoded in UTF-16.
You can use UTF16toUTF8 or UTF16toUTF8Buffer to convert those UTF-16 strings into UTF8 strings which the Ecere API expects.

As for the storage itself, you could use String ** and allocate it as such:

Code: Select all

int i, j;
int numRows, numCols; // Assuming you have these filled up already
String ** array = new String *[numRows];
for(j = 0; j < numRows; j++)
{
   array[j] = new String[numCols];
   for(i = 0; i < numCols; i++)
      // Assuming it's in UTF16, this will return newly allocated memory like CopyString would
      array[k][i] = UTF16toUTF8(data[j][i]); 
}

Alternatively, you could just allocate it as one block:
String * array = new String[numRows * numCols];
and refer to each as array[j * numCols + i];

Or you could use the Array<Array<String>>:

Code: Select all

int i, j;
int numRows, numCols; // Assuming you have these filled up already
Array<Array<String>> array { size = numRows };
for(j = 0; j < numRows; j++)
{
   array[j] = { size = numCols };
   for(i = 0; i < numCols; i++)
      // Assuming it's in UTF16, this will return newly allocated memory like CopyString would
      array[k][i] = UTF16toUTF8(data[j][i]);
}

Hope this helps

Regards,

Jerome

samsam598 · Post by **samsam598** » Wed Dec 05, 2012 10:09 pm

Jerome,

Sorry we don't know the total NumRows until reach the end of the loop(but we know numCols before the loop):

Code: Select all

 
int numRows=0;
int numCols=0;
...
retcode=SQLNumResultCols(hstmt,&numCols);        
...
 while(true)
    {  
       ...
       char* pszBuf;
       ...
       char** rowData=new char*[numCols+1];  
       if(SQLFetch(hstmt)==SQL_NO_DATA)
       {
           break;
       }
       for(i=1;i<=numCols;i++)
       {
           ...
           pszBuf=new char[colLen+1];
           pszBuf[0]='\0';
           SQLGetData(hstmt,i,SQL_C_WCHAR,pszBuf,50,&buflen);
           rowData[i-1]=CopyString(pszBuf);
         }
       array.Add(rowData);   
       numRows++;//now we knows total numRows
 
    }

Post by **jerome** » Thu Dec 06, 2012 12:27 am

Well you could certainly count the number of rows beforehand, SQL lets you do SELECT COUNT(*)...

But if you don't you can use the dynamic Array class and just use Add() to increase the size of each row. If you use just the pointer then you need to re-allocate the memory for the pointer (renew in eC: array = renew array String *[newRowCount]).

-Jerome