VB and Stored Procedures

I know about the date issue's, I am the DBA for a system that was converted
from SQL to UDB, the developers had a good time. But they don't use any
Stored Procs, so I though I'd play with one of my DBs at home, upgraded from
SQL 7 to 8 in a day, even spent a few more days moving it more towards fully
normalized. But it uses Stored Proces everywhere, almost completely, so
moving to UDB has been interesting. With this current problem I don't even
know where to look.

I'm running the latest and greatest with 7.1 FP 3, which equates to &.2,
although I had the same problem at 7.1 FP 2.

KlK, MCSE

Greg Nash <gnash@namoicotton.com.au> wrote:
>Hi,
>
>Also be careful with timestamps / dates / times.
>What fixpack level are you on?
>
>--Greg
>
>kevin Knudson wrote:
>
>> Well as I work through converting my SQL DB to UDB I continue to have
Stored
>> Procedure conversion problems. Several worked fine once I got the stored
>> procedure builder working.
>>
>> The next procedure, the core on in the system that Inserts all of the
main
>> data, with a long list of parameters / fields (40).
>>
>> The first problem I had was SQL0171N parm nn invalid, apparently you can't
>> use SMALLINT through VB. Once I eliminated them I was able to move on
to
>> the next problem.
>>
>> SQL0804N, The application program input parameters for the current request
>> are not valid. Reason code "". If a host variable or SQLVAR in the SQLDA
>> is invalid then: host variable/SQLVAR number = "",SQLTYPE="",SQLLEN="",?
>>
>> This is exactly how it is issued, with any clues missing.
>>
>> One very interesting note, if I cut and paste the CALL ... into the Command
>> Window, it works fine. With the SmallInt, or the current one.
>>
>> Any suggestions.
>

Sounds like a plan, I was thinking about the decimals too, I think it's just
some disagreement between VB and OLEDB or OLEDB And UDB I'll solve it, it's
just not a high priority item. The big problem is I've been moving ahead
with the SQL version of the DB, so I need to not only get the UDB one working,
but keep upgrading the structures.

My goal is to build the 2, and bang on them, maybe not a scientific study,
but apples to apples (bad analogy I know, but) a little more real world than
these white paper example from MS / IBM with 8 . 16 .. 32 processors, 32
gig of memory (geesh we have a big 390 here with something like 9 gig).
I also want to play with some tuning tricks.

I also don't spend as much time on the computers in the summer.

I'lk keep ya posted.

KlK, MCSE

Greg Nash <gnash@namoicotton.com.au> wrote:
>Hi Kevin,
>
>Reading your message again, I remember having the same error sometime.
It took a
>bit of pinning down, but related to the parameter types. I think I ended
up not
>using fixed decimals, and passing dates/times as strings. Since then I
believe
>there's been some improvement in handling of numeric types.
>It's a bit of mucking around, but I'd suggest building a version of the
SP with
>all the code removed except a dummy return code, and cull the parameters,
adding
>them back in a few at a time to see if it's a particular parameter type
that is
>causing the failure.
>
>--Greg
>
>kevin knudson wrote:
>
>> I know about the date issue's, I am the DBA for a system that was converted
>> from SQL to UDB, the developers had a good time. But they don't use any
>> Stored Procs, so I though I'd play with one of my DBs at home, upgraded
from
>> SQL 7 to 8 in a day, even spent a few more days moving it more towards
fully
>> normalized. But it uses Stored Proces everywhere, almost completely,
so
>> moving to UDB has been interesting. With this current problem I don't
even
>> know where to look.
>>
>> I'm running the latest and greatest with 7.1 FP 3, which equates to &.2,
>> although I had the same problem at 7.1 FP 2.
>>
>> KlK, MCSE
>>
>> Greg Nash <gnash@namoicotton.com.au> wrote:
>> >Hi,
>> >
>> >Also be careful with timestamps / dates / times.
>> >What fixpack level are you on?
>> >
>> >--Greg
>> >
>> >kevin Knudson wrote:
>> >
>> >> Well as I work through converting my SQL DB to UDB I continue to have
>> Stored
>> >> Procedure conversion problems. Several worked fine once I got the
stored
>> >> procedure builder working.
>> >>
>> >> The next procedure, the core on in the system that Inserts all of the
>> main
>> >> data, with a long list of parameters / fields (40).
>> >>
>> >> The first problem I had was SQL0171N parm nn invalid, apparently you
can't
>> >> use SMALLINT through VB. Once I eliminated them I was able to move
on
>> to
>> >> the next problem.
>> >>
>> >> SQL0804N, The application program input parameters for the current
request
>> >> are not valid. Reason code "". If a host variable or SQLVAR in the
SQLDA
>> >> is invalid then: host variable/SQLVAR number = "",SQLTYPE="",SQLLEN="",?
>> >>
>> >> This is exactly how it is issued, with any clues missing.
>> >>
>> >> One very interesting note, if I cut and paste the CALL ... into the
Command
>> >> Window, it works fine. With the SmallInt, or the current one.
>> >>
>> >> Any suggestions.
>> >
>