1. Vinay Batra
  2. PowerBuilder
  3. Monday, 27 January 2020 16:07 PM UTC

Hi all,

I need to save greek letters input by the user into the SQL Server database. 

Table columns are of type nvarchar

Datawindow Update does not work as it saves ??? instead of the letters

I tried to create the update SQL statement with update table_name set columnname = N'greek letter string' and passed the string to a stored procedure

DECLARE lp_sql PROCEDURE FOR sp_execute_sql
   @as_sql = :ls_sql
   using SQLCA;
  
  Execute lp_sql;

Inside the procedure I execute the passed sql with EXEC @as_sql

 

It still does not work. Saves ???

 

I would appreciate if I could get any idea how I can store the greek letters 

 

Thanks

Vinay

Accepted Answer
Michael Kramer Accepted Answer Pending Moderation
  1. Monday, 27 January 2020 20:11 PM UTC
  2. PowerBuilder
  3. # Permalink

Okay, I believe I found the combinations of reasons behind.

PowerBuilder's SQL Server driver has NCharBind parameter

  • NCharBind=0 => Binds string inputs to VARCHAR
  • NCharBind=1 => Binds string inputs to NVARCHAR

Since you have Greek Unicode text you need NCharBind=1.
You may argue => But I already have NCharBind=1, so why doesn't it work?

Documentation of NCharBind parameter states:

DisableBind must be set to 0
For NcharBind to take effect, the DisableBind parameter must be set to 0.
DisableBind=1 overrides the NcharBind setting.

So, your transaction object must use:

this.DBParm += '..., DisableBind=0, NCharBind=1, ...'

What kind of problems does DisableBind=0 cause you elsewhere?

/Michael
(UPDATE: Fixed typo)

Comment
There are no comments made yet.
Vinay Batra Accepted Answer Pending Moderation
  1. Tuesday, 28 January 2020 15:37 PM UTC
  2. PowerBuilder
  3. # 1

Hi Michael

 

I shall look into problems due to DisableBind=0 later on. In order to solve the immediate problem I created another transaction object using same parameters as SQLCA and just replaced the disablebind=1 with disablebind=0 in the connectstring and used the object just for this update of greek letters string. It seems to work fine even at the customer place even without NcharBind=1 but anyhow I added it in the Connectionstring for safety sake. Strangely it was failing yesterday when I used SQLCA with a connectionstring containing DisableBind=0 and without NcharBind for the updation of greek string.

 

Thanks for all your help. It is always helpful to be able to discuss with some one.

 

Regards

Vinay

 

 

Comment
There are no comments made yet.
Vinay Batra Accepted Answer Pending Moderation
  1. Tuesday, 28 January 2020 05:23 AM UTC
  2. PowerBuilder
  3. # 2

Hi Michael

 

Now I have tried to put a breakpoint in sqlpreview event of the transaction object and on my machine it shows the same correct sqlsyntax as below in both the cases (disablebind=1 and 0). However it saves correct letters only when I have disablebind= 0. I need to try it at customer place.

update cu168_riskfras_lang set riskfras_beskrivning = N''Υγρό και ατμοί πολύ εύφλεκτα..'' WHERE serial_number = 1083;

I tried also to log the SQL syntax in the sqlpreview event into another table. 

It logs as below when disablebind = 0

execute sp_execute_sql @as_sql =' update cu168_riskfras_lang set riskfras_beskrivning = N''Υγρό και ατμοί πολύ εύφλεκτα..'' WHERE serial_number = 1083;'

 

It logs as below when disablebind = 1

execute sp_execute_sql @as_sql =' update cu168_riskfras_lang set riskfras_beskrivning = N''???? ?a? atµ?? p??? e?f?e?ta..'' WHERE serial_number = 1083;'

 

Is there some way I can get it to work with disablebind=1?

 

Thanks

Vinay

Comment
  1. Michael Kramer
  2. Tuesday, 28 January 2020 06:39 AM UTC
As I quoted from docs NCharBind=1 only takes effect when DisableBind=0.

And you need that setting for Greek letters to encode correctly.
  1. Helpful
  1. Michael Kramer
  2. Tuesday, 28 January 2020 06:41 AM UTC
Can you show example of code where DisableBind=0 fails?

I'm sure that it is fixable.

