Mailing List
Home
Forum Home
MySQL General - General MySQL discussion
MySQL++ - Programming with the C++ API to MySQL
MaxDB - Everything about MaxDB, formerly known as SAP DB
ODBC - ODBC with the MySQL Connector/ODBC driver
MySQL on Win32 - Runing MySQL on Windows 9x/Me/NT/2000/XP
Java Help - Mostly related to the MySQL Connector/J driver
Perl - Perl support for MySQL with DBI and DBD::mysql
GUI - MySQL GUI Tools
Announcement
Subjects
• mysql openssl Question
• mysql:it 's a db not a dbms how it 's possible?!
• Does the binary log enabling affect the MySQL performances?
• Strange behavior, Table Level Permission
• FULLTEXT query format question
• Preventing Duplicate Entries
• Comparing and writing out BLOBS
• Executing MySQL Commands From Within C Program
• Can 't access mysql after kernel upgrade
• Mysql 4 0 Oracle Stored Procedure Trigger Conversion
• Downgrade Mysql from 4 to 3 23
• MySQL Cluster Software
• mysql test 4 1 fails with the gis test
• ERROR 2002: Can 't connect to local MySQL server through socket
• Getting Identity after INSERT
• Update one field with more fields from another table
• ERROR 1045: Access denied for user: 'root@localhost ' (Using
password: NO)
• mysql have same function mthod as Oracle decode()
 
Database crash with select command !?????????????

Database crash with select command !?????????????

2007-05-02       - By Beermann, Albert

 Back
Hello Everybody



I want to extract material data  from a structured table(instueckliste)
with an recursive query!

(all material needed for l_startstl)

My application connects via odbc



l_cmd = "DECLARE RNAME CURSOR FOR WITH RECURSIVE TMPOBJ " +;

       "(LEVEL,stlnr,stlbezeich,posnr,matnr,matbezeich,menge,me,ekz)  "
+;

       " AS (SELECT 1, instueckliste.instlnr as
stlnr,stlbeze.inbezeich_d as stlbezeich," +;

       "instueckliste.instlposnr as posnr," +;

       "instueckliste.inmatnr as matnr,matbeze.inbezeich_d as
matbezeich," +;

       "instueckliste.instlmenge as menge," +;

       "matbeze.inmatme as me,instueckliste.inekz as ekz " +;

       " from instueckliste " +;

       " join inmaterialstamm stlbeze on instueckliste.instlnr =
stlbeze.inmatnr " +;

       " join inmaterialstamm matbeze on instueckliste.inmatnr =
matbeze.inmatnr " +;

       " where instueckliste.instlnr = ?l_startstl " +;

       " UNION ALL SELECT TMPOBJ.LEVEL + 1, instueckliste.instlnr as
stlnr,stlbeze.inbezeich_d as stlbezeich," +;

       " instueckliste.instlposnr as posnr," +;

       " instueckliste.inmatnr as matnr,matbeze.inbezeich_d as
matbezeich,"+;

       " instueckliste.instlmenge * tmpobj.menge as menge," +;

       " matbeze.inmatme as me,instueckliste.inekz as ekz " +;

       " from tmpobj,instueckliste " +;

       " join inmaterialstamm stlbeze on instueckliste.instlnr =
stlbeze.inmatnr " +;

       " join inmaterialstamm matbeze on instueckliste.inmatnr =
matbeze.inmatnr " +;

       " where instueckliste.instlnr = tmpobj.matnr) " +;

       " SELECT tmpobj.* FROM TMPOBJ " +;

       " order by level,stlnr,posnr"



=sqlexec(connnr,l_cmd,"ERGEBNIS")



Everything OK!

I get the wanted result in 1 second.





Now i tried to extract the same data for building l_startstl twice

l_startmenge = 2

l_cmd = "DECLARE RNAME CURSOR FOR WITH RECURSIVE TMPOBJ " +;


"(LEVEL,stlnr,stlbezeich,posnr,matnr,matbezeich,menge,me,ekz)  " +;

           " AS (SELECT 1, instueckliste.instlnr as
stlnr,stlbeze.inbezeich_d as stlbezeich,instueckliste.instlposnr as
posnr," +;

           "instueckliste.inmatnr as matnr,matbeze.inbezeich_d as
matbezeich," +;

           "instueckliste.instlmenge * ?l_startmenge as menge," +;

           "matbeze.inmatme as me,instueckliste.inekz as ekz " +;

           " from instueckliste " +;

           " join inmaterialstamm stlbeze on instueckliste.instlnr =
stlbeze.inmatnr " +;

           " join inmaterialstamm matbeze on instueckliste.inmatnr =
matbeze.inmatnr " +;

           " where instueckliste.instlnr = ?l_startstl " +;

           " UNION ALL SELECT TMPOBJ.LEVEL + 1, instueckliste.instlnr
as stlnr,stlbeze.inbezeich_d as stlbezeich,instueckliste.instlposnr as
posnr," +;

           " instueckliste.inmatnr as matnr,matbeze.inbezeich_d as
matbezeich,"+;

           "instueckliste.instlmenge * tmpobj.menge as menge," +;

           " matbeze.inmatme as me,instueckliste.inekz as ekz " +;

           " from tmpobj,instueckliste " +;

           " join inmaterialstamm stlbeze on instueckliste.instlnr =
stlbeze.inmatnr " +;

           " join inmaterialstamm matbeze on instueckliste.inmatnr =
matbeze.inmatnr " +;

           " where instueckliste.instlnr = tmpobj.matnr) " +;

           " SELECT tmpobj.* FROM TMPOBJ " +;

           " order by level,stlnr,posnr"

           

=sqlexec(connnr,l_cmd,"ERGEBNIS")



DATABASE CRASH !!!!!!!!!

1526

Connectivity-Fehler: [SAP AG][SQLOD32 DLL][MaxDB] General error;800
Implicit SERVERDB restart (connection aborted)

[SAP AG][SQLOD32 DLL][MaxDB] General error;800 Implicit SERVERDB restart
(connection aborted)

S1000

800

1



A) Database crash with select command ??????????????????????????

b) How to extract all material that is needed to build l_startstl n
times ???????????????



Any help welcomed

Best regards

Albert