Call a VB Function from EffiProz Database

Dec 10, 2010 at 5:45 PM

Hi:

I tried the next code successfully in C# http://blog.effiproz.com/2010/03/clr-functions-with-effiproz-database.html but i have tried it in VB and encounter some problems because:

- In VB, the EXTERNAL NAME gave me the next error "user lacks privilege or object not found Exception of type 'EffiProz.Core.CoreException' was thrown. EffiProz.CLFunctionSample in statement [CREATE FUNCTION add_num(x INT, y INT)  RETURNS INT  NO SQL  LANGUAGE DOTNET  EXTERNAL NAME 'EffiProz.CLFunctionSample.Add']" and i have a dll with that name in the same folder where the application resides.

My question is, why is not working with my dll? or what i'm missing in the syntax? or how is it done in EffiProz?

Our objective is to call a VB function in the dll, using a Store Procedure made in  EffiProz.

The internet examples, don't show very practical uses like fisical files or dll, instead they show memory files and console applications.

Thanks in advance.

Coordinator
Dec 11, 2010 at 1:22 AM

 Hi, just adding the dll to the project is not enough, your application need to load the dll.

-thanks

Dec 13, 2010 at 3:04 PM

hi igloobone:

That's exactly what i'm asking my friend... how can i do the load, because when i try "CREATE FUNCTION...." that previous error in the post before, give me the impression that i miss something; but i don't know how to do the load in the EffiProz syntax.

Thanks in advance.

Coordinator
Dec 13, 2010 at 3:10 PM

Hi, there is no EffiProz command to load an assembly. You need to load all relevant assemblies in your application code before calling any CLR routines.

-thanks

Coordinator
Dec 13, 2010 at 4:07 PM

Check the sample included with 1.5 release http://www.effiproz.com/downloads/effiproz_db_1_5_3999_36543.zip

Dec 13, 2010 at 7:47 PM
Hi igloobone:
I'm sending you he code i used to test the link that you gave me in the previous post. First, i send you the code of the windows application, and second the dll i used as reference. All this like you see is made in VB, but still in the section 'soo far, soo good' the code break up with an error. One button creates the table and the record to test, and the second is the test itself, sorry for bother you but if you can run this code and tell me what's wrong, i'll be pleased. thank you in advance.
Option Explicit On
Option Strict On
Imports System.Reflection
Public Class text
    Private _conex As DbConnection
    Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Try
            _conex = New EfzConnection("Connection Type=File ; Initial Catalog=D:/CLRFunctionSample(VB)/db; User=sa; Password=;")
            _conex.Open()
        Catch ex As Exception
            MessageBox.Show(ex.Message, String.Empty, MessageBoxButtons.OK, MessageBoxIcon.Hand, MessageBoxDefaultButton.Button1)
        End Try
    End Sub
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Try
            'loading assembly.
            Dim ass As Assembly = Assembly.Load("JLSistemas.PDA.CLR")
            Dim cmd As DbCommand = New EfzCommand
            cmd.CommandType = CommandType.Text
            cmd.Connection = _conex
            cmd.CommandText = "CREATE FUNCTION add_num(x INT,  y INT)" & Environment.NewLine & "RETURNS INT" & Environment.NewLine & "NO SQL" & Environment.NewLine & "LANGUAGE DOTNET" & Environment.NewLine & "EXTERNAL NAME 'JLSistemas.PDA.CLR.Add';"
            'soo far, soo good... but when i execute the prevoius sentence, occurs the error. why?
            cmd.ExecuteNonQuery()
            cmd.CommandText = "SELECT add_num(3,4) from dual;"
            Label1.Text = Convert.ToString(cmd.ExecuteScalar)
        Catch ex As Exception
            MessageBox.Show(ex.Message, String.Empty, MessageBoxButtons.OK, MessageBoxIcon.Hand, MessageBoxDefaultButton.Button1)
        End Try
    End Sub
    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Try
            Dim cmd As DbCommand = New EfzCommand
            cmd.CommandType = CommandType.Text
            cmd.Connection = _conex
            cmd.CommandText = "CREATE CACHED TABLE TEST(X INT, Y INT);"
            cmd.ExecuteNonQuery()
            cmd.CommandText = "INSERT INTO TEST(X, Y) VALUES(4, 3);"
            cmd.ExecuteNonQuery()
            cmd.CommandText = "COMMIT"
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            MessageBox.Show(ex.Message & Environment.NewLine & Environment.NewLine & "I TOLD YOU.. JUST CLICK ONCE! ¬¬", String.Empty, MessageBoxButtons.OK, MessageBoxIcon.Hand, MessageBoxDefaultButton.Button1)
        End Try
    End Sub
End Class

Namespace

JLSistemas.PDA

 

Public Class CLR

 

Public Shared Function Add(ByVal x As Int32, ByVal y As Int32) As Int32

 

Return x + y

 

End Function

 

End Class

End

Namespace

Coordinator
Dec 14, 2010 at 12:06 AM

hi colomaj, what is the error you get? does c# sample works?

Dec 14, 2010 at 12:06 PM

Hi:

Yes igloobone, the example in C# works fine, besides it's not the same scenario for my case. The example in C# use a Console Application and a type=Memory file; and I use a Windows Application and a type=File, i don't know if that matters.

The error that occurs when i try the "CREATE FUNCTION.... is:

SOURCE: EffiProz.CF

MESSAGE: user lacks privilege or object not found Exception of type 'EffiProz.Core.CoreException' was thrown. JLSistemas.PDA.CLR in statement [CREATE FUNCTION add_num(x INT,  y INT)
RETURNS INT
NO SQL
LANGUAGE DOTNET
EXTERNAL NAME 'JLSistemas.PDA.CLR.Add']

STACKTRACE:   at EffiProz.Core.Routine.GetMethods(String name)
   at EffiProz.Core.Routine.GetMethod(String name, Routine routine, Boolean[] hasConnection, Boolean returnsTable)
   at EffiProz.Core.Routine.Resolve(Session session)
   at EffiProz.Core.Statements.StatementSchema.ProcessCreateRoutine(Session session, SchemaManager schemaManager)
   at EffiProz.Core.Statements.StatementSchema.GetResult(Session session)

For your information, i have the dll reference in my application, and the sentence that load the assembly, soo there would be a chance that i miss a thing?.

Thanks in advance.

Coordinator
Dec 14, 2010 at 12:44 PM

Hi colomaj,

Can you try with a C# Windows Application with connection type=File. So two scenarios will be same except the language.

-thanks

Dec 14, 2010 at 2:11 PM

Hi igloobone:

I made an exact Windows Application in C#, and i obtained the same error i post you before. If i can give you something else to figure it out what is happening, please let me know.

Thanks in advance.

Coordinator
Dec 14, 2010 at 2:24 PM

good evening, can email c# project to support@effiproz.com? thanks colomaj.

Dec 14, 2010 at 2:25 PM
hi again Igloobone:
i send you the code i test in C#, in case this will be of help for you.
using System;
using System.Data;
using System.Data.Common;
using System.Data.EffiProz;
using System.Reflection;
using System.Windows.Forms;

namespace CLRFunctionSample_CSharp_
{
    public partial class Form1 : Form
    {
        private DbConnection _conex;
        public Form1()
        {
            try
            {
                _conex = new EfzConnection("Connection Type=File ; Initial Catalog=D:/CLRFunctionSample(CSharp)/db; User=sa; Password=;");
                _conex.Open();
                InitializeComponent();
            }
            catch(Exception ex)
            {
                MessageBox.Show(ex.Message, String.Empty, MessageBoxButtons.OK, MessageBoxIcon.Hand, MessageBoxDefaultButton.Button1);
            }
        }
        private void Button2_Click(object sender, EventArgs e)
        {
            try
            {
                DbCommand cmd = new EfzCommand();
                cmd.CommandType = CommandType.Text;
                cmd.Connection = _conex;
                cmd.CommandText = "CREATE CACHED TABLE TEST(X INT, Y INT);";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "INSERT INTO TEST(X, Y) VALUES(4, 3);";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "COMMIT;";
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + Environment.NewLine + Environment.NewLine + "I TOLD YOU.. JUST CLICK ONCE! ¬¬", String.Empty, MessageBoxButtons.OK, MessageBoxIcon.Hand, MessageBoxDefaultButton.Button1);
            }
        }
        private void Button1_Click(object sender, EventArgs e)
        {
            try
            {
                //loading the assembly.
                Assembly ass = Assembly.Load("JLSistemas.PDA.CLR");
                DbCommand cmd = new EfzCommand();
                cmd.CommandType = CommandType.Text;
                cmd.Connection = _conex;
                cmd.CommandText = "CREATE FUNCTION add_num(x INT,  y INT)" + Environment.NewLine + "RETURNS INT" + Environment.NewLine + "NO SQL" + Environment.NewLine + "LANGUAGE DOTNET" + Environment.NewLine + "EXTERNAL NAME 'JLSistemas.PDA.CLR.Add';";
                //soo far, soo good... but when i execute the prevoius sentence, occurs the error. why?
                cmd.ExecuteNonQuery();
                cmd.CommandText = "SELECT add_num(3,4) from dual;";
                Label1.Text = Convert.ToString(cmd.ExecuteScalar());
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, String.Empty, MessageBoxButtons.OK, MessageBoxIcon.Hand, MessageBoxDefaultButton.Button1);
            }
        }
    }
}

using

System;

using

System.Collections.Generic;

using

System.Linq;

using

System.Text;

namespace

JLSistemas.PDA

{

 

public class CLR

 

{

 

public static Int32 Add(Int32 X, Int32 Y)

{

 

return X + Y;

}

}

}

Coordinator
Dec 14, 2010 at 3:31 PM

Hello Colomaj. Is this a compact framework project?

Dec 14, 2010 at 4:51 PM

Hi Igloobone:

The original project is a compact framework project but it's big; When the problem appear, i made a little example to run ,not in the pocket but in the pc in order to find out what's wrong and later apply the corrections in the compact framework project.

But, in short, both projects gave me the same error; or you think, was a bad idea send you a little example in a windows application?

Coordinator
Dec 14, 2010 at 11:46 PM

Issue is with EffiProz-CF ?