Attempt to fetch into numeric column that is too small

forum.centura.web.developer (2000-2005) & forum.td.web (2005-2010)
NewsgroupServer
Robot
Robot
Posts: 115421
Joined: 24 Feb 2017, 12:00
Location: World wide

Attempt to fetch into numeric column that is too small

Post by NewsgroupServer » 03 Apr 2007, 17:34

 Posted by:  Conrado ZAVALA 

Dear Forum,

I'm currently struggling trying to fix my WebApp. All this time I've been
using Native Routers to handle my database connections but now that I have
several users trying to gatter information I realized that I bypassed a
clear suggestion from Gupta White Paper written by Kumuthini Regavan where
he recommented the use of OLEDB for web applications instead.

I have just wrote a very simple application that populates a child table
based on a query. Here is my PM_Populate code for my table window:

On PM_Populate
! INITIALIZE variables:
Set SqlUDL = "Provider=SQLBASEOLEDB.1;Password=SYSADM;User ID=SYSADM;Data
Source=LIBDCDEV"
Set SqlIsolationLevel = "RL"
Set SqlResultSet = TRUE
Set sSql = "
SELECT DISTINCT LOAD_NO, SHIPSAIL_DT AS SHIPSAIL_DT,
@NULLVALUE( COUNT( CASE_NO ), 0 ) AS TTL_BOXES, @NULLVALUE( SUM( QTY ), 0 )
AS TTL_PIECES, @NULLVALUE( SUM( QTY * SELL_PRICE ), 0 ) AS FOB
FROM LOAD_DETAIL2 WHERE OOM_FLAG = 0 AND RECD_FLAG = 0 AND QTY > 0 GROUP BY
SHIPSAIL_DT, 1"
If SqlCreateSession( hSession, SqlUDL )
If SqlCreateStatement( hSession, hSql )
Call SalTblReset( hWndItem )
Call SalTblPopulate( hWndItem, hSql, sSql, TBL_FillAll )
Call SqlCommit( hSql )
Call SqlFreeSession( hSql )
Return TRUE

After the program gets to SalTblPopulate( ) call I get the following
message:

Err No: 105 :Attempt to fetch into numeric column that is too small;

Can anyone see any problem in my code, this is the first time I try to use
OLEDB. I will appreciate your answer.

Regards,

Conrado

Attachment: SQLBaseOLEDB.udl
Attachment: OneForm.apt

NewsgroupServer
Robot
Robot
Posts: 115421
Joined: 24 Feb 2017, 12:00
Location: World wide

Attempt to fetch into numeric column that is too small

Post by NewsgroupServer » 03 Apr 2007, 17:46

 Posted by:  Conrado ZAVALA 

My column description is the following:

TABLE NAME: LOAD_DETAIL2

LOAD_NO VARCHAR(20)
CASE_NO VARCHAR(20)
QTY SMALLINT
SHIPSAIL_DT DATE
SELL_PRICE DECIMAL(8,3)
OOM_FLAG SMALLINT
RECD_FLAG SMALLINT

Regards,

Conrado

NewsgroupServer
Robot
Robot
Posts: 115421
Joined: 24 Feb 2017, 12:00
Location: World wide

Attempt to fetch into numeric column that is too small

Post by NewsgroupServer » 03 Apr 2007, 18:26

 Posted by:  Jeff Luther 

Well, for one thing you are populating the TW dynamically -- that is, you
have no TW columns defined. And since any dynamic column is always created
as type string there is the chance that since Number != String, that is
the source of your problem.

Your 'very simple' appl. needs to be even simpler: define a correct TW
column for each item in your , add an INTO clause
and try again.

Best Regards,
Jeff @ PC Design
info: www.JeffLuther.net/gupta/

NewsgroupServer
Robot
Robot
Posts: 115421
Joined: 24 Feb 2017, 12:00
Location: World wide

Attempt to fetch into numeric column that is too small

Post by NewsgroupServer » 03 Apr 2007, 19:13

 Posted by:  Conrado ZAVALA 

Here I go:

A web app with Native Router will process the following SQL SELECT with no
problem:

SELECT DISTINCT
LOAD_NO, SHIPSAIL_DT AS SHIPSAIL_DT, @NULLVALUE( COUNT( CASE_NO ), 0 )
AS TTL_BOXES,
@NULLVALUE( SUM( QTY ), 0 ) AS TTL_PIECES, @NULLVALUE( SUM( QTY *
SELL_PRICE ), 0 ) AS FOB
FROM LOAD_DETAIL2
WHERE
OOM_FLAG = 0 AND
RECD_FLAG = 0 AND
QTY > 0
GROUP BY SHIPSAIL_DT, 1

But a SQLBASEOLEDB based web app will expect that the SUM( ) of a SMALLINT
column to be a SMALLINT too and in my case SUM( QTY ) is never going to be a
SMALLINT. So this is what I tried and worked.

SELECT DISTINCT
LOAD_NO, SHIPSAIL_DT AS SHIPSAIL_DT, @NULLVALUE( COUNT( CASE_NO ), 0 )
AS TTL_BOXES,
@NULLVALUE( SUM( @VALUE( QTY ) ), 0 ) AS TTL_PIECES, @NULLVALUE( SUM(
QTY * SELL_PRICE ), 0 ) AS FOB
FROM LOAD_DETAIL2
WHERE
OOM_FLAG = 0 AND
RECD_FLAG = 0 AND
QTY > 0
GROUP BY SHIPSAIL_DT, 1

I replaced SUM( QTY ) by SUM( @VALUE( QTY ) )

It seems that in this case SQLBase Native Router is smarter than SQLBase OLE
DB provider. The Native Router "understands" that a SUM( ) of a SMALLINT
not necessarily will be a SMALLINT so this gets converted automatically to a
NUMBER. If you take a look at the following column of my SQL statement
SUM( QTY * SELL_PRICE ). QTY still being a SMALLINT but SELL_PRICE is a
DECIMAL(8,3) when they get multiplied Native Routers and SQLBase OLE DB
Provider "understand" that the result will use the greatest data type (in
this case DECIMAL(8,3) will be the greatest) so it return a (8,3) value.

Thank You very much Jeff...

NewsgroupServer
Robot
Robot
Posts: 115421
Joined: 24 Feb 2017, 12:00
Location: World wide

Attempt to fetch into numeric column that is too small

Post by NewsgroupServer » 03 Apr 2007, 19:33

 Posted by:  Jeff Luther 

" SQLBase Native Router is smarter than SQLBase OLE DB provider." - Well,
more lenient and forgiving about semi-casting to a best-fit datatype and
length than OLE, I guess. (Though we might argue that this type of best-fit
could lead to an error in the result if something important got 'shaved off'
in the conversion process.)

But, I'm happy to hear you found the problem so quickly! Thanks for letting
us know.

- Jeff

Return to “td.web”

Who is online

Users browsing this forum: [Ccbot] and 0 guests