Friday, June 29, 2007

Unmanaged DLLs and string (or char*) output parameters

I wrestled with this one for awhile, but discovered (via Google) that you can use StringBuilder to allocate a fixed-length string, then pass the StringBuilder in by value to the exported function/method in the unmanaged DLL. Strings don't work in this case because they are not fixed length, and access violation errors can (and usually) occur.

Declare Ansi Function Function_Name Lib "lib.dll" (ByVal strGoingIn As String, ByVal strComingOut As StringBuilder) As Integer

Viewing exports for a DLL

Open a Visual Studio Command Prompt, navigate to the directory of the DLL to inspect, and use the following command:

dumpbin /export whatever.dll

Friday, June 15, 2007

More on Oracle execution plans

Another useful command in looking at Oracle execution plans is:
set autotrace traceonly explain

Query plans in Oracle (EXPLAIN PLAN)

Discovered this article when looking up how to view a query plan in Oracle.

Thursday, June 14, 2007

How to determine owners of Oracle tables

I recently needed to find out the schema names of certain Oracle tables that belonged to schemas other than the connecting user. This is because the four-part queries in SQL Server require the schema/owner name of the tables referenced. This query (run in SQL*Plus) lists the owners and names of all tables that can be seen by the connecting user:

SELECT OWNER, TABLE_NAME FROM ALL_TABLES;

A full reference for the ALL_TABLES view can be found here.

Friday, June 1, 2007

Casting to NUMERIC/DECIMAL with E notation

Found this neat tidbit on some other guy's blog. Basically, NUMERICs/DECIMALs are exact numeric types and cannot contain the E notation in their representations. FLOATs are approximate numeric types and can contain the E notation. FLOATs can also be CASTed to NUMERIC/DECIMAL with no problems (other than a loss of precision).

For example, take the VARCHAR value X = '10000000E-2'. To cast it to NUMERIC/DECIMAL, use the following:

SELECT CAST(CAST(X AS FLOAT) AS NUMERIC(15, 4))...

You would obviously lose some precision if you cast with a NUMERIC field of less precision than what is represented by the contents of the VARCHAR value.

Truncate the last n bytes of a file

Use FileStream.SetLength():

Dim strFile As String = "C:\Path\To\File.txt"
Dim fi As New FileInfo(strFile)
Dim fs As New FileStream(strFile, FileMode.Open, FileAccess.Write)
fs.SetLength(fi.Length - n)
fs.Flush()
fs.Close()