473,785 Members | 2,349 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Bogus data type incompatible error SQLSTATE=22001?

I'm getting an odd error when I try to run a select statement in my
stored procedure. I need to select records where one field = the first
host variable, and the second host variable is between the values of
two other columns. The problem seems to occur when I check all three
conditions. When I have only two of the conditions, the procedure runs
to completion and I get a resultset.

Interestingly, I created a MS Access database with a link to the table
referenced below (via OLEDB). I can get many (if not all) of the
statements below to work in an Access query.

Can anyone help me figure out the true problem here? TIA!

The error message is:
A database manager error occurred.[IBM][CLI Driver][AS] SQL0303N A
value cannot be assigned to a host variable in the SELECT, VALUES, or
FETCH statement because the data types are not compatible.
SQLSTATE=22001

Here are the statements I've tried so far:
--SET stmADDR = 'SELECT ADDR.* FROM TESTFILES.ADDRE SS AS ADDR WHERE
ADDR.ADR_POLICY _NO = ? AND (ADDR.ADR_THRU_ SEQ_NO >= ' || POLSEQ || ')
AND (ADDR.ADR_FROM_ SEQ_NO <= ' || POLSEQ || ')';
--SET stmADDR = 'SELECT ADDR.* FROM TESTFILES.ADDRE SS AS ADDR WHERE
ADDR.ADR_POLICY _NO = ? AND (ADDR.ADR_THRU_ SEQ_NO >= ' || POLSEQ || ')
AND (INT(ADDR.ADR_F ROM_SEQ_NO) <= ' || POLSEQ || ')';
--SET stmADDR = 'SELECT ADDR.* FROM TESTFILES.ADDRE SS AS ADDR WHERE
ADDR.ADR_POLICY _NO = ? AND ' || POLSEQ || ' BETWEEN
ADDR.ADR_FROM_S EQ_NO AND ADDR.ADR_THRU_S EQ_NO';
--SET stmADDR = 'SELECT ADDR.* FROM TESTFILES.ADDRE SS AS ADDR WHERE
ADDR.ADR_POLICY _NO = ? AND ' || POLSEQ || ' <= ADDR.ADR_THRU_S EQ_NO AND
' || POLSEQ || ' >= ADDR.ADR_FROM_S EQ_NO';
--SET stmADDR = 'SELECT ADDR.* FROM TESTFILES.ADDRE SS AS ADDR WHERE
ADDR.ADR_POLICY _NO = ? AND ' || POLSEQ || ' >= ADDR.ADR_FROM_S EQ_NO AND
' || POLSEQ || ' <= ADDR.ADR_THRU_S EQ_NO';
--SET stmADDR = 'SELECT ADDR.* FROM TESTFILES.ADDRE SS AS ADDR WHERE
ADDR.ADR_POLICY _NO = ? AND INT(' || POLSEQ || ') >=
ADDR.ADR_FROM_S EQ_NO AND INT(' || POLSEQ || ') <=
ADDR.ADR_THRU_S EQ_NO';
--SET stmADDR = 'SELECT ADDR.* FROM (SELECT ADDR.* FROM
TESTFILES.ADDRE SS AS ADDR WHERE ADDR.ADR_POLICY _NO = ? AND
ADDR.ADR_THRU_S EQ_NO >= ' || POLSEQ || ') AS ADDR WHERE
ADDR.ADR_POLICY _NO = ? AND ADDR.ADR_FROM_S EQ_NO <= ' || POLSEQ;
--SET stmADDR = 'SELECT ADDR.* FROM TESTFILES.ADDRE SS AS ADDR WHERE
ADDR.ADR_POLICY _NO = ? AND ' || POLSEQ || ' BETWEEN
INT(ADDR.ADR_FR OM_SEQ_NO) AND INT(ADDR.ADR_TH RU_SEQ_NO)';
--SET stmADDR = 'SELECT ADDR.* FROM TESTFILES.ADDRE SS AS ADDR WHERE
ADDR.ADR_POLICY _NO = ? AND INT(' || POLSEQ || ') BETWEEN
INT(ADDR.ADR_FR OM_SEQ_NO) AND INT(ADDR.ADR_TH RU_SEQ_NO)';
--SET stmADDR = 'SELECT tblADDRESS.* FROM (SELECT ADDR.* FROM
TESTFILES.ADDRE SS AS ADDR WHERE ADDR.ADR_POLICY _NO = ? AND
ADDR.ADR_THRU_S EQ_NO >= ' || POLSEQ || ') AS tblADDRESS WHERE
tblADDRESS.ADR_ FROM_SEQ_NO <= ' || POLSEQ;
--SET stmADDR = 'SELECT ADDR.* FROM TESTFILES.ADDRE SS AS ADDR WHERE
ADDR.ADR_POLICY _NO = ? AND CHAR(' || POLSEQ || ') BETWEEN
CHAR(ADDR.ADR_F ROM_SEQ_NO) AND CHAR(ADDR.ADR_T HRU_SEQ_NO)';
--SET stmADDR = 'SELECT ADDR.* FROM TESTFILES.ADDRE SS AS ADDR WHERE
' || POLSEQ || ' >= ADDR.ADR_FROM_S EQ_NO AND ' || POLSEQ || ' <=
ADDR.ADR_THRU_S EQ_NO AND ADDR.ADR_POLICY _NO = ?';
--SET stmADDR = 'SELECT ADDR.* FROM TESTFILES.ADDRE SS AS ADDR WHERE
ADDR.ADR_POLICY _NO = ? AND (ADDR.ADR_THRU_ SEQ_NO >= ?) AND
(ADDR.ADR_FROM_ SEQ_NO <= ' || POLSEQ || ')';
--SET stmADDR = 'SELECT ADDR.* FROM TESTFILES.ADDRE SS AS ADDR WHERE
ADDR.ADR_POLICY _NO = ? AND (ADDR.ADR_THRU_ SEQ_NO >= ?) AND
(ADDR.ADR_FROM_ SEQ_NO <= ?)';

