Andreas Reetz wrote:
> hi
> I have some problems with the using of insert select statment. I try
> following code:
>
> package test;
> import java.sql.*;
> public class Untitled1 {
> public Untitled1() { }
> static String sql = " INSERT INTO TBMAHIERARJOB ( MANDANT, " +
> " ID, " +
> " HISTNR, " +
> " STATUS, " +
> " GUELTIGAB, " +
> " FKOFSTAMM, " +
> " FKMAMITARBEITER, " +
> " FKFGUSER, " +
> " FKFGUSERHISTNR, " +
> " ERFASST, " +
> " MANUMMER, " +
> " BEARBSTATUS, " +
> " TABLENAME, " +
> " BEARBTYP, " +
> " BEARBVALUE, " +
> " BEARBGRUND ) " +
> " (SELECT MANDANT MANDANT, " +
> " 1 + ID ID, " +
> " 1 HISTNR, " +
> " 2 STATUS, " +
> " ? GUELTIGAB, " +
> " FKOFSTAMM FKOFSTAMM, " +
> " ID FKMAMITARBEITER, " +
> " 0 FKFGUSER, " +
> " 0 FKFGUSERHISTNR, " +
> " ? ERFASST, " +
> " manummer MANUMMER, " +
> " 0 BEARBSTATUS, " +
> " 'TBMAMITARBEITER' TABLENAME, " +
> " 1 BEARBTYP, " +
> " mauebernr BEARBVALUE, " +
> " 0 BEARBGRUND " +
> " from tbmamitarbeiter " +
> " where mandant = 0 and status = 2) ";
>
> public static void main(String[] args) {
> try {
> Class.forName("com.sap.dbtech.jdbc.DriverSapDB");
> Connection con =
> DriverManager.getConnection("jdbc:sapdb://localhost/test","dba
> ","dba");
>
> System.err.println("DB="+con.getMetaData().getDatabaseProductName());
>
> System.err.println("DB-Version="+con.getMetaData().getDatabase
> ProductVersion());
>
> System.err.println("Driver="+con.getMetaData().getDriverName());
>
> System.err.println("Driver-Version="+con.getMetaData().getDriv
> erVersion());
> PreparedStatement stmt = con.prepareStatement(sql);
> stmt.setDate(1, new
> java.sql.Date(System.currentTimeMillis()));
> stmt.setDate(2, new
> java.sql.Date(System.currentTimeMillis()));
> stmt.executeUpdate();
> }
> catch (Exception ex) {
> ex.printStackTrace();
> }
> }
> }
>
> I get following result:
> DB=SAP DB
> DB-Version=Kernel 7.3.0 Build 021-000-000-000
> Driver=SAP DB
> Driver-Version=package com.sap.dbtech.jdbc, "SAP DB JDBC
> Driver", "SAP AG",
> "7.3.0 Build 021-000-084-832"
> com.sap.dbtech.jdbc.exceptions.DatabaseException: SAP DBTech
> SQL: [-9400]
> System error: AK Cachedirectory full
>
> If I remove date-values (gueltigab, erfasst) it is working fine.
>
> Is it a bug or can anybody help me ????
>
> The tables definitions are:
> CREATE TABLE TBMAHIERARJOB
> (
> MANDANT INTEGER NOT NULL
> DEFAULT 0,
> ID INTEGER NOT NULL
> DEFAULT 0,
> HISTNR INTEGER NOT NULL
> DEFAULT 0,
> STATUS SMALLINT NOT NULL
> DEFAULT 0,
> GUELTIGAB DATE NOT NULL
> DEFAULT DATE,
> FKOFSTAMM INTEGER NOT NULL
> DEFAULT 0,
> FKMAMITARBEITER INTEGER NOT NULL
> DEFAULT 0,
> FKFGUSER INTEGER NOT NULL
> DEFAULT 0,
> FKFGUSERHISTNR INTEGER NOT NULL
> DEFAULT 0,
> ERFASST DATE NOT NULL
> DEFAULT DATE,
> MANUMMER CHAR(10) NOT NULL
> DEFAULT ' ',
> BEARBSTATUS SMALLINT NOT NULL
> DEFAULT 0,
> TABLENAME CHAR(18) NOT NULL
> DEFAULT ' ',
> BEARBTYP SMALLINT NOT NULL
> DEFAULT 0,
> BEARBVALUE CHAR(50) NOT NULL
> DEFAULT ' ',
> BEARBGRUND INTEGER NOT NULL
> DEFAULT 0,
> CONSTRAINT P_KEY_1 PRIMARY KEY (MANDANT, ID, HISTNR)
> );
>
>
> CREATE TABLE TBMAMITARBEITER
> (
> MANDANT INTEGER NOT NULL
> DEFAULT 0,
> ID INTEGER NOT NULL
> DEFAULT 0,
> HISTNR INTEGER NOT NULL
> DEFAULT 0,
> STATUS SMALLINT NOT NULL
> DEFAULT 0,
> GUELTIGAB DATE NOT NULL
> DEFAULT DATE,
> FKOFSTAMM INTEGER NOT NULL
> DEFAULT 0,
> FKFGVERTRIEB INTEGER NOT NULL
> DEFAULT 0,
> FKFAFIBU INTEGER NOT NULL
> DEFAULT 0,
> MANUMMER CHAR(10) NOT NULL
> DEFAULT ' ',
> MAUEBERID INTEGER NOT NULL
> DEFAULT 0,
> MAUEBERNR CHAR(10) NOT NULL
> DEFAULT ' ',
> MAHAUPTID INTEGER NOT NULL
> DEFAULT 0,
> MAHAUPTNR CHAR(10) NOT NULL
> DEFAULT ' ',
> MASTATUS INTEGER NOT NULL
> DEFAULT 0,
> MAHAUPTSTATUS SMALLINT NOT NULL
> DEFAULT 0,
> MAART INTEGER NOT NULL
> DEFAULT 0,
> MASTUFE SMALLINT NOT NULL
> DEFAULT 0,
> MAAUSZAHLSTOP SMALLINT NOT NULL
> DEFAULT 0,
> MAEINTRITT DATE NOT NULL
> DEFAULT DATE,
> MAAUSTRITT DATE NOT NULL
> DEFAULT DATE,
> MAAUSTRITTGRUND INTEGER NOT NULL
> DEFAULT 0,
> MACHECK SMALLINT NOT NULL
> DEFAULT 0,
> MACHANGE SMALLINT NOT NULL
> DEFAULT 0,
> CONSTRAINT P_PKMA01 PRIMARY KEY (MANDANT, ID, HISTNR)
> );
>
> and there are 4 rows in tbmamitarbeiter.
Hurra, in the end I managed it to find out who, where and when overwrites
some
main memory which will result in that funny error much later.
It will be corrected with the next version of the kernel.
To help you in the meantime:
the table TBMAHIERARJOB should be changed 'a little bit'.
Instead of
BEARBSTATUS SMALLINT NOT NULL DEFAULT 0,
use something like this:
BEARBSTATUS FIXED(5) DEFAULT 0
CHECK BEARBSTATUS BETWEEN -32768 AND 32767 NOT NULL
that means, do not use smallint / integer + NOT NULL for the same column.
If you use the same constraint we use internally for smallint / integer, but
specify
it explicitly + NOT NULL, then your insert..select will work. It is just the
number of
smallint/integer-columns + the number of NOT NULL-columns in the
insert-table which should
(in the current release) not exceed the number of columns of that table to
be inserted in.
No doubt, it is strange behaviour, but bug-fix is at work.
Thank you for reporting and thank you for waiting for the answer.
Elke
SAP Labs Berlin
_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general
