1. Kwadwo Boahene
  2. PowerBuilder
  3. Friday, 6 March 2020 20:48 PM UTC

I have this weird situation where a selected record into variables are 'successful' but the sqlca.sqlnrows parameter is still come out as zero so as the sqlcode and sqldbcode. This is happening sql server. The db2 application is fine.

example below

select a1 into :l_a1

from ssss

where b = 1

using sqlca;

 

The problem is here => IF sqlca.Sqlnrows > 0 then

                               found something - go ahead

 

                                Else

                                     This is where the next step is dropping because the sqlnrows = 0 even though the  variable l_a1 is populated.

                                end IF  

 

PowerBuilder 2017 and Sql server 2016 through ODBC.

 

Thanks,

Kwadwo

 

Gonzalo Villalobos Accepted Answer Pending Moderation
  1. Thursday, 2 February 2023 21:56 PM UTC
  2. PowerBuilder
  3. # 1

I Have solved the issue for ODBC and SQL Server by setting the property

SQLCA.DBParm="DisableBind=1"

This work for me.

Comment
  1. Chris Pollach @Appeon
  2. Friday, 3 February 2023 02:05 AM UTC
Careful though as the "DisableBind=1" setting can produce other DML negative execution side effects. If you continue to use this setting, I would highly recommend that you perform a full regression test of your App against the SS DBMS.
  1. Helpful
  1. Gonzalo Villalobos
  2. Friday, 3 February 2023 12:41 PM UTC
Thanks Chris, we are performing a full regression test for the app now.
  1. Helpful
There are no comments made yet.
Kwadwo Boahene Accepted Answer Pending Moderation
  1. Monday, 9 March 2020 12:51 PM UTC
  2. PowerBuilder
  3. # 2

Thank you all. Chris, I have never used the "native" driver connection since ODBC has always worked for us for the past 20 years. We started with the single digit PB up till now and always used DB2- no problems. This is something new for us. This is how my connection looks like with a little changes to the variables being passed. On the native side what changes should I make?

SQLCA.AutoCommit = False

SQLCA.DBMS = "ODBC"

SQLCA.DBParm = "Connectstring='DSN="+ls_xxxxx+";UID="+ls_aaaaaa+";PWD="+ls_ppppp+"'"+",StripParmNames='Yes'"

////

 

Without understanding why the sqlnrows would not work can't i check the sqlcode =0 and the variable i am selecting into for data. How about using a datawindow to check on the existence of a record?

 

Thanks,

Kwadwo

Comment
  1. Chris Pollach @Appeon
  2. Monday, 9 March 2020 14:10 PM UTC
Hi Kwadwo ... SQLNRows is just one of those hidden "gems " (aka P.I.A.) features of any DBMS connection option that raises its head every 20 years (or less) to bite you in the rear. Unless to try to use a particular feature - and that may only happen too on one type of DBMS - it seems that everything is wonderful. Using ODBC for example with ASE, Oracle, DB/2, etc may not be a problem for NRows. ;-)
  1. Helpful
  1. Michael Kramer
  2. Monday, 9 March 2020 18:09 PM UTC
Let me add: Common advise is to check SQLCode = 0 for success of any embedded SQL statement. Remember in a FETCH loop the End-of-Resultset state is marked by SQLCode = +100.

In "new" versions of PowerBuilder you have SQLPreview and DBError events on BOTH DataWindow/DataStore AND on Transaction object. So, on SQLCA you can track and centralize all SQL both embedded and auto-generated by DataWindow/DataStore objects. Very few SQL statements (like CONNECT) don't fire SQLPreview.
  1. Helpful 1
There are no comments made yet.
Michael Kramer Accepted Answer Pending Moderation
  1. Sunday, 8 March 2020 07:20 AM UTC
  2. PowerBuilder
  3. # 3

Hi Kwadwo, I tested several embedded SQL statements to compare SNC vs. ODBC vs. my expectations.

  • PowerBuilder 2019 R2
  • Microsoft SQL Server 2019
  • DBMS = "SNC"
  • DBMS = "ODBC"   <= ODBC driver = "SQL Server Native Client 11.0", version 2011.110.7462.06
  • DBMS = "ODBC"   <= ODBC driver = "SQL Server", version 10.00.19041.01

MY FINDINGS

  1. The different ODBC drivers acted identical - - - no exceptions
  2. All DBMS drivers return identical SQLNRows - - - except for ROLLBACK
  3. Valid SELECT
    1. SQLNRows = 1
  4. Invalid SELECT returning 2+ rows
    1. SQLNRows = 0
    2. SQLCode = -1  -- read: PB signals failure
    3. SQLDBCode = 0  -- read: SELECT is valid syntax in DB server, but PB has buffer overrun
    4. SQLErrText = "Select returned more than one row"
  5. ROLLBACK - differences
    1. SNC returns number of reverted rows
    2. ODBC returns zero

 

TEST SCRIPT

NOTE - EDITED (I inserted my complete test script so anyone can run same test in your environment so we can compare and discuss results.

// PUBLIC FUNCTION = of_Test_SQLNRows ( )
// Test SQLNRows return values from embedded SQL.
// 1. Create TEST table in database (see comment below).
// 2. Call this function.
// TEST TABLE IN DATABASE --------------------------------------------------------------------------
//		CREATE TABLE dbo.test_sqlnrows(
//			id    int identity not null,
//			name  nvarchar(50),
//			value int,
//		CONSTRAINT pk_test_sqlnrows PRIMARY KEY (id));
// -------------------------------------------------------------------------------------------------
constant string EOL = '~r~n'
constant string TAB = '~t'
long count, id
string text, name
text = 'DBMS = ' + SQLCA.DBMS + EOL + EOL
text += 'Test--' + TAB + 'SQL # rows' + TAB + 'NRows' + TAB + 'Notes---------' + EOL
// -------------------------------------------------------------------------------------------------
text += '(A)' + TAB + 'INSERT 1 row' + TAB
INSERT INTO test_sqlnrows(name, value) VALUES ('Test embedded', 4);
text += string(SQLCA.SQLNRows) + EOL
// -------------------------------------------------------------------------------------------------
text += '(B)' + TAB + 'SELECT 1 row' + TAB
SELECT newID INTO :id FROM (SELECT SCOPE_IDENTITY() newID) data;
text += string(SQLCA.SQLNRows) + EOL
// -------------------------------------------------------------------------------------------------
text += '(C)' + TAB + 'COMMIT 1 row' + TAB
COMMIT;
text += string(SQLCA.SQLNRows) + EOL
// -------------------------------------------------------------------------------------------------
text += '(D)' + TAB + 'SELECT COUNT' + TAB
SELECT count(*) INTO :count FROM test_sqlnrows WHERE value = 4;
text += string(SQLCA.SQLNRows) + TAB + ' COUNT(*) = ' + string(count) + EOL
// -------------------------------------------------------------------------------------------------
text += '(E)' + TAB + 'SELECT ' + string(count) + ' rows' + TAB
SELECT name INTO :name FROM test_sqlnrows WHERE value = 4;
text += string(SQLCA.SQLNRows) + EOL
text += TAB + 'SQL Code   ' + TAB + string(SQLCA.SQLCode) + '   =>' + TAB + 'PB buffer overrun' + EOL
text += TAB + 'SQL DB Code =' + TAB + string(SQLCA.SQLDBCode) + '    =>' + TAB + 'Valid in DBMS' + EOL
text += TAB + 'SQL Err Text  = "' + SQLCA.SQLErrText + '"' + EOL
// -------------------------------------------------------------------------------------------------
text += '(F)' + TAB + 'UPDATE ' + string(count) + ' rows' + TAB
UPDATE test_sqlnrows SET name = REVERSE(name) WHERE value = 4;
text += string(SQLCA.SQLNRows) + EOL
// -------------------------------------------------------------------------------------------------
text += '(G)' + TAB + 'ROLLBACK all ' + string(count) + TAB
ROLLBACK;
text += string(SQLCA.SQLNRows) + EOL
// -------------------------------------------------------------------------------------------------
MessageBox("Test SQLNRows", text)
// DONE

Test Results

Check
no.
SQL SQLNRows
for SNC
SQLNrows
for ODBC
Comments
A INSERT 1 1  
B SELECT 1 1  
C COMMIT 0 0 COMMIT changes no rows
D SELECT 1 1 Result of COUNT(*) is one value in one row
E SELECT 0 0 SQLCode = -1
SQLDBCode = 0
SQLErrText = Select returned more than one row
F UPDATE 17 18 ODBC: extra row inserted - hence 18, not 17
G ROLLBACK 17 0 SNC: ROLLBACK reverted UPDATE to 17 rows.
ODBC: ROLLBACK states 0 despite reverted data.

NOTE: Each time I run my test I add one extra row. That's why my UPDATE gradually increases.

 

HTH /Michael

Comment
There are no comments made yet.
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Saturday, 7 March 2020 05:02 AM UTC
  2. PowerBuilder
  3. # 4
Comment
  1. Chris Pollach @Appeon
  2. Monday, 9 March 2020 14:21 PM UTC
Hi Miguel .. great suggestion. FWIW: I typically use this command at the start of all my ASE & SS "Stored Procedure" scripts.

  1. Helpful
  1. Miguel Leeuwe
  2. Monday, 9 March 2020 14:32 PM UTC
Hmm I think I read somewhere it doesn't work for odbc... maybe oledb? Can't tesr now
  1. Helpful
  1. mike S
  2. Friday, 3 February 2023 14:21 PM UTC
needed in particular for triggers
  1. Helpful 1
There are no comments made yet.
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Friday, 6 March 2020 21:53 PM UTC
  2. PowerBuilder
  3. # 5

Hi Kawado;

   Have you tried using the SS "native" driver vs ODBC?

Regards ... Chris

Comment
  1. John Fauss
  2. Friday, 6 March 2020 22:59 PM UTC
I have verified that a similar in-line SQL SELECT returns SQLNRows=1 in PB 2017, SQL Server DB using teh SNC driver.
  1. Helpful
  1. Chris Pollach @Appeon
  2. Saturday, 7 March 2020 02:22 AM UTC
Hi John ... Sounds like the issue then is around the ODBC middleware. The next question is, is it the SS side or the PB side of the ODBC software. I'm betting on the MS side 1st (best guess). What "provider" are you mapping to in the DSN?

Regards ... Chris
  1. Helpful
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.