Stored Procedures, Sybase

[wingless]

Hi I've developped an SP in Sybase and tested it on the console, therefore I know it works. However when calling it from my script I just can't seem to send the parameter values.

This is my code:

Public Function queryModify(ByVal storedProcedure As String, ByVal parameters(,) As String) As Integer
        MyOdbcCommand = New OdbcCommand(storedProcedure, MyOdbcConnexion)
        MyOdbcCommand.CommandType = CommandType.StoredProcedure
        Dim height, i As Integer
        height = parameters.GetLength(1)
        For i = 0 To height
            MyOdbcCommand.Parameters.AddWithValue(parameters(i, 0), parameters(i, 1))
        Next
        Return MyOdbcCommand.ExecuteNonQuery
End Function

Dim query(2, 1) As String

query(0, 0) = "@Valeur"
query(1, 0) = "@Class"

query(0, 1) = "Value1"
query(1, 1) = "Value2"

queryModify("MyStoredProcedure", query)

And this is the exception I get:

System.Data.Odbc.OdbcException: ERROR [HY000] [DataDirect][ODBC Sybase Wire Protocol driver][SQL Server]Procedure SW_P_CreateIssuesOTC expects parameter @Valeur, which was not supplied.

ERROR [HY000] [DataDirect][ODBC Sybase Wire Protocol driver][SQL Server]Procedure SW_P_CreateIssuesOTC expects parameter @Class, which was not supplied.

   à System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)
   à System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod)
   à System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader)
   à System.Data.Odbc.OdbcCommand.ExecuteNonQuery()
   à SQLCommand.SQLOdbcCommand.queryModify(String storedProcedure, String[,] parameters) in ...

 

[cjard]

Er. Your code or your thinking is very bizarre..


Set the command up ONCE:

Class DBAccess
  Private SP_NAMECommand as OdbcCommand

  Public Sub New()
    SP_NAMECommand = New OdbcCommand(STORED_PROC_NAME, CONNECTION_GOES_HERE)
    SP_NAMECommand.Parameters.Add("@Valeur", WHATEVER_DB_TYPE)
    SP_NAMECommand.Parameters.Add("@Class", WHATEVER_DB_TYPE)
  End Sub

  Public Sub RunSP_NAME(valeur as WHATEVER_CLR_TYPE, class as WHATEVER_CLR_TYPE)
    SP_NAMECommand.Parameters("@Valeur").Value = valeur
    SP_NAMECommand.Parameters("@Class").Value = class

    Dim openedByMe as Boolean = False
    If SP_NAMECommand.Connection.State And ConnectionState.Open <> ConnectionState.Open Then 
      SP_NAMECommand.Connection.Open
      openedByMe = True
    EndIf

    SP_NAMECommand.ExecuteNonQuery()

    If openedByMe Then SP_NAMECommand.Connection.Close()

  End Sub

Make one instance of the class, the connection is inited once. If it isnt open then it is opened used and closed. If it IS open (maybe you are doing a transaction?) it is left alone

Do the init ONCE

Then repeatedly call it


DONT keep repeatedly adding parameters to your command every time you run it! Change the existing values...

 

[wingless]

Yeah I know I'm bizarre...can't help it

Anyways thanks for answering my post. I tried your code and now I get this exception :

System.Data.Odbc.OdbcException: ERROR [HY000] [DataDirect][ODBC Sybase Wire Protocol driver][SQL Server]Procedure SW_P_CreateIssuesOTC expects parameter @Valeur, which was not supplied.

ERROR [HY000] [DataDirect][ODBC Sybase Wire Protocol driver][SQL Server]Procedure SW_P_CreateIssuesOTC expects parameter @Class, which was not supplied.

   à System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)
   à System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod)
   à System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader)
   à System.Data.Odbc.OdbcCommand.ExecuteNonQuery()
   à SQLCommand.SQLOdbcCommand.queryModify(OdbcCommand myOdbcCommand) dans...

Do you think it has to do with the Sybase itself?

 

[cjard]

I tried your code

Posting it might help

 

[wingless]

Sorry my bad

Here it is:

Public Class SQLClass
  Private connex As OdbcConnection
  Private sqlCommand As OdbcCommand

  Public Sub New()
    Me.sqlCommand = New OdbcCommand()
    Me.connex = New OdbcConnection()
  End Sub

  Public Function open(ByVal MyConnectString As String)
    Dim openedByMe as Boolean = False
    If SP_NAMECommand.Connection.State And ConnectionState.Open <> ConnectionState.Open Then 
      Me.connex.ConnectionString = MyConnectString
      Me.connex.Open()
      openedByMe = True
    End If
    Return openedByMe
  End Function

  Public Sub close()
    Me.connex.close()
  End Sub

  Public Function queryStoredProc(ByVal MyOdbcCommand As OdbcCommand) As Integer
    Me.sqlCommand = MyOdbcCommand
    Me.sqlCommand.Connection = Me.connex
    Return sqlCommand.ExecuteNonQuery
  End Function
End Class

Imports SQLClass.dll

Public Class MyForm
  Private spCommand As New OdbcCommand()
  Private sql As New SQLClass()

  Private Sub MyButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyButton.Click
    spCommand.Parameters.Clear() 'I have various buttons who call different SP with different parameters so I clear them before.
    Dim spCommand.CommandText = "MySP"
    spCommand.Parameters.Add("@Valeur", OdbcType.VarChar)
    spCommand.Parameters.Add("@Class", OdbcType.VarChar)

    spCommand.Parameters("@Valeur").Value = myValeur
    spCommand.Parameters("@Class").Value = myClass

    If sql.open(MyConnectionString) Then
      Try
        sql.queryModify(storedProc)
      Catch ex As Exception
         MessageBox.Show(ex.ToString)
      End Try
      sql.close()
    End If
  End Sub
End Class

 

[cjard]

Your code is a bit confused still:


spCommand.Parameters.Add("@Valeur", OdbcType.VarChar)
storedProc.Parameters.Add("@Class", OdbcType.VarChar)

What?


Make one command for EACH storedproc you have; dont keep reassinging connection strings, and stored procedure names, and clearing and adding parameters all the time.. just do it once, then keep calling them with new values:

Module

  myInsertSP as Command...
  myUpdateSP as Command...
  myDeleteSP as Command...
  
  Sub New()
    'setup all 3 comamnds
  End Sub

  Sub RunInsert(param1, param2)
    myInsert.Params("param1").Value = param1
    myInsert.Params("param2").Value = param2
    execute command
  End Sub

  Sub RunUpdate(param1, param2)
    myUpdate.Params("param1").Value = param1
    myUpdate.Params("param2").Value = param2
    execute command
  End Sub

and so on..

 

[wingless]

I had just mixed up my variable names. I understand what you are trying to tell and I will apply it to my code, however the fact is that it doesnt work with or without a proper code structure.

I keep getting the exception that tells me its expecting the parameters, even though I declared them like your code suggested.

 

[cjard]

Check that SyBase understands parameters in @ format, and check that it expects the @ as part of the name

I use oracle and its parameters look like ARAMETER, but it doesnt (in my experience) expect the leading colon (I actually suspect that it doesnt care)

Try adding your parameters without the @ in the command.Parameters.Add("Valeur"...) call