1. Vishrut Dave
  2. PowerBuilder
  3. Monday, 4 April 2022 09:17 AM UTC

Dear Support Team,

Following code is not working in my environment.

Connect using SQLCA;

integer 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("Database Error", &
			"Cannot connect to database " &
			+ SQLCA.SQLErrText)
		RETURN
END IF

FETCH FIRST my_cursor INTO :cmpnt_Id_var ;
CLOSE my_cursor ;

disconnect using SQLCA;	

I cannot get the result value inside the variable "cmpnt_Id_var". Even it will not throw any kind of error and always I get the 0 in "cmpnt_Id_var" variable.

Could you help me what is wrong in this code? 

 

John Fauss Accepted Answer Pending Moderation
  1. Monday, 4 April 2022 19:54 PM UTC
  2. PowerBuilder
  3. # 1

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

Comment
There are no comments made yet.
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Monday, 4 April 2022 09:35 AM UTC
  2. PowerBuilder
  3. # 2

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

Comment
  1. Miguel Leeuwe
  2. Monday, 4 April 2022 09:40 AM UTC
If that's not the problem, then try doing a FETCH instead of a FETCH FIRST.
  1. Helpful
There are no comments made yet.
Vishrut Dave Accepted Answer Pending Moderation
  1. Monday, 4 April 2022 10:10 AM UTC
  2. PowerBuilder
  3. # 3

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;	

 

Comment
  1. Chris Pollach @Appeon
  2. Monday, 4 April 2022 12:12 PM UTC
Hi Vishrut ... Does the "Select" statement work OK when run in the DB Painter's iSQL Pane?
  1. Helpful
  1. Vishrut Dave
  2. Monday, 4 April 2022 12:19 PM UTC
Well, I am new in Power Builder. Could you please tell me where this iSQL Pane is available in IDE?
  1. Helpful
There are no comments made yet.
Arthur Hefti Accepted Answer Pending Moderation
  1. Monday, 4 April 2022 11:34 AM UTC
  2. PowerBuilder
  3. # 4

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

Comment
There are no comments made yet.
John Fauss Accepted Answer Pending Moderation
  1. Monday, 4 April 2022 13:40 PM UTC
  2. PowerBuilder
  3. # 5

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!

Comment
  1. Chris Pollach @Appeon
  2. Monday, 4 April 2022 15:44 PM UTC
Hi John ... better yet, why not just use a DW Object running this SQL inside a DataStore? ;-)
  1. Helpful
  1. John Fauss
  2. Monday, 4 April 2022 16:17 PM UTC
Yes, of course, Chris... But it really depends on what Vishrut is trying to accomplish, since he did not elaborate. If he only wants/needs the one value, using a DataWindow/DataStore is overkill - particularly for someone new to PowerBuilder. If he instead wants to inspect or interact with multiple component ID's, then using a DW/DS as you suggest is a better way to accomplish it.
  1. Helpful
  1. Chris Pollach @Appeon
  2. Monday, 4 April 2022 16:26 PM UTC
My first suggestion though would be to first run the SQL in the PB IDE's DB Painter's iSQL Pane and see if it even works. ;-)
  1. Helpful
There are no comments made yet.
Vishrut Dave Accepted Answer Pending Moderation
  1. Tuesday, 5 April 2022 07:01 AM UTC
  2. PowerBuilder
  3. # 6

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

 

Comment
  1. Andreas Mykonios
  2. Tuesday, 5 April 2022 08:10 AM UTC
Hi.

And why case b cannot be implemented to a datastore? Why do you really need to use a cursor? What is the max number of rows this query may return?

Andreas.
  1. Helpful
  1. Vishrut Dave
  2. Tuesday, 5 April 2022 08:33 AM UTC
Hi Andreas,

I am not aware about the datastore. So I tried with Cursor. Query will return the maximum 3 rows.

Let me go with datastore to achieve my scenario.
  1. Helpful
There are no comments made yet.
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Monday, 4 April 2022 15:52 PM UTC
  2. PowerBuilder
  3. # 7

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

 

Comment
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.