(Note: I'm probably offline for several hours very soon.)
  1. Helpful
  1. Vinay Batra
  2. Tuesday, 28 January 2020 06:48 AM UTC
Hi Michael



No issues. I need to dig deeper into finding other issues with DisableBind=0. We can catch up later.

Sorry for the delay in my reply as I am in different time zone.

  1. Helpful
There are no comments made yet.
Michael Kramer Accepted Answer Pending Moderation
  1. Monday, 27 January 2020 22:46 PM UTC
  2. PowerBuilder
  3. # 3

How to trace SQL in your PB app.

Transaction has SQLPreview and DBError events.
SQLPreview triggers just before SQL is sent to the database.
It triggers for both embedded SQL and SQL from DataWindow/DataStore.

What I typically do in any app where I start from no code at all:

  1. Inherit from Transaction and name this class tr_base.
  2. Application > Additional Properties > Variable Types > assign SQLCA := tr_base
  3. In tr_base add following code and breakpoint
    tr_base == event SQLPreview(...)
    int i
    i = 0   // <== Add debug breakpoint on THIS line!
    
  4. Start debugger and run your app.
    Your breakpoint triggers for every SQL statement.
    Disable this breakpoint until your app is where you want to look at SQL.

There are more tracing options but to capture SQL the SQLPreview of Transaction (and of DataWindow + DataStore) are plenty.

HTH /Michael

Comment
There are no comments made yet.
Vinay Batra Accepted Answer Pending Moderation
  1. Monday, 27 January 2020 21:52 PM UTC
  2. PowerBuilder
  3. # 4

Hi Michael

 

The string passed to the procedure contains N prefix as you see below

ls_sql = " update "+ls_table_name+" set "+ls_colname+" = N'"+ls+"' WHERE serial_number = "+String(ll_serial_number)+";"

 

On my machine it works with disablebind=0 even without NcharBind=1. It seems to default to 1. I will anyhow put it at customer Place tomorrow and see.

Regarding other errors due to disableBind=0, one immediate error is "The data type Char and Text are incompatible in the greater than or equal to operation" in a function. I need to find out exactly where.

 

Could you guide me exactly how to trace the exact SQL statement.

 

Thanks for your inputs so far.

Regards

Vinay

 

Comment
  1. Michael Kramer
  2. Monday, 27 January 2020 21:55 PM UTC
Let me write a separate reply to make code more readable. - reply in a few minutes
  1. Helpful
There are no comments made yet.
Michael Kramer Accepted Answer Pending Moderation
  1. Monday, 27 January 2020 19:20 PM UTC
  2. PowerBuilder
  3. # 5

"DisableBind = 0 works!" => That's a hint on what is happening.

Sidebar :: DisableBind=1 means you request PowerBuilder to perform inlining of parameter values. Which is exactly where SQL Injection is possible.

I know, PowerBuilder is good at escaping potentially risky character sequences - but in any case: From "avoid SQL injection" approach you should consider using only DisableBind=0.

The inlining of variable values could have a deficiency of not inserting the >N< prefix on text strings to indicate string is Unicode (instead of ANSI).

Could you try to capture the explicit SQL statement sent to the database when you call the stored procedure? Either trace facility at SQL Server or on PB side - or Transaction object's SQLPreview?

I expect this happens:

// Text sent from PB app
sp_execute_sql '...some text...'

// Correct text to send (notice prefix)
sp_execute_sql N'...some text...'

I will do a test using PB 2019 R2 and MS SQL 2019 - stay tuned (unless someone beats me to the finish line with exact explanation of issue at hand.

HTH /Michael

Comment
There are no comments made yet.
Vinay Batra Accepted Answer Pending Moderation
  1. Monday, 27 January 2020 18:28 PM UTC
  2. PowerBuilder
  3. # 6

Hi Roland/Michael

The procedure is as below

ALTER  procedure [dbo].[sp_execute_sql] @as_sql nvarchar (4000) AS IF @as_sql IS NOT NULL AND LTRIM(RTRIM(@as_sql)) <> '' EXEC (@as_sql)

 

Update statement in SQL Server works fine

Update cu168_riskfras_lang
set riskfras_beskrivning_backup = N'Υγρό και ατμοί πολύ εύφλεκτα.'

 

Select statement returns correct result.

Its just that update from powerbuilder code does not work. Tried putting disablebind=0 in the connectstring then it works on development PC but not on other and moreover it creates problems elsewhere.

 

Thanks

Vinay

 

 

Comment
There are no comments made yet.
Roland Smith Accepted Answer Pending Moderation
  1. Monday, 27 January 2020 18:06 PM UTC
  2. PowerBuilder
  3. # 7

Make sure the stored procedure source code declares the argument as nvarchar.

Comment
There are no comments made yet.
Michael Kramer Accepted Answer Pending Moderation
  1. Monday, 27 January 2020 16:49 PM UTC
  2. PowerBuilder
  3. # 8

You probably have some character conversion going on.

PowerBuilder uses UTF-16. So does MSSQL nvarchar. However, it seems your string is sent as VARCHAR instead of NVARCHAR.

Try the following in DB Painter's ISQL (Notice the prefix = N on second select):

select 'varchar' [Type], 'Α α, Β β, Γ γ, Δ δ, Ε ε, Ζ ζ, Η η, Θ θ, Ι ι, Κ κ, Λ λ, Μ μ, Ν ν, Ξ ξ, Ο ο, Π π, Ρ ρ, Σ σ/ς, Τ τ, Υ υ, Φ φ, Χ χ, Ψ ψ, and Ω ω' [Text]
union
select 'nvarchar' [Type], N'Α α, Β β, Γ γ, Δ δ, Ε ε, Ζ ζ, Η η, Θ θ, Ι ι, Κ κ, Λ λ, Μ μ, Ν ν, Ξ ξ, Ο ο, Π π, Ρ ρ, Σ σ/ς, Τ τ, Υ υ, Φ φ, Χ χ, Ψ ψ, and Ω ω' [Text];

What I see in the Results pane is:

HTH /Michael

Comment
  1. Michael Kramer
  2. Monday, 27 January 2020 16:53 PM UTC
Forgot (sorry) > What are the parameters and datatypes for sp_execute_sql ?
  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.
We use cookies which are necessary for the proper functioning of our websites. We also use cookies to analyze our traffic, improve your experience and provide social media features. If you continue to use this site, you consent to our use of cookies.