I am working on a process in my applicaiton that is used to update a table in our database called 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 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 The problem is that when I execute the Just to make sure it's clear, the |
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 |
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.
I've turned on request-level logging and there are no begin end statements wrapping either of the two statements. Any other ideas?
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.)
@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.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 :
"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."
If you name the temp table starting with a # sign it will be build automatically for you.
HTH
...but the same (i.e. automatic creation of the temp table) is true for the clause he is using in the OP:
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.