Return Value by System_Data_SqlClient_SqlDataReader_GetValues returns NULL(s)

[RC]

I've been able to communicate with with our SQL Server using the .NET controller. I'm having a problem with the routine.

/*-------------------- Prototype ---------------------*/
int System_Data_SqlClient_SqlDataReader_GetValues
        (System_Data_SqlClient_SqlDataReader Instance_Handle,
         CDotNetHandle values[], int valuesLength,
         int *returnValue, CDotNetHandle *Exception_Handle);

This routine is supposed to return the specified number of columns of the currently read record to speed up getting the individual columns for a record. However the CDotNetHandle values[] always return as NULL even though the *returnValue (Val) returns the correct number of columns (in this case 11). Below is a snippet of the code. Note that there is no problem using the System_Data_SqlClient_SqlDataReader_Get_Item () routines instead but requires 11 calls to get each items and is immensly slow.

static CDotNetHandle chx[100];

 int Status, Val, i;
.

.

.

 while((Status = System_Data_SqlClient_SqlDataReader_Read (srd, &Val, 0)) == 0)
 {
  Status = System_Data_SqlClient_SqlDataReader_GetValues (srd, chx, 11, &Val, 0); // Status returns 0, Val returns 11 BUT chx all NULLS
  if(Status)                
  {                  
   command = "System_Data_SqlClient_SqlDataReader_Get_Item";   
   goto error;               
  }                   

[JonathanN]

Hi RC,

I know you are working with one of our applications engineers through email on the issue but after looking at your post I thought I might throw a suggestion out there.

There are certain instances in which you must programmatically allocate and free memory when using our .NET Library functions as well as our generated wrapper functions.  Specifically, you must call CDotNetAllocateMemory to allocate memory for any reference parameter input string or array.  On that same note, you must call CDotNetFreeMemory to free the memory for any output parameter or reference parameter output strings or arrays.

This topic and several other resource management topics are covered in our Managing Resources help topic in the LabWindows/CVI Help (Help >> Contents; Switch to the Contents tab and navigate to Library Reference >> .NET Library)

In your case, try allocating the memory for the chx array before you make the call to the System_Data_SqlClient_SqlDataReader_GetValues function.

Hope this helps!

Best Regards,

[RC]

int CVIFUNC System_Data_SqlClient_SqlDataReader_GetValues(
 System_Data_SqlClient_SqlDataReader __instance,
 CDotNetHandle * values,
 int __valuesLength,
 int * __returnValue,
 CDotNetHandle * __exception)
{
 int __error = 0;
 char * __parameterTypeNames[1] = {0};
 unsigned int __parameterTypes[1];
 void * __parameters[1];
 CDotNetHandle values__ = 0;
 unsigned int __returnValueTypeId;

 if (__exception)
  *__exception = 0;

 // Pre-process parameter: values
 if (values)
  __errChk(CDotNetCreateArray(
   CDOTNET_OBJECT,
   1,
   0,
   &__valuesLength,
   values,
   &values__));
 __parameterTypeNames[0] = "System.Object[]";
 __parameterTypes[0] = (CDOTNET_OBJECT | CDOTNET_ARRAY);
 __parameters[0] = &values__;

 // Pre-process return value
 __returnValueTypeId = CDOTNET_INT32;

 // Call instance member
 __errChk(CDotNetInvokeGenericMember(
  __assemblyHandle,
  "System.Data.SqlClient.SqlDataReader",
  __instance,
  CDOTNET_CALL_METHOD,
  "GetValues",
  0,
  0,
  1,
  __parameterTypeNames,
  __parameterTypes,
  __parameters,
  &__returnValueTypeId,
  __returnValue,
  __exception));

__Error:
 if (values__)
  CDotNetDiscardHandle(values__);
 return __error;
}

[RC]

OOPS didn't past all the text for reply:

Using the allocation routines as shown below crashes the System_Data_SqlClient_SqlDataReader_GetValuess()

static CDotNetHandle chx[11];

for(i=0; i < 11; i++)
{
chx[i] = CDotNetAllocateMemory(sizeof(CDotNetHandle));
}

Maybe I don’t quite understand what this routine is supposed to do. If I pass chx declared as: static CDotNetHandle chx[11];

Status = System_Data_SqlClient_SqlDataReader_GetValues (srd, chx, 11, &Val, 0);

The function returns ok and returns a “Val” with a value of 11. I assumed the routine is supposed to return chx with 11 CDotNetHandles to the 11 different columns of data. However the array is always returned full of NULLS.

Below is the System_Data_SqlClient_SqlDataReader_ wrapper function generated by CVI. Note that it uses the CDotNetCreateArray() function. If chx is anything other then a CDotNetHandle array of sufficient dimension it crashes.

int CVIFUNC System_Data_SqlClient_SqlDataReader_GetValues(
 System_Data_SqlClient_SqlDataReader __instance,
 CDotNetHandle * values,
 int __valuesLength,
 int * __returnValue,
 CDotNetHandle * __exception)
{
 int __error = 0;
 char * __parameterTypeNames[1] = {0};
 unsigned int __parameterTypes[1];
 void * __parameters[1];
 CDotNetHandle values__ = 0;
 unsigned int __returnValueTypeId;

 if (__exception)
  *__exception = 0;

 // Pre-process parameter: values
 if (values)
  __errChk(CDotNetCreateArray(
   CDOTNET_OBJECT,
   1,
   0,
   &__valuesLength,
   values,
   &values__));
 __parameterTypeNames[0] = "System.Object[]";
 __parameterTypes[0] = (CDOTNET_OBJECT | CDOTNET_ARRAY);
 __parameters[0] = &values__;

 // Pre-process return value
 __returnValueTypeId = CDOTNET_INT32;

 // Call instance member
 __errChk(CDotNetInvokeGenericMember(
  __assemblyHandle,
  "System.Data.SqlClient.SqlDataReader",
  __instance,
  CDOTNET_CALL_METHOD,
  "GetValues",
  0,
  0,
  1,
  __parameterTypeNames,
  __parameterTypes,
  __parameters,
  &__returnValueTypeId,
  __returnValue,
  __exception));

__Error:
 if (values__)
  CDotNetDiscardHandle(values__);
 return __error;
}

[JonathanN]

Hi RC,

I guess my question at this point would be is have you tried to call that function from a .NET language (C#, VB.NET, etc) to validate that you are calling this function correctly? If you can successfully call that function from .NET and have it return the expected results (non-NULL values), then we know it’s a problem with CVI.

If we can get that part working, then it narrows down the problem further. According to the help, GetValues returns NULL for null database columns so make sure that is not the case. It also says that the GetValues method gets all attribute columns in the collection for the current row. I assume Microsoft provides method or properties to verify you are at the correct row? Again, testing this in .NET would verify you know how the function works and determine if you correctly calling it (i.e. are there other methods or properties that need to be set before calling this function??)

I believe Microsoft has some code-snippets to get you started (of course you might already be a sufficient .NET programmer so you won't need these but just in case) here and here.

Best Regards,

[RC]

Hi Jonathan

Yes. Right now we have a C++ program that grabs the data out of the SQL database and writes it out to text files. I have a CVI program that reads these files and produces reports based on this data.

I'm not a .NET programmer (C++ et. al) and CVI is my main platform. However, I though the addition of the .NET functionallity would allow me to get the data directly from the SQL Server instead of having CVI run the C++ program and then read the files produced. I'm basically implementing the functionallity of the above C++ program into the report program.

Getting data using the CVI .NET has gone reasonably well except I'm disappointed with the amount of time it takes to pull the individual fields with these functions. Therefor I tried the "GetValues" function that as the documentation says should give back all the fields in one call. This is the first function I've been unable to get working in CVI. The GetValue function which again only pulls one field works fine.

I've sent my program (stripped to bare minimum to show problem) to support. After looking at the online documentation on the routine I'm reasonable sure that the GetValues function should be returning the CDotNetHandle array I pass with handles to the fields. However, it just returns the number of fields it thinks it successfully retrieved in the return value but the array is just NULL pointers.

Best Regards,

Rene

[JonathanN]

Hi Rene,

Is your C++ program MFC based or is it managed C++ meaning that you are using the .NET assemblies? In that I mean is your C++ program calling the GetValues method? I assume that you know it works because your C++ program is using that function already?

Best Regards,

[RC]

Hi Jonathan

Unfortunately I am not able to implement with GetFields due to lack of Knowledge of C++.
Below is program stripped to test case

Best Regards,
Rene

// This is a program to grab Project Management data out of the Project Management database and output
// to ASCII readable form for the project management report generator

#include "stdafx.h"
#include <iostream>
using namespace std;

#using <mscorlib.dll>

#using <System.dll>        // For Console I/O
#using <System.Data.dll>   // For ADO.NET

using namespace System;
using namespace System::IO;
using namespace System::Text;
using namespace System::Data;              // Generic ADO.NET definitions
using namespace System::Data::SqlClient;   // Specific definitions for SQL Server data provider

char *prog;
char line[2000];
char *outdir;
char *FirstPoDate;
char *TCStartDate;
char *TCStopDate;

///////////////////////////////////////////////////////////////////////////
// Get Allocations information
//
///////////////////////////////////////////////////////////////////////////
class get_allocations
{
public:
 void Run()
 {
  int i = 0, k;
  int Total = 0;
  String * OutFile;
  //String * str;
  //strcpy(OutFile, outdir);
  //strcat(OutFile, "projects_test.txt");
  OutFile = String::Concat(".\\","Allocations.txt");
  //Console::Write("Creating File: ");
  Console::WriteLine(OutFile);

  FileStream * fs = new FileStream(OutFile, FileMode::Create);
  StreamWriter * w = new StreamWriter(fs);
  // Create a SqlConnection object
  SqlConnection * conn = new SqlConnection();
  //
  // Set the connection string
  //
  conn->ConnectionString = S"data source=NT2; integrated security=true;initial catalog=ProjectMgmt";
  //
  // Get Project Manpower records
  try
  {
   // Try to open the connection
   conn->Open();
   //Console::WriteLine(S"Connected to database master 1 successfully!");
   // Create a command object
   SqlCommand * Allocations = new SqlCommand();
Allocations->CommandText = S"SELECT p.full_id, q.type, q.fiscalyr, q.postdate, t.taskid, t.empfname, t.amt, t.mantime, t.taskdesc, t.startdate, t.stopdate FROM dbo.Projects AS p INNER JOIN dbo.ProjResource AS q ON p.full_id = q.projectid INNER JOIN dbo.ProjManTime AS t ON q.actionid = t.actionid WHERE q.type = 'manpower' AND (p.isactive = 1) ORDER BY p.full_id ASC";

   Allocations->CommandType = CommandType::Text;
   Allocations->Connection = conn;
   // Execute the query
   SqlDataReader * reader = Allocations->ExecuteReader();
   i = 0;
w->Write(reader->FieldCount);
w->Write("\r\n");
   while (reader->Read())
   {
    //w->Write(reader->GetValues(k));
    for(k=0; k < 11; k++)
    {
     w->Write(reader->GetValue(k));
     w->Write(S"\t");
    }
    w->Write("\r\n");
    // Execute the query
    i++;
   }
   Console::Write(S"Number of Manpower Records Written: ");
   Console::WriteLine(i);
   Total += i;
   reader->Close();
  }
  catch (SqlException * Xcp)
  {
   Console::Write(S"Error occurred: ");
   Console::WriteLine(Xcp->Message);
   cin.ignore();
  }

  // Close the connection
  if (conn->State != ConnectionState::Closed)
  {
   conn->Close();
  }

  // Close the connection
  if (conn->State != ConnectionState::Closed)
  {
   conn->Close();
  }
  
  Console::Write(S"Number of Allocations Written: ");
  Console::WriteLine(Total);
  w->Flush();
  w->Close();
  //Console::WriteLine(S"The database connection is now closed");
  //Console::ReadLine();
  //cin.ignore();
 }
};
////////////////////////////////////////////////////////////////////////////
// This is the entry point for this application
////////////////////////////////////////////////////////////////////////////
#ifdef _UNICODE
int wmain(void)
#else
int main(int argv, char **argc)
#endif
{
 int DoIt = 0;
 int Pause = 0;
 double version = 1.23;

 prog = *argc++;
 Console::Write(S"GetSqlData Version: "); //output "getsqldata"
 Console::WriteLine(version); //output "getsqldata"
 Console::WriteLine(prog); //output "getsqldata"
 
 get_allocations * allocations = new get_allocations();
 allocations->Run();
 cin.ignore();
}

[JonathanN]

Hi RC,

It appears in the code you posted you have the statement //w->Write(reader->GetValues(k)); commented out and you are using the GetValue method. I am a little confused now because I thought you mentioned that the GetValues method was working for you but in the code, you have it commented out. Did the developer who wrote this code discover some issues and that's why the code doesn't use that method?

Best Regards,

[RC]

Thats just a commented out statement from trying to implement GetValues(). Note that the program uses GetValue(). The commented statement is just a leftover from trying to implement GetValues() to try and answer your question about whether or not it works for a .NET applications.

As I understand it from the documentaiton I need to implement something like this:

while (reader->Read())
{

    Object[] values = new Object[reader.FieldCount];
    int fieldCount = reader.GetValues(values);

    // Print the object then read the next
  Console.WriteLine("reader.GetValues retrieved {0} columns.",
        fieldCount);
     for (int i = 0; i < fieldCount; i++)
         Console.WriteLine(values[i]);

}

However I can't get this to compile as the Object is undifined and I don't know what is missing to define it.

I also don't want to spend anymore time on the C++ program as I want to get the CVI program running. As I stated before most of the routines have worked in CVI except the reader.GetValues() method.

Regards,

Rene