Database connectivity

[Vishi Anand]

Does latest version of SQL toolkit provides features to call stored
procedures in databases like SQL Server or Oracle? I need to call stored
procedure by passing some parameters based on user input. Last time I tried
it (2 yrs ago.) I couldn't do it so had to use VC++.

I know now CVI supports microsoft ADO through ActiveX, haven't tried it
myself, is it simple? Any thoughts between using SQL Toolkit vs. ADO rom
CVI.


vishi

[mfukuchi]

Hello,
Yes, the SQL toolkit has a function to execute a stored procedure. You can go to:

http://www.ni.com/pdf/manuals/370502a.pdf


and see the product manual to see what features are available.

Mika Fukuchi
Application Engineer
National Instruments

[RajV]

Hi,

I am new to CVI and SQL Toolkit. My background is Database programming.

I am working on this project where I am helping our CVI development team to use a Oracle Procedure that returns a "stream" of ASCII text as the output value. The output value can be from 2000 to 3000 characters long (will contain newline \n characters as well)

My problem is: The CVI SQL toolkit code is unable to read the output param...

We are using DBGetParamChar to read the output params.

The code uses DBPrepaerSQL and DBExecutePreparedSQL and finally DBClosePreparedSQL - before reading the output params.

The code goes as follows:

resCode = DBSetAttributeDefault(hdbc, ATTR_DB_COMMAND_TYPE, DB_COMMAND_STORED_PROC);

hstmt = DBPrepareSQL (hdbc, "GETSTRING");

resCode = DBSetAttributeDefault(hdbc, ATTR_DB_COMMAND_TYPE, DB_COMMAND_UNKNOWN);

resCode = DBCreateParamChar(hstmt, "strInput1", DB_PARAM_INPUT, "", 100);

resCode = DBCreateParamChar(hstmt, "strInput2", DB_PARAM_INPUT, "", 100);

resCode = DBCreateParamChar(hstmt, "strReturn", DB_PARAM_OUTPUT, "", 4000);

resCode = DBExecutePreparedSQL(hstmt);

resCode = DBClosePreparedSQL(hstmt);

//Reading output params

resCode = DBGetParamCharBuffer(hstmt, 2, &strReturn, 4000, "");

>>>>Here I get a -2 as the resCode and the value in the strReturn is NULL..I am using everything the CVI manual says, but does not seem to work. Something is missing in myside or the manuals are wrong...

The procedure takes 2 input param and 1 output param. Logging in the procedure shows that the CVI SQL Toolkit's code is making a call the procedure and passing the params and the database is returning the output - (per my logging within the Proc.) The procedure does not error out per the Error handler logs. I have a test code to test the proc via a Web Page and the output is produced.

Any help will be highly appreciated.

Thanks,

Raj

[skykeeper]

Guys Please help us solve this problem...

Oracle Procedure that returns a "stream" of ASCII text as the output value. The output value can be from 2000 to 3000 characters long (will contain newline \n characters as well)

My problem is: The CVI SQL toolkit code is unable to read the output param...

We are using DBGetParamChar to read the output params.

The code uses DBPrepaerSQL and DBExecutePreparedSQL and finally DBClosePreparedSQL - before reading the output params.

The code goes as follows:

resCode = DBSetAttributeDefault(hdbc, ATTR_DB_COMMAND_TYPE, DB_COMMAND_STORED_PROC);

hstmt = DBPrepareSQL (hdbc, "GETSTRING");

resCode = DBSetAttributeDefault(hdbc, ATTR_DB_COMMAND_TYPE, DB_COMMAND_UNKNOWN);

resCode = DBCreateParamChar(hstmt, "strInput1", DB_PARAM_INPUT, "", 100);

resCode = DBCreateParamChar(hstmt, "strInput2", DB_PARAM_INPUT, "", 100);

resCode = DBCreateParamChar(hstmt, "strReturn", DB_PARAM_OUTPUT, "", 4000);

resCode = DBExecutePreparedSQL(hstmt);

resCode = DBClosePreparedSQL(hstmt);

//Reading output params

resCode = DBGetParamCharBuffer(hstmt, 2, &strReturn, 4000, "");

>>>>Here I get a -2 as the resCode and the value in the strReturn is NULL..I am using everything the CVI manual says, but does not seem to work. Something is missing in myside or the manuals are wrong...

The procedure takes 2 input param and 1 output param. Logging in the procedure shows that the CVI SQL Toolkit's code is making a call the procedure and passing the params and the database is returning the output - (per my logging within the Proc.) The procedure does not error out per the Error handler logs. I have a test code to test the proc via a Web Page and the output is produced.

Any help will be highly appreciated.

Thanks,

[Gavin F]

Hi RajV,

Do you have any code that gives you something besides NULL as the strReturn in DBGetParamCharBuffer?
Do you get this behavior if you use the exact code from the manual, in the Library Reference part for DBGetParamCharBuffer?
Do SQL Toolkit examples execute as expected?
What version of CVI and SQL Toolkit are you using?

[JonathanN]

Howdy skykeeper,

I had one thought that might relate to your problem.  The DBGetParamCharBuffer function takes in a char[] data type and so I believe your last statement should go without the &. For example, I think it should say:

ResCode = DBGetParamCharBuffer(hstmt, 2, strReturn, 4000, "");

Hope this helps!

Best Regards,

[RajV]

Hi, We did try the DBGetCharBuffer as well...nothing worked.

 But for the heck of it, we changed the ODBC driver to use the MS Oracle driver from the Oracle installed drivers.....and voila!!!! it started working.....

I guess the Oracle ODBC driver with 10g has something to do with this or Oracle ODBC just doesn't work!....

Anyway, we are progressing with the project now.

Thanks again for all your help with this! I sincerely appreciate it.

Raj