1. Luca DG
  2. PowerBuilder
  3. Monday, 04 March 2019

Hello
I'm migrating a PB2017 application to support datatype from CHAR to NCHAR and I have problems using SNC connection migrating from  PB 6.5 and Native MS SQL driver.


This is the connection string:


SQLCA.DBMS = "SNC SQL Native Client(OLE DB)"
SQLCA.LogPass = "XXXXXXXX"
SQLCA.ServerName = "TECNINFO2\TECNINFO2"
SQLCA.LogId = "sa"
SQLCA.AutoCommit = False
SQLCA.DBParm = "Database='SEG001AUGNCHAR',Provider='SQLNCLI10',OJSyntax='PB',DisableBind=0,NcharBind=1,TrimSpaces=1"

Datawindows with multiple row update does not work and bring me back tis error for DELETE:

Microsoft SQL Server Native Client 10.0
Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

No changes made to database.

DELETE FROM pianistudiostudente WHERE matricol = ? AND universi = ? AND facolta = ? AND corsolau = ? AND annocors = ? AND corsi = ?

For INSERT I have the same error:

Microsoft SQL Server Native Client 10.0
Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

No changes made to database.

INSERT INTO pianistudiostudente ( matricol, universi, facolta, corsolau, annocors, corsi, grupinsi, crediti, dispensato, semestre ) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )

The other strange thing is that for insert if I add 3 rows 1 of them is inserted and the other 2 no. Than I get the error.

For DELETE is the same thing.

If I add or delete on row at time eveything works. With PB 6.5 and Native MS SQL driver I have no problem.

Thanks in advance.

Luca DG Accepted Answer Pending Moderation
0
Votes
Undo

Thanks to Roland.

I have used a different transaction object in the SQLPreview event and it works even if I still have the problem regarding the parameter in the SQLSyntax marked as "?".

PB Help says that :

GetSQLPreview and binding

When binding is enabled for your database, the SQL returned in the GetSQLPreview method may not be complete—the input arguments are not replaced with the actual values. For example, when binding is enabled, GetSQLPreview might return the following SQL statement:

INSERT INTO "cust_order" ( "ordnum", "custnum",
"duedate", "balance" ) VALUES ( ?, ?, ?, ? )

When binding is disabled, it returns:

INSERT INTO "cust_order" ( "ordnum", "balance",
"duedate", "custnum" ) VALUES ( '12345', 900,
'3/1/94', '111' )

I need to have the Disablebind parameter enabled for working with NChar so it seems that there is no way...
Comment
I was under the impression that the SNC driver didn't require DisableBind to work with NChar. I know that the OLE DB driver does.
  1. Roland Smith
  2. Wednesday, 6 March 2019
There are no comments made yet.
  1. Tuesday, 5 March 2019
  2. PowerBuilder
  3. # 1
Luca DG Accepted Answer Pending Moderation
0
Votes
Undo

Hello Chris, 

yes I'm using SQL Server 2008 winth SNC 10. 

I have to plan also the migration to major release of SQL Server 2012 or 2016.

I have found that the error affecting the datawindows update or insert of multiple row :

Microsoft SQL Server Native Client 10.0
Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

is due to the SQL Insert I use in the sqlpreview event for logging purpose. One insert work, the second give the error and stop the database update.

With only one row at time it works, even if I have "?" character in place of the bind value.

PB 6.5 application is still working on Windows 10 without problems and basically is the same that I have written on 1998.

PB is great and follow me since Windows 3.11 for Workgroup...

I have PB 2017 since 2017 but only now I have to face with a big migration to NCHAR and NVARCHAR for managing UTF and problems are coming out...

Comment
You could create a second transaction object just for the logging process.
  1. Roland Smith
  2. Tuesday, 5 March 2019
There are no comments made yet.
  1. Tuesday, 5 March 2019
  2. PowerBuilder
  3. # 2
Roland Smith Accepted Answer Pending Moderation
0
Votes
Undo

 

Great to see single digit PowerBuilder users upgrading!

Comment
My sentiments exactly.
  1. Armeen Mazda
  2. Monday, 4 March 2019
There are no comments made yet.
  1. Monday, 4 March 2019
  2. PowerBuilder
  3. # 3
Chris Pollach Accepted Answer Pending Moderation
0
Votes
Undo

Hi Luca;

  You have set the provider to version 10, are you using SS 2008?

Regards ... Chris

Comment
There are no comments made yet.
  1. Monday, 4 March 2019
  2. PowerBuilder
  3. # 4
  • Page :
  • 1


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