Retrieve string column from SQL database table

[Kanu]

Hi All,

I am using CVI SQL toolkit to retrieve data from MS SQL database. I am DBGetVariantArray and DBGetVariantArrayColumn to retrieve columns from database tables. I am unable to obtain string columns. Probably, I am not dynamically allocating memory in right manner. I will appreciate if someone can help me out with this.

Regards,

Kanu

[jdubs]

Hi Kanu,

I am curious what happens when you try to get the columns.  Does an error return?  If so, what is it?  Or does it return junk data, or do nothing?  How are you trying to allocate memory?  Also, I just want to check that you are able to get back other data types, such as doubles, and the problem only seems to be with strings.

Thanks!

Jen W

Applications Engineer

National Instruments

[Kanu]

Hi Jen,

I solved the problem. I was not allocating 2D array for string in right manner. Now I am using a pointer to pointer to pointer parameter (char ***cStr) and allocating it in the following manner:

      *cStr = malloc(numRecs * sizeof(char *));
        for(i=0; i<numRecs; i++)
        {
            (*cStr)[i] = malloc(10 * sizeof(char));
        }

       resCode = DBGetVariantArrayColumn (cArray, numRecs, numFields,
       CAVT_CSTRING, 1, 0, numRecs, *cStr);

The code works fine. Further, prior to this I was able to retrieve integer and double columns successfully using pointer to pointer parameter and dynamically allocating them. Anyways, thanks for reply.

Regards,

Kanu

[Kanu]

Hi Jen,

I have a question though. I observed that if a field's value is null for certain records, DBGetVariantArrayColumn just ignores that record. I am ultimately binding the retrieved data to a table control. So in order for all the records to make sense, I need to return a blank for those fields. How should I be doing it ? Should I be using DBGetVariantArrayValue as given in CVI SQL Toolkit help ? But I am afraid as the number of records can go up to millions, DBGetVariantArrayValue processing will take time. I will appreciate if you suggest some alternative.

Thanks,

Kanu

[jdubs]

Kanu,

Good to hear that you got the code working to get back strings.  As far as your other question, the DBGetVariantArrayValue is going to be the best thing to use if you need to handle NULL records.  As seen in the function help, you can handle the NULL case with this function, unlike with DBGetVariantArrayColumn.  You are correct that processing time will go up with the number of records, but this is true no matter what function you are using.  If you are displaying in a table, perhaps you don't need to display all of the millions of records (the data is in the database, so it's not a matter of loosing data, but of how much data your user can actually digest).  If you only display, for example, the first hundred or so, and update the table if necessary with more, then you can cut down on the processing time overall.

Hope this helps,

Jen W

Applications Engineer

National Instruments

[Kanu]

Hi Jen,

One more question. Following code shows error and breaks at NULL data, ie, when rescode = DB_NULL_DATA. How should I handle it so that the code should not throw error and save balnk in the variable instead ?

resCode = DBGetVariantArrayValue (cArray, numRecs, numFields,
                CAVT_CSTRING, i, 1, &cTemp);

I observed one more thing. Whenever my stored procedure returns only one column in the result set, DBGetVariantArrayColumn gives junk data. Is it true or I am doing something incorrect ?

Thanks for help,

Kanu

[jdubs]

Kanu,

If you right click in the function panel for DBGetVariantArrayValue, the function help will come up.  In this, there is some example code that shows how to handle the case where DB_NULL_DATA is returned.  I would suggest doing much the same thing in your case, where you would write empty strings or the like to the table where you would handle the null data.

Also, I wouldn't guess that you should get junk data from DBGetVariantArrayColumn when you return only one column.  Does it work with more than one column returned?  If so, are the kinds of data being returned in each case different?  What else is different between the cases where you get one column or multiple?

Thanks,

Jen W

Applications Engineer

National Instruments