Hi,

What type is cmpnt_id in your database table? Maybe your local integer variable is not big enough to hold the returned value.

regards

Hi Miguel,

Type of "cmpnt_id" column is Integer in SQL Server database. So I change the local variable type from Integer to long but result is same. Also, I tried with the FETCH instead of FETCH FIRST but always it returns the 0.

New code as below

Connect using SQLCA;

long cmpnt_Id_var
DECLARE my_cursor DYNAMIC CURSOR FOR SQLSA ;
PREPARE SQLSA FROM "SELECT cmpnt_Id FROM Component" ;
OPEN DYNAMIC my_cursor ;

IF SQLCA.SQLCode <> 0 THEN
		MessageBox("SEIM Preview", &
			"Cannot connect to database " &
			+ SQLCA.SQLErrText)
		RETURN
END IF

FETCH my_cursor INTO :cmpnt_Id_var ;
CLOSE my_cursor ;

disconnect using SQLCA;	

Hi 

you have the connect error handling after opening the cursor (not that it would make a difference) but how about enhancing the error handling and checking the SQLCode after the FETCH? This should give you the error (or none if the table is empty).

Regards
Arthur

Since your posted code sample is fetching only a single row/value, why use a cursor?

Consider using Embedded SQL in your PowerScript code to retrieve the requested value into a PowerScript variable. Assuming SQLCA is connected to the database:

Long cmpnt_Id_var

SELECT cmpnt_Id
  INTO :cmpnt_Id_var
  FROM Component
 USING SQLCA;

However, the above will not work if the Component table contains more than one row. Include a WHERE clause in the SELECT statement or use SELECT TOP 1 cmpnt_id with an ORDER BY clause to ensure only one row is returned by the SELECT statement.

For debugging purposes, you can temporarily preface SQLCA's DBMS property with the keyword "TRACE". For example, instead of coding:

SQLCA.DBMS = "SNC"  // If you are using SQL Server Native Client
// or
SQLCA.DBMS + "MSO"  // If you are using Microsoft OLE DB SQL client

use:

SQLCA.DBMS = "TRACE SNC"
// or
SQLCA.DBMS = "TRACE MSO"

You must set this BEFORE you connect SQLCA to the database.

Doing this will make the PB database driver record all database interaction in a clear-text log file that you can subsequently inspect.

Good luck!

What values do you have in your SQLCA attributes sqlcode and sqlerrtext after doing the CONNECT ?

Hi, Vishrut -

In response to your earlier follow-up question, there is an ISQL (Interactive SQL) Session pane in the Database Painter,. This painter opens in the IDE via the toolbar button shown below:

You need to have previously created a Database Profile in the IDE for your SQL Server database. The toolbar button to the immediate left of the Database button opens the DB Profile dialog.

In the Database Painter window, you'll find the ISQL Session pane here:

Type in your SQL. Be sure to include a terminating semi-colon (red circle), which is required in the the ISQL Session pane. Press Ctrl+L to execute the SQL statement, or click the Execute button in the Painter's toolbar:

Results (if any) can be viewed in the "Results" tab (located next to the ISQL Session tab).

HTH, John

Hi John and Chris,

Thank you for your time to investigate my issue.

Just for your information, here I have used test sql query. In real application it is complex sql Query having joins of multiple tables and where conditions. So that statement will returns multiple rows I will loop through the records and process the data. So that I prefer to use cursor.

Following is the details requirement:

1. There is a Data Window control which displays the PSR report. Set the SQL Query using SetSqlSelect() method and retrieve the data and load data in PSR report.
2. There are few text controls in PSR report which I need to fill manually.

So for the requirement number "#b", I need to execute query separately and loop through the rows and fill the data in text controls.

Hope above information may help you to guide me proper solution.

Regards,

Vishrut