Calling ESQL from .NET program

[Former Member]

I am going to be writing some code in a C DLL that will be called from an ADO.net program. I need the C code to use the same connection and transaction that the ADO.net program uses. That is, the work that the C code does must happen in the same transaction that the ADO.net code does its work in.

I'm a complete novice at ESQL. I have no idea how to do this, or even if it's possible. Can it be done?

Where does one find information on how to write ESQL code? The documentation is a bit light on detail. It seems to rely on sample code, which isn't answering my questions.

[VolkerBarth]

Just to ask: Does it need to be ESQL - or would ODBC do as well? I guess the ODBC API would seem way more familiar to ADO.Net than ESQL...)

(Yes, I'm aware that this doesn't answer any of your questios.)

[Former Member]

No, it can't be ODBC. We're trying to speed up the process in our program, not slow it down. I do know that there is a C API as well as ESQL. I know I can send multiple rows using the PUT statement in ESQL; if there is an equivalent in the C API, I can use that.

This has to do with my other question about sending data for multiple rows to be inserted into the database in one I/O operation. The performance of SABulkCopy is abysmal or we'd use it.

Basically, I'm looking to send lots of data in single I/O operations. I don't have time to chase down things that might work (i've got a 4 week deadline on this); I've got to do something that will work. I believe ESQL is the fastest way to get it done. And I know I can make it work in ESQL. So that's what I'm going to do.

[johnsmirnios]

ESQL uses DBLIB which goes directly to our native communications library. ODBC goes directly to our native communications library too -- there's no reason to expect ODBC to be slower.

The "C API" (dbcapi) is a convenience layer that is used by most or all of our scripting language drivers (perl, php, etc) and is implemented in ESQL. It is more convenient to use because it manages many buffers, etc for you and that comes at a minor cost.

[Former Member]

John:

My experience with ODBC, in general, has been that the ODBC drivers have more overhead & are slower. I have no experience using ODBC with SQL Anywhere.

Are there any books out there that explain how to write ESQL programs? And is it possible for code in a DLL written using ESQL to share the connection & transaction in use in C# ADO.NET?

[johnsmirnios]

I don't know of any books other than the documentation: http://dcx.sybase.com/index.html#sa160/en/dbprogramming/pg-esql.html*d5e25879

Sorry but I also don't know anything definitive about .NET and how/whether it allows the same connection to use ESQL. A quick glance at the code suggests that it links directly to the same native communications libraries as DBLIB and ODBC. I think that would mean that there is no SQLCA available for native code to use since SQLCA objects are only used by DBLIB. There would also be no ODBC connection for native code to use either.

[VolkerBarth]

@John: Thanks to confirm my experience with SQL Anywhere's ODBC drivers:)

---

@Tony: Sorry, I don't mean to mess around with your project requirements. It's free advice, feel free to use it or not:)

However, from your other question, your basic question seems to be how to do mass-inserts effectively with ADO.Net.

I still have the impression it would be way easier to try an OPENSTRING() approach with ADO.Net than to try to have a transaction on different APIs. In the end, you would just be sending one simple SQL statement to the engine, without any parameters or with just one parameter naming the file or variable which contains the mass data. With such an approach, the API used won't matter much since the base work has to be done within the database engine. In other words, the database engine would need to extract the data itself from the source, and you would not need to supply the values parameter-wise. - Well, apparently, my assumption on the data source can easily be wrong since you have not yet told how the "data [sent] over the wire from the server to the client" is stored within the client machine. Maybe you could be more verbose here?

IIRC, OPENSTRING() is at the heart of the LOAD TABLE statement, and LOAD TABLE is surely the fastest way to do mass insertion. However, LOAD TABLE won't handle "upserts", and it does an automatic commit which would end your transaction...

[VolkerBarth]

Just to add: Here is a FAQ that explains the connection between LOAD TABLE and OPENSTRING - by John himself:

Where did "OpenString" come from?

[Former Member]

Volker:

Given what John has said about there being no SQLCA in the .NET drives, it seems ESQL isn't going to cut the mustard for me, anyway. There's no way we're going to rewrite our entire program in ESQL at this late date and in the timeframe I have.

And, given that LOADTABLE & OPENSTRING are almost one & the same thing, OPENSTRING is where I'm headed. All I have to do is figure out how to structure the BLOB I'm going to pass to the statement as the one and only parameter. Not to mention how exactly to write the statement to execute.

[jeff_albion · Accepted solution]

That is, the work that the C code does must happen in the same transaction that the ADO.net code does its work in. ... I'm a complete novice at ESQL. I have no idea how to do this, or even if it's possible. Can it be done?

No. .NET (managed) to native (unmanaged) code is always done through P/Invoke. You could call a native .DLL from within an SATransaction .NET context, but the ESQL DLL would still need to create its own connection back to the database, so it wouldn't be "within the same connection context" as the .NET connection (e.g. you can't "tap in" to the underlying unmanaged connection object that is already held by SAConnection from your ESQL code).

Conversely, if you use the ESQL external environment called directly from the database server, you are supplied the database context to avoid creating this extra connection when invoked, so if you simply wrote everything in an ESQL DLL with this external environment architecture in mind outside of .NET, you could work within an existing connection context as you currently desire - however, as the documentation notes, there is a small performance penalty to call out to external environments, so it's not clear that this process would actually be faster even if you were to implement it.

[Former Member · Accepted solution]

Thanks. I've already decided not to pursue this route, and now I'm especially glad that I have. We don't have the time or inclination to rewrite everything in a native DLL. It's taken us 2 years to get to where we are now and we're very happy not to take a giant step back to where we were in 2010 . . . 😉