• You are here:  
  • Home
  • Q&A
  • Q&A
  • PowerBuilder
  • ODBC::Argument data type text is invalid for argument 1 of upper function.

Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (4)

Resolved ODBC::Argument data type text is invalid for argument 1 of upper function.

  1. How-to
0
Votes
Undo
  1. Suhas Shravagi
    Suhas Shravagi
  2. PowerBuilder
  3. Tuesday, 25 January 2022 16:12 PM UTC

Hi

I have a desktop application developed in PB2017 R3. It is connecting to the MS SQL database thru ODBC with connection string as below:

SQLCA.DBMS = "ODBC"
SQLCA.AutoCommit = True
SQLCA.DBParm = "ConnectString='DSN=dsn_name;UID=xyz;PWD=abc',CommitOnDisconnect='No',TrimSpaces='Yes'"

With this connection string, the connection to the database is happening successfully as SQLCA.SqlCode is 0. However, it is failing to execute one query in the application. The query looks like somewhat as below:

SELECT IsNull(col1,'') INTO :ls_pwd
FROM tab_name
WHERE Upper(col2) = Upper(:struct.id)
USING SQLCA;

Here struct.id is a field from struct structure and its datatype is String. The column col2 is of datatype Char(10) in the database. This query execution is throwing the error "SQLSTATE = 37000 [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Argument data type text is invalid for argument 1 of upper function."

I tried using convert function as below and it is successful:

SELECT IsNull(col1,'') INTO :ls_pwd
FROM tab_name
WHERE Upper(col2) = Upper(Convert(VARCHAR(MAX),:struct.id)
USING SQLCA;

 

But this solution is not feasible as the application has wide number of such queries and structures. Is there any good solution for this issue? 

 

--Thanks,

Suhas.

  1. ODBC
  2. PowerBuilder
  3. SQL Server
Blank Pages between some composite reports - tried...
PB exe breaks when assigning a dataobject
Accepted Answer
Arnd Schmidt
Arnd Schmidt Accepted Answer Pending Moderation
  1. Thursday, 27 January 2022 23:38 PM UTC
  2. PowerBuilder
  3. # Permalink

Hi Suhas,

you can try using "DisableBind=1" , although I do not like this kind of problem solving - but this might work fine if your database is not designed using unicode (nchar / nvarchar /ntext) datatypes and you want to stay on using the OBDC Driver.

Try:

SQLCA.DBParm = "ConnectString='DSN=dsn_name;UID=xyz;PWD=abc',CommitOnDisconnect='No',TrimSpaces='Yes',DisableBind=1"

hth

Arnd

 

Comment
There are no comments made yet.
Responses (4)
Miguel Leeuwe
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Thursday, 27 January 2022 17:53 PM UTC
  2. PowerBuilder
  3. # 1

Maybe you have to convert your string value to a blob, since if I'm not mistaken a TEXT datatype field is of type blob and not string, correct me if I'm wrong.

regards

Comment
Miguel Leeuwe
  1. Miguel Leeuwe
  2. Thursday, 27 January 2022 17:55 PM UTC
can you export your datawindow and post it here?
  1. Helpful
There are no comments made yet.
Suhas Shravagi
Suhas Shravagi Accepted Answer Pending Moderation
  1. Thursday, 27 January 2022 13:09 PM UTC
  2. PowerBuilder
  3. # 2

This issue not limited to a window script, but it is there with datawindow as well. If I try to retrieve a datawindow with a retrieval argument of type Text, I am getting the same error.

Comment
There are no comments made yet.
Miguel Leeuwe
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Tuesday, 25 January 2022 18:15 PM UTC
  2. PowerBuilder
  3. # 3

See if it works when doing this:

string ls_pwd, ls_struct_id

ls_struct_id = Upper(struct.id)

SELECT IsNull(col1,'') INTO :ls_pwd
  FROM tab_name
 WHERE Upper(col2) = :ls_struct_id;
Comment
Olan Knight
  1. Olan Knight
  2. Wednesday, 26 January 2022 14:37 PM UTC
Miguel is right. Often the best solution is to break the single, complex statement into separate statements. It does not increase the performance, it's easier to read, it's easier to debug, and it is FAR cleaner clode.



JMHO.
  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.