Error: temporary table not found

[huber1]

First of all, I wish to all of you a happy New Year and a good 2020!

I have switched to the SQL Anywhere native driver and finding now some occurance of following errors. In the java.sql.SQLException: line (somewhere a bit below the middle of the code) it says Table '#TMP_8473482885' not found. This is the table it docs an insert (second line of code).

As this is done by SQL Anywhere itself (my understanding) what could be the reason it can't find the temporary table?

2020-01-08 10:34:10,851 ERROR [RMI TCP Connection(18)-62.167.9.102] com.servoy.j2db.util.Debug - insert into #TMP_8473482885 (entraural_person_id_8473482887, entra_fraction_name_8473482888, entraod_school_year_8473482889, entram_profile_code_8473482890, entraeducation_code_8473482891, entra_fraction_name_8473482892, entraod_school_year_8473482893, entran_subject_code_8473482894) SELECT                    eet.entrance_exam_natural_person_id,                    eet.entrance_exam_period_fraction_name,                 eet.entrance_exam_period_school_year,                   eet.entrance_exam_profile_code,                 eet.entrance_exam_profile_education_code,                   eet.entrance_exam_profile_period_fraction_name,                 eet.entrance_exam_profile_period_school_year,                   eet.entrance_exam_test_definition_subject_code              FROM                    entrance_exam_tests eet             WHERE                   eet.entrance_exam_natural_person_id IN (                        SELECT                          pp.profile_position_natural_person_id                       FROM                            probation_positions pp                          INNER JOIN probations p                             ON pp.probation_class_member_class_period_fraction_name = p.class_member_class_period_fraction_name                             AND pp.probation_class_member_class_period_school_year = p.class_member_class_period_school_year                                AND pp.probation_class_member_natural_person_id = p.class_member_natural_person_id                          INNER JOIN class_members cm                             ON cm.class_period_fraction_name = p.class_member_class_period_fraction_name                                AND cm.class_period_school_year = p.class_member_class_period_school_year                               AND cm.natural_person_id = p.class_member_natural_person_id                     WHERE                           pp.profile_position_period_fraction_name = 'HS'                         AND pp.profile_position_period_school_year = 2019                           AND pp.profile_position_profile_definition_subject_code = 'D'                           AND cm.class_level_number = 1                           AND cm.class_sign = 'fa'                            AND pp.mark IS NOT NULL)                    AND eet.entrance_exam_period_fraction_name = 'HS'                   AND eet.entrance_exam_period_school_year = 2019                 AND eet.entrance_exam_test_definition_subject_code = 'D'                    AND eet.entrance_exam_profile_code IN (                     SELECT DISTINCT                         pp.profile_position_profile_definition_profile_code                     FROM                            probation_positions pp                      INNER JOIN probations p                         ON pp.probation_class_member_class_period_fraction_name = p.class_member_class_period_fraction_name                         AND pp.probation_class_member_class_period_school_year = p.class_member_class_period_school_year                            AND pp.probation_class_member_natural_person_id = p.class_member_natural_person_id                      INNER JOIN class_members cm                         ON cm.class_period_fraction_name = p.class_member_class_period_fraction_name                            AND cm.class_period_school_year = p.class_member_class_period_school_year                           AND cm.natural_person_id = p.class_member_natural_person_id                     WHERE                           pp.profile_position_period_fraction_name = 'HS'                         AND pp.profile_position_period_school_year = 2019                           AND pp.profile_position_profile_definition_subject_code = 'D'                           AND cm.class_level_number = 1                           AND cm.class_sign = 'fa'                            AND pp.mark IS NOT NULL)                    AND eet.mark IS NOT NULL parameters: <null> [ ]
java.sql.SQLException: [SAP][JDBC Driver][SQL Anywhere]Table '#TMP_8473482885' not found
    at sap.jdbc4.sqlanywhere.IIPreparedStatement.execute(Native Method)
    at sap.jdbc4.sqlanywhere.IPreparedStatement.execute(IPreparedStatement.java:390)
    at org.apache.commons.dbcp2.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:198)
    at sun.reflect.GeneratedMethodAccessor435.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at com.servoy.j2db.datasource.Zf.invoke(Zf.java:18)
    at com.sun.proxy.$Proxy15.execute(Unknown Source)
    at org.apache.commons.dbcp2.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:198)
    at org.apache.commons.dbcp2.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:198)
    at org.apache.commons.dbcp2.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:198)
    at org.apache.commons.dbcp2.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:198)
    at com.servoy.j2db.server.dataprocessing.Zx.Za(Zx.java:1286)
    at com.servoy.j2db.server.dataprocessing.Zx.Za(Zx.java:327)
    at com.servoy.j2db.server.dataprocessing.Zx.Za(Zx.java:1064)
    at com.servoy.j2db.server.dataprocessing.Zx.performQuery(Zx.java:919)
    at sun.reflect.GeneratedMethodAccessor427.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at sun.rmi.server.UnicastServerRef.dispatch(UnicastServerRef.java:357)
    at sun.rmi.transport.Transport$1.run(Transport.java:200)
    at sun.rmi.transport.Transport$1.run(Transport.java:197)
    at java.security.AccessController.doPrivileged(Native Method)
    at sun.rmi.transport.Transport.serviceCall(Transport.java:196)
    at sun.rmi.transport.tcp.TCPTransport.handleMessages(TCPTransport.java:573)
    at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run0(TCPTransport.java:834)
    at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.lambda$run$0(TCPTransport.java:688)
    at java.security.AccessController.doPrivileged(Native Method)
    at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run(TCPTransport.java:687)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
    at java.lang.Thread.run(Thread.java:748)

