[Firebird-net-provider] Some questions about the support of embedded server

[<car...@te...>]

Hello:

I have made a little test ( at the end of this email ) with embedded
server using PInvoke, it's a very simple test but it's working ( it's
not using ADO.NET yet ;) ).

Pierre Arnaud told to me some time ago that PInvoke has some performance
penalties but i have decided to start the tests with PInvoke for 3 reasons:

1. It can be used in mono::, this will allow to use it in linux,
and i think this is a very interesting issue.

2. As the embedded server works only in local i think that if the
performance penalty is not great, we can assume it.

3. It's much more easy for me than write a wrapper in C++ :D


I want to hear comments about this issue.

What do you think about give the embedded support using PInvoke ??




--
Best regards

Carlos Guzmán Álvarez
Vigo-Spain

P.S.: Sorry for send this email two times, i'm having sending/receiving 
emails in the .net provider the list,
i'm not sure if it's a sourceforge problem or a problem with my ISP.


Example using PInvoke:

int[] isc_status = new int[20];
int db_handle = 0;
byte[] param         = new byte[0];

string connString = @"c:\bugtracker.fdb";

FbDpbBuffer dpb = new FbDpbBuffer();

dpb.Append(1);
dpb.Append(58, new byte[] {120, 10, 0, 0});
dpb.Append(63, new byte[] {3, 0, 0, 0});
dpb.Append(48, "NONE");
dpb.Append(28, "SYSDBA");
dpb.Append(29, "masterkey");

byte[] dpbArray = dpb.ToArray();

// Attach to database
int result = FbClient.isc_attach_database(
isc_status,
(short)connString.Length,
connString,
ref db_handle,
(short)dpbArray.Length,
dpbArray);

Console.WriteLine("Database handle: {0}", db_handle);

// Start transaction
int tr_handle = 0;
isc_status = new int[20];
result = FbClient.isc_start_transaction(
isc_status,
ref tr_handle,
1,
ref db_handle,
0,
new byte[0]);

Console.WriteLine("Transaction handle: {0}", tr_handle);

// Allocate statement
int stmt_handle = 0;
isc_status = new int[20];
result = FbClient.isc_dsql_allocate_statement(
isc_status,
ref db_handle,
ref stmt_handle);

Console.WriteLine("Statement handle: {0}", stmt_handle);

// Prepare statement
string stmt = "select * from USERS";
isc_status = new int[20];

//
XSQLDA sqlda = new XSQLDA();
sqlda.version = 1;
sqlda.sqln         = 1;
sqlda.sqld         = 0;
sqlda.sqldaid = new String(new char[8]);

//
int size = XSQLDA.ComputeLength(sqlda.sqln);
IntPtr xsqlda = Marshal.AllocHGlobal(size);

Marshal.StructureToPtr(sqlda, xsqlda, true);

XSQLVAR var = new XSQLVAR();
var.sqlData = IntPtr.Zero;
var.sqlInd = IntPtr.Zero;
Marshal.StructureToPtr(
var,
new IntPtr(xsqlda.ToInt32() + XSQLDA.ComputeLength(0)),
true);

result = FbClient.isc_dsql_prepare(
         isc_status,
         ref tr_handle,
         ref stmt_handle,
         (short)stmt.Length,
         stmt,
         3,
         xsqlda);

sqlda = (XSQLDA)Marshal.PtrToStructure(xsqlda, typeof(XSQLDA));
sqlda.sqln = sqlda.sqld;

//
size = XSQLDA.ComputeLength(sqlda.sqln);
Marshal.FreeHGlobal(xsqlda);
xsqlda = Marshal.AllocHGlobal(size);

Marshal.StructureToPtr(sqlda, xsqlda, true);
for (int i = 0; i < sqlda.sqln; i++)
{
XSQLVAR wvar = new XSQLVAR();
wvar.sqlData = IntPtr.Zero;
wvar.sqlInd = IntPtr.Zero;
Marshal.StructureToPtr(
                 wvar,
                 new IntPtr(xsqlda.ToInt32() + XSQLDA.ComputeLength(i)),
                 true);
}

// Describe
isc_status = new int[20];
result = FbClient.isc_dsql_describe(
isc_status,
ref stmt_handle,
1,
xsqlda);

sqlda  = (XSQLDA)Marshal.PtrToStructure(xsqlda, typeof(XSQLDA));
XSQLVAR[] sqlvars = new XSQLVAR[sqlda.sqln];
for (int i = 0; i < sqlda.sqln; i++)
{
sqlvars[i] = new XSQLVAR();
sqlvars[i] = (XSQLVAR)Marshal.PtrToStructure(
         new IntPtr(xsqlda.ToInt32() + XSQLDA.ComputeLength(i)),
         typeof(XSQLVAR));
}

// Execute statement
isc_status = new int[20];
result = FbClient.isc_dsql_execute(
isc_status,
ref tr_handle,
ref stmt_handle,
1,
IntPtr.Zero);

// Fetch data
isc_status = new int[20];
size = XSQLDA.ComputeLength(sqlda.sqln);
Marshal.FreeHGlobal(xsqlda);
xsqlda = Marshal.AllocHGlobal(size);

Marshal.StructureToPtr(sqlda, xsqlda, true);
for (int i = 0; i < sqlda.sqln; i++)
{
sqlvars[i].sqlData = Marshal.AllocHGlobal(sqlvars[i].sqlLen);
sqlvars[i].sqlInd = Marshal.AllocHGlobal(2);
Marshal.StructureToPtr(
         sqlvars[i],
         new IntPtr(xsqlda.ToInt32() + XSQLDA.ComputeLength(i)),
         true);
}

result = FbClient.isc_dsql_fetch(
isc_status,
ref stmt_handle,
1,
xsqlda);

if (result != 0)
{
byte[] buffer = new byte[1024];

int length = FbClient.isc_interprete(
         buffer,
         ref isc_status);

    Console.WriteLine(
         Encoding.Default.GetString(buffer, 0, length));
}

// Interprete result
sqlda  = (XSQLDA)Marshal.PtrToStructure(xsqlda, typeof(XSQLDA));
byte[][] data = new byte[sqlda.sqln][];
for (int i = 0; i < sqlda.sqln; i++)
{
sqlvars[i] = (XSQLVAR)Marshal.PtrToStructure(
         new IntPtr(xsqlda.ToInt32() + XSQLDA.ComputeLength(i)),
         typeof(XSQLVAR));

data[i] = new byte[sqlvars[i].sqlLen];
Marshal.Copy(sqlvars[i].sqlData, data[i], 0, sqlvars[i].sqlLen);
}

// Free memory
for (int i = 0; i < sqlda.sqln; i++)
{
Marshal.FreeHGlobal(sqlvars[i].sqlData);
Marshal.FreeHGlobal(sqlvars[i].sqlInd);
}

Marshal.DestroyStructure(xsqlda, typeof(XSQLDA));
Marshal.FreeHGlobal(xsqlda);
xsqlda = IntPtr.Zero;

// Drop statement
isc_status = new int[20];
result = FbClient.isc_dsql_free_statement(
isc_status,
ref stmt_handle,
2);

// Commit transaction
isc_status = new int[20];
result = FbClient.isc_commit_transaction(
isc_status,
ref tr_handle);

// Detach from database
isc_status = new int[20];
result = FbClient.isc_detach_database(
isc_status,
ref db_handle);