In a Bind with DisableBind!!

[Former Member]

Hoping someone can shed some light on a few little quirks with DisableBind=0 on SQL Native Client connection (Originally OLEDB).  Would love an explanation in lay-mans terms!

I am working on a system which is multilingual and allows Arabic text be entered and saved into SQL Server 2014 DB.  The fields used to store Arabic values are all nvarchar and we have had to set DisableBind=0 on the DBParm connection string to allow users to input and save the Arabic text correctly into the DB.  We are using Latin collation on the DB and needs to remain so for the main system.

We are now experiencing a few problem areas in the system where there was no issue prior to the DBParm change.

In some screens we are able to insert rows into DWs, set column values in script, allow user input and save without issue.  One particular screen is saving to a DB Table which uses an autoincrementing Identity and contains columns which do not allow NULL values and works fine.

We then have another window where a similar situation occurs, but there is no user data input - it is all done through DW row insert, setitem and update.  In this instance the underlying DB Table also has Identity column and a non-nullable column, but the system just crashes out without any error message.  I can get this window to work by removing all the setitem statements and inserting directly into the table using a SQL INSERT statement, but this is a time consuming change and will be required in a lot of other areas.  The problem seems to hinge on the non nullable column in this instance!!  Once any value is set for it on the DW and and update performed the system crashes.  Excluding this column allows the script to run and complete, but naturally the DB table is empty because the non-nullable column is not set.

I have read about using different DB connection objects, but in this case it will not work as the screen that is failing needs to insert Arabic values.

We are using:

PB 12.1

PFCs

SNC connection to SQL Server 2014.

Thanks in advance.

[Former Member · Accepted solution]

Hi Michael;

  Are you using the SS 2014 client to connect to SS2014  - or  - because you mentioned SNC & PB 12.1 (I'm assuming build 6518) are you using an older SS client?

Regards .. Chris

[Former Member · Accepted solution]

Hi Chris,

I am using build 7217

The 2 options I have available for DB connections in PB are:

SQLNCLI (2005) - SNC 9 for SQL 2005

SQLNCLI10 (2008) - SNC 10 for SQL 2008

I have the SQL Native Client version 11 for SQL 2012 but it does not show up in PB and if I try to force the DBParm to use it I get the following error.

Any idea how I can use the newer SQL Native Client, would be great if that was the solution.

Cheers,

Michael

[Former Member · Accepted solution]

Thanks Michael!

Looks like you have an extra EBF applied above the basic v12.1 level ...  that's great. 

As far as the SNC / DB Client connection is concerned - this worries me a little. The client version and the SS server version are a matched pair. Its like buying different tires for your car with bias on the front and radials on the back. This will work but under certain driving conditions can easily make the driver loose control. I wonder if this is contributing to your DB issues?

FYI: SNC 11 was not supported until PB v12.6 BTW. This is the recommended connectivity for SS2014 by MS (https://msdn.microsoft.com/en-us/library/cc280356.aspx).

FWIW: I have had stability issues in many applications over the many years that I have used PB. I have found that an unstable / incompatible DB client driver can take you out faster than you can say GPF. 

[Former Member · Accepted solution]

I'm with Chris

Having looked at the datatype and other things we are more into the heart of how PB is talking to the DB client driver. Try and get to a matched pair and see if that gets rid of the problem.

Good luck,

David

[Former Member · Accepted solution]

Thanks a million Chris,

I think I now have it working using the SQLNC 11.  I had to revert back to using OLEDB as mentioned by Matt Balent in the thread below.

Settings below:

[OLE DB]

DBParm="PROVIDER='SQLNCLI11',DATASOURCE='SERVER',PROVIDERSTRING='database=DBNAME;APP=APPNAME',OJSyntax='ANSI',PBTrimCharColumns='Yes',DateTimeFormat='\''yyyy-mm-dd hh:mm:ss.fff\''',DateFormat='\''yyyy-mm-dd\''',TimeFormat='\''hh:mm:ss\',Identity='SCOPE_IDENTITY()',DisableBind=0"

Many Thanks For The Help Everyone.

Michael

[Former Member · Accepted solution]

Excellent news Michael!  

Have you hugged your DataWindows today?     

[Former Member · Accepted solution]

Brilliant!

Nice one Chris

[Former Member · Accepted solution]

I always Hug My Datawindow!

[Former Member · Accepted solution]

U Da Man ... keep up the great work!  

[Former Member]

Hi Michael

With disablebind=1 (Bind disabled) then the DW will generate a SQL Insert statement for the columns that have had values set and not the others. With disablebind=0 (enabled) it will use a fixed SQL statement with All columns in the statement and the local variables bound to them.

The default for OLE DB is to disable bind, by changing that you have altered the SQL that is being used to insert data. I think you should capture the SQL generated in the sqlpreview event and see what it has generated in the areas where you have a problem. That may well show why you have a problem.

Your arabic characters will potentially be messed up with disablebind on as it converts them to a string to do the insert.

Looking at the sqlpreview and the SQL it has generated would be my first action in investigating this problem.

Good luck

David

[Former Member]

Thanks David,

I have just tried your suggestion in the hopes of finding something, but the SQL Statement is bizarre, it just contains ? for all values!

Michael

[arnd_schmidt]

Looks like DisableBind=0.

I would check the datawindow's column datatypes, if it fails using Binding.

Eventualy there are some type mismatches.

You can also enable PowerBuilders TRACE feature for the database connection.

http://pbdj.sys-con.com/node/195271

hth

Arnd

[Former Member]

That is correct, the ? represent the bound variables that are shared between PB & the DB. As Arnd has said you will need to verify that the datatypes are compatible.

With disablebind=1 the insert will look like a normal insert but will not include all columns. It will only include the columns that have data. The subtlety of this is that those not included will use the DB default values in not null columns. With bind ON then all values are passed even if they are empty/null and this I believe will override the DB defaults. So not null columns that are not given a value in the DB will be set to null and cause an error.

My guess is that your problem is somewhere around these differences.

Hope that helps.

Regards

David

[Former Member]

Thanks guys,

I did think that might have been an issue, but I cannot find any obvious mismatched DataTypes.

The only columns that are non-nullable are as below (this is just a small snapshot of the full column list).  The fg_id column is the table IDENTITY and I am assuming the DB is handling that as I have not included it on the DW.

The fg_horse column seems to be the one causing me the problems as when I comment out the setitem for this column, the script loop will run albeit without actually updating and inserting the rows.

As soon as the script puts any value into the DW field for fg_horse the DW update causes the program to crash, without any indication of what the problem may be.  If I use SQL Script to do the INSERT there is no problem, so the DB will allow the variable type I am using in script.

The DB column is numeric and shows up in the DW as decimal(8,0).  I have tried using every numeric variable type but none work.  If I exclude the setitem all together and set a default value directly in the column specifications the program still crashes!

It is really puzzling me but if I can find the issue, it will save a lot of time consuming rewrites.

Thanks.

[arnd_schmidt]

I wonder if someone did an edit source or whatever went wrong...

not sure but decimal ( x , y ) in DW Source is new to me.

Try to change the decimal (8,0) to decimal (0) [via edit source] or just add and delete a column via the Datawindow Painter.

SyBooks Online - ColType

hth

Arnd

[arnd_schmidt]

Have you checked the column specification view and a default value (inital value) for that column?

Evenutally the initial value is set during runtime via some code to ... let's say 'bug'.

[Former Member]

Sorry Arnd

The DW does have Decimal(0) as you have correctly assumed.  I had a typo in my post.

M