[chris_keating]

INSERT INTO #tmp requires that the temporary table already exist. To create and populate a temporary table in a single statement, you would use the syntax

select a,b,c into #tmp from t

Note that the INTO in this form always creates a temporary table. The table name does not need to start with a pound sign.

[huber1]

I have only the SELECT part (following the insert into #TMP_) in my code, not the insert part. I am assuming that comes from SQL Anywhere running the SELECT ...

[Breck_Carter]

> in my code

Please show us your code... the exact code, copy and paste, no edits, thanks! 🙂

[chris_keating]

It is unlikely that we are doing such a change - it would not be valid. The code appears to be generated. Perhaps this is happening in the software that eventually calls into SQL Anywhere.

[huber1]

Sorry for being late. Here is my code as it is in the Eclipse IDE using Servoy Developer (an Eclipse plugin).

@Chris: If I understand you correctly, it seems Servoy Developer is creating the temp table. Thanks for the hint.

     var query = "SELECT\\
                    eet.entrance_exam_natural_person_id,\\
                    eet.entrance_exam_period_fraction_name,\\
                    eet.entrance_exam_period_school_year,\\
                    eet.entrance_exam_profile_code,\\
                    eet.entrance_exam_profile_education_code,\\
                    eet.entrance_exam_profile_period_fraction_name,\\
                    eet.entrance_exam_profile_period_school_year,\\
                    eet.entrance_exam_test_definition_subject_code\\
                FROM\\
                    entrance_exams ee\\
                    INNER JOIN entrance_exam_tests eet\\
                        ON ee.period_fraction_name = eet.entrance_exam_period_fraction_name\\
                        AND ee.period_school_year = eet.entrance_exam_period_school_year\\
                        AND ee.natural_person_id = eet.entrance_exam_natural_person_id\\
                        AND ee.profile_code = eet.entrance_exam_profile_code\\
                        AND ee.profile_education_code = eet.entrance_exam_profile_education_code\\
                        AND ee.profile_period_fraction_name = eet.entrance_exam_profile_period_fraction_name\\
                        AND ee.profile_period_school_year = eet.entrance_exam_profile_period_school_year\\
                    INNER JOIN persons_roles pr\\
                        ON pr.person_id = ee.natural_person_id\\
                    INNER JOIN persons p\\
                        ON ee.natural_person_id = p.id\\
                WHERE\\
                    ee.period_fraction_name = '" + scopes.basConfiguration.getPeriodFractionName() + "'\\
                    AND ee.period_school_year = " + scopes.basConfiguration.getPeriodSchoolYear() + "\\
                    AND ee.entrance_exam_class_code = '" + selectedClass + "'\\
                    AND eet.entrance_exam_test_definition_subject_code = '" + selectedSubject + "'\\
                    AND (p.is_enrolment_cancelled is null OR p.is_enrolment_cancelled = 0)\\
                    AND pr.role_life_cycle_state_status = 'angemeldet'";
        foundset.loadRecords(query);

[chris_keating]

We can see that there is a significant difference between the query supplied and the query that is reported in the exception. I have no experience with Servoy but the documentation suggests that the query must have an ORDER BY on the primary keys (and if I understand correctly, the query must just return the primary keys and I cannot tell if the source query complies and should do so).

[huber1]

There is an additional insert into #ZMO_ ... part, but the SELECT part is the one of my SELECT code. The ODRDER BY is there (and Collies as you refer to), done with a sorting of the the foundset (where the result of the SELECT is temporary stored). ORDER BY can be done this way (sorting the foundset) as well as an ORDER BY clause in the SELECT statement. I have to speak to the Servoy people to find out about the error and why it does not find the TMP table. Thanks a lot for your answer.