sql api. получение значения возвращаемое по return

[remedius]

Доброго времени суток!
Если кто имел дело с sql api, есть ли возможность получить значение, возвращаемое из хранимой процедуры по return?
Если да, то каким образом это можно сделать?
2.Можно ли передать аргументы для хранимой процедуры в строке setCommandText?(у меня что-то не получается .Приходится устнавливать вручную через CreateParam.
Спасибо.

[Oldy]

select mystoredproc(par1, par2 ...) - не работает?

[remedius]

т.е. вместо  pCmd.setCommandText("sp_alter_table",SA_CmdStoredProc ); указывать что-то типа
("select mystoredproc(par1, par2 ...)",...); ?

[Oldy]

Ну да,  разве иначе можно получить возвращаемые данные? Потом ALTER TABLE - DDL команда, а я понял из вопроса, что нужна DML. Т.е. нужно получить данные, а не изменять структуру таблицы.

[remedius]

   pCmd.setCommandText("SELECT sp_create_spacial_index (:1)",SA_CmdSQLStmt);
   pCmd.Param(1).setAsString() = sTblName;
Вот так не работает:
'sp_create_spacial_index' is not a recognized function name.

[Dimka]

А что за язык программирования используется и СУБД?

Собрал тут коротенький пример: серверная часть на SQL Server 2000 (язык T-SQL) и клиент WSH 5.6 (язык VBScript + библиотека ADO 2.8 ).

В начале создаём экспериментальную БД:

Код: (Text)

EXEC sp_addlogin
  @loginame = 'TestLogin',
  @passwd   = 'TestPassword'

CREATE DATABASE Test
GO

USE Test
GO

EXEC sp_adduser
  @loginame   = 'TestLogin',
  @name_in_db = 'TestUser'

CREATE PROCEDURE spTest
  @Param VARCHAR(10)
AS
  IF @Param = 'Zero'
    RETURN 0
  ELSE
    RETURN 1
GO

GRANT EXECUTE ON spTest TO TestUser

Затем пишем клиента:

Код: (Text)

' Файл test.vbs
On Error Resume Next

Dim Connection

' Открываем
Set Connection = CreateObject("ADODB.Connection")
Connection.ConnectionString = _
        "Provider=SQLOLEDB.1;" & _             
        "User ID=TestLogin;" & _               
        "Password=TestPassword;" & _
        "Data Source=TESTSERVER;" & _          
        "Initial Catalog=Test;"
Call Connection.Open
If Err.number <> 0 Then
        Call WScript.Echo("Отсутствие соединения: " & Err.Description)
        Stop
End If

' Тестируем
Call WScript.Echo("Проверка с Zero    - возвращённое значение: " & CommandExecute(Connection, "Zero"))
Call WScript.Echo("Проверка с Nonzero - возвращённое значение: " & CommandExecute(Connection, "Nonzero"))

' Закрываем
Call Connection.Close

Set Connection = Nothing


' Тестовая функция
Function CommandExecute(Connection, Param)
        Dim Command, I, Value

        ' Настраиваем
        Set Command = CreateObject("ADODB.Command")
        Set Command.ActiveConnection = Connection
        Command.CommandType = 4 ' adCmdStoredProc = 4
        Command.CommandText = "spTest"
        ' Второй параметр: adInteger    = 3, adVarChar          = 200
        ' Третий параметр: adParamInput = 1, adParamReturnValue = 4
        Call Command.Parameters.Append(Command.CreateParameter(, 3, 4))
        Call Command.Parameters.Append(Command.CreateParameter("@Param", 200, 1, 10, Param))

        ' Обращаемся к серверу
        Call Command.Execute   
        If Err.number <> 0 Then
                Call WScript.Echo("Ошибка исполнения команды: " & Err.Description)
                Call Err.Clear
                CommandExecute = "Ошибка"
                Exit Function
        End If 
       
        ' Обрабатываем параметры и вычисляем результат
        Call Command.Parameters.Refresh
        If IsEmpty(Command.Parameters(0).Value) Then
                Value = "Empty"
        Else
                Value = Command.Parameters(0).Value
        End If
        CommandExecute = Command.Parameters(0).Name & " = " & Value
       
        Set Command = Nothing
End Function

Откуда следует, что для получения возврщаемого хранимой процедурой результата надлежит добавить в коллекцию параметров на первое место целочисленный параметр типа adParamReturnValue (числовое значение 4). После выполнения команды коллекцию параметров нужно обновить и прочитать значение введённого параметра.

Проверяем работу:

Код:

C:\>cscript test.vbs
Сервер сценариев Windows (Microsoft R) версия 5.6
c Корпорация Майкрософт (Microsoft Corp.), 1996-2001. Все права защищены.

Проверка с Zero    - возвращённое значение: @RETURN_VALUE = 0
Проверка с Nonzero - возвращённое значение: @RETURN_VALUE = 1

Работает!

Удаляем за собой на сервере:

Код: (Text)

USE master
GO

DROP DATABASE Test

EXEC sp_droplogin
  @loginame = 'TestUser'

P.S. Естественно, речь идёт о SQL Server, а не о произвольной СУБД.

[remedius]

dimka,  Извиняюсь - не поняла про что Вы написали. Используется sql api библиотека

[Dimka]

есть ли возможность получить значение, возвращаемое из хранимой процедуры по return?
Если да, то каким образом это можно сделать?

Повторяю: завести параметр типа ReturnValue, целочисленный; поместить его первым в коллекции параметров.

Не работает?

[remedius]

все, поняла. Проверю.
P.S. вообще-то заводила, но не додумалась его первым заводить:(
Спасибо

[Dimka]

Ещё, кстати, полезно обратить внимание на такой момент: http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q194792
Хотя, наверно, в последнее время это уже не так актуально.

[remedius]

Вот работающий вариант.
pCmd.setCommandText("sp_create_spacial_index",SA_CmdStoredProc )
pCmd.CreateParam("",SA_dtLong, -1, 4,0,0,SA_ParamReturn);   
T = ExecuteCommand(&pCmd);
printf("Result: %i\n",pCmd.ParamByIndex(0).asLong());

 Спасибо dimka!

Получается еще, что нельзя прямо в setCommandText (типа "sp_create_spacial_index :1")указывать параметры, как в запросе - а жаль.

[Dimka]

Получается еще, что нельзя прямо в setCommandText (типа "sp_create_spacial_index :1")указывать параметры, как в запросе - а жаль.

Почему это нельзя? Нужно только тип команды использовать CmdText, а не CmdStoredProc

В случае явного задания параметров будет Command.CommandText = "EXEC spTest @Param = 'Zero'". Недостаток заключается в том, что значения параметров нужно задавать прямо в строке, а не в коллекции параметров.
В случае неявного задания параметров будет Command.CommandText = "EXEC spTest 'Zero'". Недостаток тот же + необходимо строго соблюдать порядок следования параметров.
В случае использования коллекции параметров можно использовать символы-заменители Command.CommandText = "EXEC spTest ?" и Command.Parameters(0).Value = "Zero". Недостаток в строгом порядке следования параметров.

Способ обращения к SQL Server значения не имеет: это может быть и SQL API, и библиотеки вроде ADO или ADO.NET, и HTTP-запросы через ISAPI-расширение IIS и т.д., и т.п. Суть и правила работы от этого не меняются. Разница только в конкретных названиях объектов, методов, свойств и констант и синтаксисе используемого языка программирования.

[remedius]

dimka, Да вот так работает: pCmd.setCommandText("EXEC sp_create_spacial_index :1, :2 ",SA_CmdSQLStmt), но это в случае, гогда не указываешь параметр возврата. Что-то типа "SET :1 =EXECT (SELECT) sp_create_spacial_index :2, :3"  - ошибка.

[Dimka]

Что-то типа "SET :1 =EXECT (SELECT) sp_create_spacial_index :2, :3"  - ошибка.

Да, так не работает. Вообще говоря, правильный синтаксис тогда уж: "EXEC :1 = sp_create_spacial_index :2, :3" (см BOL).

[remedius]

Вы наверняка правы с синтаксисом, но так тоже не работает:
Multiple-step OLE DB operation generated errors. Check each OLE DB status value,
 if available. No work was done.

[Dimka]

но так тоже не работает:

Не могу с этим согласиться, ибо факты говорят об обратном.

Взята учебная база данных Northwind из поставки SQL Server, в ней взята первая попавшаяся хранимая процедура. Клиент написан на VBA в виде макроса к MS Word с подключением к макросу библиотеки ADO 2.8.

Код: (Text)

Sub Test()
    ' Using Microsoft ActiveX Data Objects 2.8 Library
   
    On Error Resume Next
   
    Dim Connection As New ADODB.Connection
    With Connection
        .ConnectionString = _
            "Provider=SQLOLEDB.1;" & _
            "Integrated Security=SSPI;" & _
            "Data Source=TESTSERVER;" & _
            "Initial Catalog=Northwind;"
        Call .Open
    End With
    With Err
        If .Number <> 0 Then
            Call MsgBox("Connection error: " & .Description)
            Call .Clear
            Exit Sub
        End If
    End With
   
    Dim Command As New ADODB.Command
    With Command
        Set .ActiveConnection = Connection
        .CommandType = adCmdText
        .CommandText = "EXEC ? = CustOrderHist ?"
        With .Parameters(0)
            .Direction = adParamReturnValue
            .Type = adInteger
            .Value = -1 ' Other than 0, stored procedure must return 0.
        End With
        With .Parameters(1)
            .Direction = adParamInput
            .Type = adWChar
            .Size = 5
            .Value = "ALFKI"
        End With
    End With
   
    Dim Recordset As ADODB.Recordset
    Set Recordset = Command.Execute
    With Err
        If .Number <> 0 Then
            Call MsgBox("Execution error: " & Err.Description)
            Call .Clear
            Exit Sub
        End If
    End With
   
    Dim Message As String
    Message = ""
   
    ' Data processing
    Dim Count As Integer
    Count = 0
    While Not Recordset.EOF
        Count = Count + 1
        Call Recordset.MoveNext
    Wend
    Message = Message & "Has processed " & CStr(Count) & " records." & Chr(13)
   
    Call Recordset.Close
    Set Recordset = Nothing
   
    Call Command.Parameters.Refresh
    With Command.Parameters(0)
        Message = Message & "Result is " & .Name & " = " & CStr(.Value)
    End With
   
    Set Command = Nothing
   
    Call Connection.Close
    Set Connection = Nothing
   
    Call MsgBox(Message)
End Sub

Результат работы:

Код:

---------------------------
Microsoft Word
---------------------------
Has processed 11 records.
Result is @RETURN_VALUE = 0
---------------------------
ОК   
---------------------------

Результат работы хранимой процедуры получен. Результат верный - 0, хотя предварительно параметр был установлен в -1. Кроме того, всё же работает та синтаксическая конструкция, которая, по вашим словам, не работает.

Ошибка "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.", видимо, имеет номер 80004005 - "Подключение занято выдачей результатов для другой команды" и связана с тем, что вы пытались получить результат, предварительно не закрыв полученный Recordset. Обратите внимание на порядок выполняемых вами действий, ибо работа прикладного программиста сродни работе сапёра .

[remedius]

dimka,  Честно говоря, не совсем понимаю о каком RecordSet Вы говорите:
Вот так работает:
pCmd.setCommandText("EXEC sp_create_spacial_index :1, :2 ",SA_CmdSQLStmt);
pCmd.Param(1).setAsString() = sTblName;
pCmd.Param(2).setAsNull();
ExecuteCommand(&pCmd);
pCmd.DestroyParams();

Вот так нет:
pCmd.setCommandText("EXEC :1 = sp_create_spacial_index :2, :3 ",SA_CmdSQLStmt);
pCmd.Param(1).setAsLong();
pCmd.Param(2).setAsString() = sTblName;
pCmd.Param(3).setAsNull();
ExecuteCommand(&pCmd);
pCmd.DestroyParams();

По моему здесь опять дело в синтаксисе:(

[Dimka]

По моему здесь опять дело в синтаксисе:(

Не думаю.

Код:

pCmd.Param(1).setAsLong();

Тип данных параметра вы задали - хорошо. А где тип самого параметра? По умолчанию параметр рассматривается как Input, а вам надо ReturnValue. Где эта настройка? Не вижу.

[remedius]

На самом деле я об этом тоже думала, и даже пробовала:)
pCmd.Param(1).setParamDirType(SA_ParamReturn);
Но ошибка менялась на "Unknown Data Type"

[remedius]

Оказалось дело вот в чем:
pCmd.Param(1).setParamType(SA_dtLong);
Вот так все ок. А вот так:
pCmd.Param(1).setAsLong();  - возникают проблемы в данном случае.

[remedius]

Все больше и больше я начинаю понимать - НАДО ЧИТАТЬ МАНУАЛ!

Спасибо