Question Re: Need help getting this to work in Technology Q&A

Need help getting this to work

Former Member — Wed, 09 Jan 2013 13:59:00 GMT

I am working on a process in my applicaiton that is used to update a table in our database called ListDetails. The idea behind this process is that the data is in a text file that has been parsed into a fixed, known format by another, outside process. We take this text file, parse it into records, and insert each into a temporary table. When all of the data is loaded into the temporary table, we use a stored procedure to merge the data from the temporary table into the live table.

This process was originally built using SQL Server and works there. I am porting it to Sql Anywhere 12.0.1. This process is built in C#.

My problem is in the loading into the temporary table phase. Here's the code for creating the temporary table:

new SACommand( @"DROP TABLE IF EXISTS tempimport;
       SELECT * INTO LOCAL TEMPORARY TABLE tempimport FROM ListDetails WHERE 1 = 0;", saConnection, saTransaction ).ExecuteNonQuery();

This executes without any errors.

In SQL server, we used bulk copy to load the data. It turns out that a dedicated SACommand that runs an INSERT statement is actually faster that the SA ADO.net SABulkCopy class, so I created just that:

SACommand insertCommand = new SACommand( "INSERT INTO tempimport " +
                                             "( ListDetailId, ListId, CountryId, LocaleCode, Plate, HashedPlate, AlarmClassId, BeginDate, EndDate, " +
                                               "ListPriorityId, VehicleTypeId, PlateClassId, MakeId, ModelId, Year, ColorId, " +
                                               "Notes, OfficerNotes, CreateDate, Subscriber, InstanceId ) " +
                                         "VALUES " +
                                             "( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) ", saConnection, saTransaction );

The next code creates a bunch of SAParameter objects, one for each ?, and adds them to the SACommand object's Parameters collection. Then, in a loop, I parse the file, load the data into each SAParameter object's Value property as needed, and finally execute the SACommand.

The problem is that when I execute the SACommand, I get an error that states that the table tempimport does not exist. What am I doing wrong? Should I use a DECLARE LOCAL TEMPRORARY TABLE command? The advantage of the way I'm doing it is that this code doesn't have to change if the ListDetails table ever changes.

Just to make sure it's clear, the SAConnection and SATransaction objects in the two SACommands are the same objects, and neither the transaction or the connection end until after all of the work is done.

Re: Need help getting this to work

Breck_Carter — Wed, 09 Jan 2013 14:52:07 GMT

Try using request-level logging to see what SQL statements are sent to the server. If the SQL in the SACommand is wrapped in a compound statement (begin end) when it hits the server, then tempimport will "go out of scope" when the compound statement ends.

Re: Need help getting this to work

Former Member — Wed, 09 Jan 2013 15:08:56 GMT

I've turned on request-level logging and there are no begin end statements wrapping either of the two statements. Any other ideas?

Re: Need help getting this to work

VolkerBarth — Thu, 10 Jan 2013 04:00:58 GMT

Just some questions (and I don't know if they count as ideas...):

Does it work when you only add one row (i.e. when you just loop over one element??
I'm not sure if "SELECT INTO" fits to the ExecuteNonQuery() method - does it work if you split the first SACommand into two separate commands?
Does it work with "DECLARE LOCAL TEMPORARY TABLE" instead of select into? - (I agree on your point of the advantage of a schema-agnostic approach.)

Re: Need help getting this to work

Former Member — Thu, 10 Jan 2013 08:26:41 GMT

@VolkerBarth: I haven't tried a "DECLARE LOCAL TEMPORARY TABLE" but it does work with a "CREATE LOCAL TEMPORARY TABLE". I'm leaving it like that for now because it works. However, I would like to see we can get the "SELECT INTO" to work. It does look like the temporary table is going out of scope. It works when I run it in ISQL.

Re: Need help getting this to work

Former Member — Mon, 14 Jan 2013 09:28:25 GMT

A question for your problem :

The connection in Interactive SQL and in the .NET Code is for users that have the same level of rights ?

For a better control you can create a procedure that :

1) use the sa_get_table_definition() on your 'ListDetails' Table.

2) do some string manipulations on the procedure return to build the dynamic CREATE LOCAL TEMPORARY TABLE statment. From the documentation :

"If you want a procedure to create a local temporary table that persists after the procedure completes, use the CREATE LOCAL TEMPORARY TABLE statement instead."

3) do an EXECUTE IMMEDIATE statment on this string.

Re: Need help getting this to work

thomas_duemesnil — Mon, 14 Jan 2013 14:51:00 GMT

If you name the temp table starting with a # sign it will be build automatically for you.

SELECT * INTO #tempimport FROM ListDetails WHERE 1 = 0;

HTH

Re: Need help getting this to work

VolkerBarth — Tue, 15 Jan 2013 04:20:24 GMT

...but the same (i.e. automatic creation of the temp table) is true for the clause he is using in the OP:

SELECT * INTO LOCAL TEMPORARY TABLE tempimport FROM ListDetails WHERE 1 = 0;

Re: Need help getting this to work

Former Member — Thu, 07 Feb 2013 17:18:19 GMT

Just to share, the solution was to hard code the layout of the temporary table in a DDL statement, i.e.,

CREATE LOCAL TEMPORARY TABLE tempimport(
    . . .
)

This means that I will have to update the DLL if we ever change the layout of the table. Even though the SELECT INTO statement works in the Interactive SQL program, it will not work in a program. Frankly, this sucks, but it's the only way to get the job done.

Re: Need help getting this to work

Former Member — Fri, 12 Jul 2013 09:13:22 GMT

The connection is established in C# and always uses the DBA user ID to connect.

Calling sa_get_table_definition() never occurred to me. That's an interesting idea. I'll have to look at that one of these days.