Tim

This is going to be part of an import routine from Excel spreadsheets. I think you have an error in how you handle parameters

exception class   : EEDBError
exception message : ElevateDB Error #1011 An error occurred with the value Technical Architect (The value would result in truncation).

This nice simple bit of SQL

SELECT _JobCode FROM JobCodes WHERE _JobCode = :Value OR _JobDesc = :Value

was replaced with

SELECT _JobCode FROM JobCodes WHERE
(LENGTH(:Value) <= LENGTH(_JobCode) AND _JobCode  = SUBSTR(:Value,1,LENGTH(_JobCode)))
OR
(LENGTH(:Value) <= LENGTH(_JobDesc) AND _JobDesc  = SUBSTR(:Value,1,LENGTH(_JobDesc)))

which also doesn't work when :Value is

Senior Director, Worldwide Strategic Planning & Business Intelligence

if I alter it to SUBSTR(:Value,1,10) I still get the error and also if I use

(LENGTH(:Value) <= LENGTH(_JobDesc) AND _JobDesc  = CAST(:Value AS VARCHAR(10)))

CREATE TABLE "JobCodes"
(
"_JobCode" VARCHAR(10) COLLATE "ANSI_CI",
"_JobDesc" VARCHAR(35) COLLATE "ANSI_CI",
CONSTRAINT "PK" PRIMARY KEY ("_JobCode")
)


Roy Lambert

Roy,

<< This is going to be part of an import routine from Excel spreadsheets. I
think you have an error in how you handle parameters

exception class : EEDBError
exception message : ElevateDB Error #1011 An error occurred with the value
Technical Architect (The value would result in truncation). >>

I'll make sure this is fixed for the next build, but for now you're going to
have to make sure that any parameter values are within the sizes of the
columns being compared against.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

>I'll make sure this is fixed for the next build, but for now you're going to
>have to make sure that any parameter values are within the sizes of the
>columns being compared against.

Whew - I had this nightmare that you were going to say it was part of the standard

I've already programmed round it, might even be more effective since I'm potentially replacing a query with a length test but code has risen from c10 lines to c100.

Roy Lambert

Tim

><< This is going to be part of an import routine from Excel spreadsheets. I
>think you have an error in how you handle parameters
>
> exception class : EEDBError
> exception message : ElevateDB Error #1011 An error occurred with the value
>Technical Architect (The value would result in truncation). >>
>
>I'll make sure this is fixed for the next build, but for now you're going to
>have to make sure that any parameter values are within the sizes of the
>columns being compared against.

Even that doesn't work. I get

ElevateDB Error #1011 An error occurred with the value An (The value would result in truncation)

with this sql

SELECT _ID, _Forename
FROM Contacts
WHERE            
_Surname = :Surname
AND   
(_Forename LIKE :Forename+'%' OR _Forename IS NULL OR :Forename IS NULL)
AND
(
_Mobile = :Mobile
OR
_HomePhone = :HomePhone
OR
_HomeEMail = :HomeEMail
)

An is the :Forename parameter. _Forename is defined as VARCHAR(15)

This

(SUBSTR(_Forename,1,2) = :Forename OR _Forename IS NULL OR :Forename IS NULL)

seems to work

Roy Lambert

Roy,

<< Even that doesn't work. I get

ElevateDB Error #1011 An error occurred with the value An (The value would
result in truncation)

with this sql >>

It's due to the fact that you're concatenating the parameter with '%', which
is confusing the automatic type/length inference for the parameter.  Assign
the '%' as part of the parameter, and it will work.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim


>It's due to the fact that you're concatenating the parameter with '%', which
>is confusing the automatic type/length inference for the parameter. Assign
>the '%' as part of the parameter, and it will work.

If its able to be confused does that mean you've won the AI prize

Roy Lambert

Roy,

<< If its able to be confused does that mean you've won the AI prize >>

I wish - I could then take a vacation and let the computer do all of the
work.

--
Tim Young
Elevate Software
www.elevatesoft.com