-- Lines below work, but don't check all three conditions
--SET stmADDR = 'SELECT ADDR.* FROM TESTFILES.ADDRE SS AS ADDR WHERE
ADDR.ADR_POLICY _NO = ? AND ADDR.ADR_THRU_S EQ_NO >= ' || POLSEQ;
--SET stmADDR = 'SELECT ADDR.* FROM TESTFILES.ADDRE SS AS ADDR WHERE
ADDR.ADR_POLICY _NO = ? AND ADDR.ADR_FROM_S EQ_NO <= ' || POLSEQ;
--SET stmADDR = 'SELECT ADDR.* FROM TESTFILES.ADDRE SS AS ADDR WHERE
' || POLSEQ || ' >= ADDR.ADR_FROM_S EQ_NO AND ' || POLSEQ || ' <=
ADDR.ADR_THRU_S EQ_NO';
PREPARE sADDR FROM stmADDR;
OPEN cADDR USING POLICYNO;

Additional Info:
I am using DB2 UDB v 8.1 running on an iSeries model 270.
I coded and tested my stored procedure using the DB2 Development
Center.

Nov 12 '05 #1
0 4920

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
5264
by: Bj?rn Terje Svennes | last post by:
I'm using ODBC to interface a Microsoft SQL Server 2000. One of the operations involves placing files within BLOBs. I'm using the image data type for this purpose. Most of the time this works okey, but when trying to add a 21,3 MB file I get an error. The error code is 22001, which means "String right-truncation". But why? Does this mean that the field cannot accepts BLOBs with this size?
10
49598
by: joel.brewster | last post by:
We have a VB6 application using ADO version 2.5 and I am receiving a " CLI0109E String data right truncation. SQLSTATE=22001" error when I execute the rs.UpdateBatch method. I have determined that the error is caused by an extended ascii character 164 (ñ). Has anyone else encountered this problem and found a solution. TIA Joel Brewster Mutual Of Omaha
1
60963
by: gimme_this_gimme_that | last post by:
When you get the message : String data right truncation. SQLSTATE=22001 What is usually the source of the problem? Might a null value assigned get a PreparedStatement's setString method be the problem? Thanks.
2
6617
by: roha_78 | last post by:
COM.ibm.db2.jdbc.DB2Exception: CLI0109E String data right truncation. SQLSTATE=22001 at com.ibm.ejs.container.ContainerManagedBeanO.postCreate(ContainerManagedBeanO.java:500) at com.ibm.ejs.container.EJSHome.postCreateCommon(EJSHome.java:2050) at com.ibm.ejs.container.EJSHome.postCreate(EJSHome.java:1953) at...
2
4885
by: roha_78 | last post by:
i am using the follwing code to connect to db2 : java.sql.Connection connection = null; java.sql.ResultSet rs = null; ExperimentJDBCHelperBean jdbchelper = new ExperimentJDBCHelperBean(); DataSource myDS = ExperimentJDBCHelperBean.getDataSource();
1
4857
by: jrs_14618 | last post by:
Hello All, This post is essentially a reply a previous post/thread here on this mailing.database.myodbc group titled: MySQL 4.0, FULL-TEXT Indexing and Search Arabic Data, Unicode I was wondering if anybody has experienced the same issues
0
3189
by: priyankas | last post by:
I am getting this error when doing a SELECT statement . COM.ibm.db2.jdbc.DB2Exception Message is: CLI0 :109E String data right truncation. SQLSTATE=22001 Can anyone help me out with this?
1
8745
by: Justin | last post by:
I have a function that has a varchar about 300 characters. When I run the insert command from a function I receive the error SQL0433N - Value .... is too long SQLSTATE=22001. If I run the query outside of the function it seems to work fine. I read about char limitations of 255 characters does a user defined function (UDF) limit me with 255 characters as well?
0
6559
by: Brenda | last post by:
We are on DB2, AIX fixpak 14 This error happens when running PeopleSoft PeopleCode Application Engine process. I have checked other posts out here and it seems like others who have reported it seem to be saying their is a problem with the way DB2 is interpreting a string statement with a varchar field. -- 00.03.40 ..(ZZ_EX_NOTS.SendMail.Send3) (PeopleCode)
0
9646
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
10346
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10096
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9956
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
8982
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7504
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6742
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
1
4055
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
3658
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.