Hi Kawado;

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

Regards ... Chris

Maybe you have to do a "SET ROWCOUNT = 0"

https://docs.microsoft.com/en-us/sql/t-sql/statements/set-rowcount-transact-sql?view=sql-server-ver15

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

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

HTH /Michael

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

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

SQLCA.DBParm="DisableBind=1"

This work for me.