Aleksey,
>Can you be more specific?
I'm not sure how to describe this other than the examples below. Basically, VFP 8 using MS SQL Server works, VFP 8 using Sybase doesn't. I have to assume that the behind-the-scenes way VFP parses the SQL string and passes that to the ODBC driver isn't compatable with the Sybase drivers I've tested. There could be a setting in Sybase to start this working, but I don't know them. Short story: Just use MS SQL Server<g>. It works better than Sybase in other ways as well.
>What VFP version are you using?
8.0
>How exactly SQLEXEC fails?
When it fails, I get a failure result code and the parameter is not updated. The exact failure depends on the way the output param is constructed. For example here are a few permutations and error messages:
?@NewKey output --> Connectivity error: [INTERSOLV][ODBC SQL Server driver]Character, decimal, and binary parameters cannot have a precision of zero. Error in parameter 1.
@NewKey output --> Connectivity error: [INTERSOLV][ODBC SQL Server driver][SQL Server]Must declare variable '@NewKey'.
@NewKey --> Connectivity error: [INTERSOLV][ODBC SQL Server driver][SQL Server]Must declare variable '@NewKey'.
NewKey output --> Connectivity error: [INTERSOLV][ODBC SQL Server driver][SQL Server]Can't use the OUTPUT option when passing a constant to a stored procedure.
Here is how I get around it now:
*-- Build a SQL statement that declares a variable, calls the proc and
*-- SELECTs the variable in order to produce a result set I can read.
cSQL=[declare @cNextValue char(12)]+CHR(13)+CHR(10)+;
[exec GetNextID 'KeyFieldName', @cNextValue output]+CHR(13)+CHR(10)+;
[select @cNextValue]
SQLExec(nConnection, cSQL, 'curResults')
cNewKey = curResults.exp
Works like a champ, but I'll probably explore the direct ODBC API calls just to get the experiance.
Gary
>>I need to make calls to an ODBC driver directly. I assume after using VFP's SQLConnect() and SQLGetProp() to get a connection or statement handle, I call SQLExecDirect() and possibly related functions to call a stored proc on the back end, bypassing VFP's SQLExec() function.
>>
>>Can anyone point me to a VFP example? MSDN has the function calls documented, so I can figure out the individual calls, but I didn't see an example of the calls working together.
>>
>>The reason for doing something the hard way:
>>The use of VFP as a major player for my company's application development hangs on this. The problem is that VFP's SQLExec() fails to get a value from an OUTPUT parameter passed to a stored proc in Sybase. Works like a champ using MS SQL Server, but not with Sybase, I want to try directly, in case the VFP "wrapper" is clashing with the Sybase driver.
>>
>>This works fine using MS SQLServer:
>>cOutParam = ''
>>SQLExec(nConHand,[exec MySp 'SomeValue', ?@cOutParam])
>>? cOutParam && Changed value by the sp
>>
>>Sybase:
>>SQLExec(nConHand,[exec MySp 'SomeValue', ?@cOutParam]) && Fails
>>SQLExec(nConHand,[exec MySp 'SomeValue', @cOutParam]) && Fails
>>SQLExec(nConHand,[exec MySp 'SomeValue', @cOutParam OUTPUT]) && Fails
>>SQLExec(nConHand,[exec MySp 'SomeValue', @cOutParam OUTPUT],'MyCursor')&& Fails
>>
>>SQLExec(nConHand,[exec MySp 'SomeValue', cOutParam]) && Succeeds, sort of
>>
>>The last call succeeds, but is worthless since cOutParam was passed by val and is unchanged by the procedure.
>>
>>
>>I know I can have the sp "SELECT cOutParam = new value" at the end of the proc and look at the result set to get a value, but that's unacceptable. OK, perhaps, for new sp's, but unacceptable for the many existing sp's using OUTPUT parameters.
>>
>>Gary
>
>Hi Gary,
>
>Can you be more specific?
>What VFP version are you using?
>How exactly SQLEXEC fails?
>
>The repro code that creates procedure on the fly and then tries to call it is the best.
>
>Thanks,
>Aleksey.
Précédent
Suivant
Répondre
Voir le fil de ce thread
Voir le fil de ce thread à partir de ce message seulement
Voir tous les messages de ce thread
Voir tous les messages de ce thread à partir de ce message seulement