A good One... Need a lot of help !!! OdrPrepareProc

forum.advanced.programming (1998-2005)
Remi

A good One... Need a lot of help !!! OdrPrepareProc

Post by Remi » 20 Dec 2004, 21:14

 Posted by:  Remi 

Hi Guys!!!

This is a good one..
I just start to using the library odbsal32.apl and i start to send my
procedures to sql server, but ....
when i execute the procedure with the lines below
!!CB!! 129
Set sSql = "{call PRC_ORDERDETAIL_MAN (:sDML , :dfOrdNum, :tbl1.colOrdLine
, :tbl1.colProductId, :dfManId, :tbl1.colQuantity ) }"
Call OdrPrepareProc( hSql, sSql , ':sCampo1')

SQL server return me an error that say 20026 Invalid Character value for
CAST specification

i was triying everthing and i discovered that the problem is genereted by
the variable :sIdMan char(4)
i send the instruccion

!!CB!! 129
! Set sSql = "{call PRC_ORDERDETAIL_MAN (:sDML ,'007651', :tbl1.colOrdLine
, :tbl1.colProductId, '0001', :tbl1.colQuantity ) }" !esta funciona bien

and was workig perfectly....

now i don't know what to do???

Greetings

remi
p.d.
this is the procedure parameters...

CREATE PROCEDURE PRC_ORDERDETAIL_MAN
@DML CHAR(1), -- I=INSERT , U=UPDATE
@ORDNUM CHAR(6), @ORDLINE CHAR(3), @PROID CHAR(4), @MANID CHAR(4), @ORDQUAN
CHAR(3)

Remi

A good One... Need a lot of help !!! OdrPrepareProc

Post by Remi » 20 Dec 2004, 21:36

 Posted by:  Remi 

another test....
!!CB!! 129
! Set sSql = '{call PRC_ORDERDETAIL_MAN ("I" , "007651", "001", "0001",
"0001", "12" ) }' no work
Set sSql = "{call PRC_ORDERDETAIL_MAN ('I' , '007651', '001', '0001',
'0001', '12' ) }" work fine
Call OdrPrepareProc( hSql, sSql , ':sCampo1')

Martin Duty

A good One... Need a lot of help !!! OdrPrepareProc

Post by Martin Duty » 20 Dec 2004, 23:02

 Posted by:  Martin Duty 

How about trying:

Set sSql = '{call PRC_ORDERDETAIL_MAN (\'I\' , \'007651\', \'001\',
\'0001\', \'0001\', \'12\' ) }'

Though I'm not sure if this is the problem. Are all the parameters suppose
to be strings and not numeric? And if so, are all the fields, variables, and
columns string type? From the error 20026, I would think that is the
problem.

Martin

--
Martin Duty
IT Manager
Underground Vaults & Storage, Inc.
TD 3.0 PFT2
Client Win 2K/XP
Database MS Sql Server 2000

Krister Henningsson

A good One... Need a lot of help !!! OdrPrepareProc

Post by Krister Henningsson » 21 Dec 2004, 09:03

 Posted by:  Krister Henningsson 

dfManId should be string type and not more than 4 characters then.....

dfsManId would be a better name :-)

--
--
Regards, Kriss

soon Former Gupta Developer :-/
FAQ-stuff
------------------------------------------------
OS: WindowsXP
GUPTA: TD3.1 PTF1(R)
Client 9i (9.2)
Db: Oracle 8.1.7
------------------------------------------------
"Sitting target
Sitting waiting
Anticipating
Nothing
Nothing

Life
Is full of surprises
It advertises
Nothing
Nothing

What am I trying to do
What am I trying to say
I'm not trying to tell you anything
You didn't know
When you woke up today

Sitting target
Sitting praying
God is saying
Nothing
Nothing

Always
Knows the prospects
Learnt to expect
Nothing
Nothing"
--Depeche Mode

Remi

A good One... Need a lot of help !!! OdrPrepareProc

Post by Remi » 21 Dec 2004, 19:40

 Posted by:  Remi 

Hi Martin :
Yes, everithing was string, and the problem is caused by de string
sIdMan...
It's very wear to me... I made some more test.. and these is the result....

First i try to discover wich one of these variables and datfields are
causing the problem... and i made this changes in the call of the
procecure....

!!CB!! 129
! Set sSql = "{call PRC_ORDERDETAIL_MAN (:sDML ,:dfOrdNum, :tbl1.colOrdLine
, :tbl1.colProductId, '0001', :tbl1.colQuantity ) }" !esta funciona bien

In this new line, i was sure that the :sIdMan is the one that is causing the
problem, cause every time that i replace
the '0001' whith :sIdMan the message come out...
One more wear thing i was so tired to do this so i decide to move the
sentences in a sqlinmediate line
and it's work pretty fine...
The sIdMan is declare as string variable and when is in debug mode adopt
the value of "0001"

CENTURA 3.0
SQL SERVER 7.0 personal edition
WIN XP

happy holydays
remi

Martin Duty

A good One... Need a lot of help !!! OdrPrepareProc

Post by Martin Duty » 21 Dec 2004, 22:34

 Posted by:  Martin Duty 

When we switched from SQL 6.5 to 7.0 I had some problems. I had intermixed
single and double quotes in my sql statements. They worked fine in 6.5, but
7 didn't like it very well. I had to go through all my code and change all
double quotes (") for single quotes (') and all single quotes (') with slash
quote (\'). Once I did this everything worked correctly.

I noticed this because you are doing it with intermingled double and single
quotes. Try this and see if it helps. I'll be offline till the 27th.

--
Martin Duty
IT Manager
Underground Vaults & Storage, Inc.
TD 3.0 PFT2
Client Win 2K/XP
Database MS Sql Server 2000

Remi

A good One... Need a lot of help !!! OdrPrepareProc

Post by Remi » 22 Dec 2004, 15:50

 Posted by:  Remi 

Thank you Martin....
i'll try this new point...
Do you know is that gonna happen in SQL 2000??

happy holidays..
Remi

Martin Duty

A good One... Need a lot of help !!! OdrPrepareProc

Post by Martin Duty » 27 Dec 2004, 15:37

 Posted by:  Martin Duty 

In 2000 came performance problems. I do not remember what the problem was (I
think you can search through the forum and find it). But basically SQL 2K
doesn't like numeric bind variables in the where clause.

To fix this I changed all code so all bind variables (no matter what type)
are concatenated into the sql statement. Now this might not be needed, and
could cause other problems depending on what you are doing. but for me it
works fine. Perhaps some of the guru's here could be more precise on this.
shrug

--
Martin Duty
IT Manager
Underground Vaults & Storage, Inc.
TD 3.0 PFT2
Client Win 2K/XP
Database MS Sql Server 2000

Return to “advanced.programming”

Who is online

Users browsing this forum: [Ccbot] and 0 guests