SQLSTATE 22005 error code - Invalid character value in cast specification

kathotia
kathotia used Ask the Experts™
on
I have a data window which has as stored procedure as its data source. The parameter passed to the stored procedure is of type datetime. In the datawindow design mode and when previewing, the data is properly retrieved on entering the datetime parameter.

However when this datawindow control is embeded in a window and data is retrieved by passing a datetime parameter through script, the retrieve fails and the error mentioned as below is displayed

Select Error: SQLSTATE =22005
[Microsoft][ODBC SQL Server Driver] Invalid character value for cast specification

I am unable to resolve the problem. I am using PB10 eval version. Please help
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
i think the variable you are passing  is of date type where the argument in the datawindow is datetime or vis versa check your setting

or post the script where you are retrieving the data for the data window
gajender

Author

Commented:
the procedure uses a datetime variable to receive the argument and the datawindow retrieve function also passes a datetime variable.

Earlier I used PB 6.5 with MSS conntivity to SQL server but with PB 10.2 native connectivity is not available, I am using OLEDB to connect and this ODBC related problem has crept up.

POST YOUR SQL AND THE SCRIPT
OWASP: Threats Fundamentals

Learn the top ten threats that are present in modern web-application development and how to protect your business from them.

Author

Commented:
The following is the stored procedure

CREATE PROCEDURE sp_checksyntax(@ldm datetime) AS
BEGIN
SELECT sdm = @ldm
END


Powerbuilder is not allowing me to even create a datawindow using the above stored procedure. I just gives out the error code as stated above.

This problem is definetly with ODBC drivers.

Author

Commented:
The problem is not specific to datetime variable, I changed the variable to varchar still the same problem
However, if i excute the sp from ISQL the procedure works .

Commented:
I think all you need to do is turn off 'Use Call Escape Syntax' in your database profile.

Author

Commented:
i have tried all permutation in database profiles. Still the same problem. Even though data window creation is now possible but retreiveal through power script --- dw_1.retrieve(ldt_ldm) is still giving the same error.

Author

Commented:
I have tried connecting to SQL server through  ODBC, OLEDB, ADO .NET but at all times the same erro props up and the data retreival fails

Please help
I believe the following may solve your problem:

In development mode, on the "Connection" tab of the database profile, enter the following string for the "Driver-Specific Parameters"

DelimitIdentifier='No',MsgTerse='Yes',CallEscape='No',FormatArgsAsExp='N'

I hope this helps. Let me know if this solves your problem
Hi,

can u post ur connectstring...


here is an example...
SQLCA.DBParm = "ConnectString='DSN=PC-CMS;UID=sa;PWD=;',StripParmNames = 'Yes'"

so what u need to add is  --->  ,StripParmNames = 'Yes'

this will sort ur problem...

Let me know if u need more assistance...

Cheers,
Rosh

Author

Commented:
when using ADO.net 'stripparmnames' is not supported

Author

Commented:
moderators please split the points to all persons who gave a response and close the topic.

I found out the reason for the problem but notthe solution.

The problem is specifically with ODBC and is not there with OLEDB or ADO.NET. Actuallythe template generated by PB102 uses ODBC by force througha NVO. which resulted in the error.

He problem got solved by using ADO.NET and connecting to the database usning powerscrip after disabling th NVO.

Commented:
Kathotia,
I went back through some of my code. I have run into this before.
The problem is the DateTime column.
PowerBuilder uses its PBODB??.INI file to keep information about each of the database types
using ODBC. If the settings in the PBODB??.INI has the Datetime formatting set to a format
that is incompatible with the database format, you will run into this error.
If you use embedded SQL in a script, you can see how this works.
Example:
SELECT ??
INTO :var
FROM tbl
WHERE col = :dtData
Reguardless of the dtData formatting, when PowerBuilder passes it to the database, the
datetime formatting will be changed to match the formatting in the PBODB??.INI file.
Changing the dtData to a StrData will have no effect.
An automattic type conversion will still convert the data into a datetime.
You can test this by changing the formatting of the datetime data being passed.
Example:
SELECT ??
INTO :var
FROM tbl
WHERE col = :var2  

var2 = String(dtData, 'mm/dd/yyyy hh:mm:ss')
  OR
var2 = String(dtData, 'yyyy/mm/dd hh:mm:ss')
  OR
var2 = String(dtData, 'yy/mm/dd hh:mm:ss')
etc...

I had alot of trouble with this in PB 7 but not any any other version.
Your simptoms appear to be axactly the same.
Also, check the 'Syntax' tab of your profile. You will see
entries for 'Date Format', 'DateTime Format', etc..
Typically they are empty. If you have something there, remove it.
If yours are empty, fill them in.

Ben

Author

Commented:
I was facing this problem not with datetime type variable but will all type of paramters so it is logically assumend the problem lies with ODBC drivers.

SYBASE site has solutions to this problem but it is locked and is avaiable to only PAID SUPPORT SUBSCRIBERS.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial