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

[Remi]

 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

Code: Select all

!!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 trying everything and i discovered that the problem is generated by
the variable :sIdMan char(4)
i send the instruction

Code: Select all

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

and was working perfectly....

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

Greetings

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

Code: Select all

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]

 Posted by:  Remi 

another test....

Code: Select all

!!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]

 Posted by:  Martin Duty 

How about trying:

Code: Select all

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]

 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
------------------------------------------------

[Remi]

 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....

Code: Select all

!!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]

 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]

 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]

 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