Marco, thank you for your reply.
My apologies for the incomplete report, but it was at 4:15am this morning
here in South Africa.
Here is some more information about the error:
- You correctly identified the exact error.
- the appplication is written in Clarion 5.5 Enterprise Edition (for Native
Windows and ClarioNet Access)
- There are only 60 tables in total and the largest ones contain only around
1.5 million entries.
- It seems to occur on two tables one is a table that has become wide (CUS -
approx. 30000 entries).
These two tables are the "Customer" table and the "Ledger Batch Detail"
table (LEDBATDET - approx. 600000 entries after cleaning up).
Table1: CUS: -
--------------
I have previously separated the CUS table into more tables to allow for the
same error to be resolved.
We have added a few more fields which has pushed it over the limit again.
In other words we cannot do "SELECT * FROM CUS" or even "SELECT FIELD1,
FIELD2, ..., FIELDn FROM CUS" where all fields are listed. This occurs from
any direct query tool including SQL Studio. And as luck may have it the
client has already created many important reports on the existing structure.
Until 74 fields it was OK, but now with 80 it gives the error again.
This the state of the current test database: (Excluding Indexes)
--------------------------------------------
CREATE TABLE DBA.CUS(
CUSTOMERID INTEGER NOT NULL PRIMARY KEY,
ACCOUNT CHAR(15),
GDC CHAR(1),
POLICYNUMBER CHAR(30),
EMPLOYERCODE CHAR(50),
EMPLOYEENUMBER CHAR(30),
CUSTOMERSTATUS CHAR(10),
PREFERREDCORRESPONDENCETYPE CHAR(10),
TITLE CHAR(10),
INITIALS CHAR(10),
SURNAME CHAR(40),
DATEOFBIRTH DATE,
NATIONALIDNUMBER CHAR(20),
HOMETELEPHONENUMBER CHAR(30),
HOMEFAXNUMBER CHAR(30),
WORKTELEPHONENUMBER CHAR(30),
WORKFAXNUMBER CHAR(30),
EMAILADDRESS CHAR(50),
POSTALADDRESSLINE1 CHAR(40),
POSTALADDRESSLINE2 CHAR(40),
POSTALADDRESSLINE3 CHAR(40),
POSTALADDRESSLINE4 CHAR(40),
POSTALCITYORTOWNID CHAR(10),
PAYMENTTYPEID CHAR(10),
BANKID CHAR(20),
BANKACCOUNTNAME CHAR(40),
BANKACCOUNTNUMBER CHAR(40),
BANKACCOUNTTYPEID CHAR(20),
BANKBRANCHCODE CHAR(20),
APPLICATIONDATE DATE,
EFFECTIVEDATE DATE,
FIRSTPAYMENTDATE DATE,
FIRSTDEBITORDERDATE DATE,
EXPIRYDATE DATE,
EXITDATE DATE,
PREFERREDDAYOFTHEMONTH INTEGER,
CONTRACTTYPEID CHAR(20),
SALESREPID CHAR(10),
RECEIVEDDEPENDANTSFORMYN SMALLINT,
RECEIVEDDEPENDANTSFORMDATE DATE,
RECEIVEDDEPENDANTSFORMBYSTAFFID INTEGER,
SENTCONTRACTSCHEDULEYN SMALLINT,
SENTCONTRACTSCHEDULEDATE DATE,
SENTCONTRACTSCHEDULEBYSTAFFID INTEGER,
SENTCONTRACTDETAILSYN SMALLINT,
SENTCONTRACTDETAILSDATE DATE,
SENTCONTRACTDETAILSBYSTAFFID INTEGER,
SENTMEMBERSHIPCARDYN SMALLINT,
SENTMEMBERSHIPCARDDATE DATE,
SENTMEMBERSHIPCARDBYSTAFFID INTEGER,
CURRENTAMOUNT DECIMAL(15, 2),
LASTTOTALDATE DATE,
LASTCREDITAMOUNT DECIMAL(15, 2),
LASTCREDITDATE DATE,
LASTDEBITAMOUNT DECIMAL(15, 2),
LASTDEBITDATE DATE,
SCRATCHCARDNO INTEGER,
SCRATCHCARDNOMINATEDYN SMALLINT,
ACTION_REACTIVATEINACTIVEACC INTEGER,
LASTPACS_PERIOD_RAN INTEGER,
LASTPACS_DATE_RAN DATE,
PACS_MULTIPLE_MARK INTEGER,
LASTINVOICE_PERIOD_RAN INTEGER,
LASTINVOICE_DATE_RAN DATE,
GOV_EXPORT_DATE DATE,
ENTRY_DATE DATE,
CURRENT_PREMIUM DECIMAL(15, 2),
PREMIUM_CHANGED_DATE DATE,
GAMESHOW_NOMINATED_YN SMALLINT,
GAMESHOW_INVITE_YN SMALLINT,
GOV_UPDATE_FLAG SMALLINT,
GOV_PREV_AMOUNT DECIMAL(15, 2),
MSN_EFF_DATE DATE,
REF_SCRATCHNO INTEGER,
ENTRY_STAFFID INTEGER,
MSN_FPD TIMESTAMP,
TELECALL_YN SMALLINT,
TELECALL_STAFFID INTEGER,
TELECALL_DATE TIMESTAMP,
TELECALL_TIME TIMESTAMP
)
- NOTE: These timestamps above is the beginning of an experiment to see if
we can create one structure for several databases (the last 6 columns are
new). The production database uses VARCHAR instead on CHAR. Fieldnames are
long to assist clients in creating theit own reports.
Table2: ACC_LEDGERBATCHDETAIL: -
--------------------------------
This is more of a mystery. You can "SELECT * FROM LEDBATDET" and get
successful results, but when I looked in ODBC trace log after a problem
where a processing routine in our application would only run after several
tries.
Here is that section of the ODBC trace: (zipped the full version will be
12K, if you want it?)
---------------------------------------
<<cursors seem to be set above this section>>
SELECT "SYSID","BATCHNO","GDC","ACCOUNT","DISCOUNTFLAG","CURRCODE","SPARE",
"PERIOD","TRNDATE","TYPE","REFERENCE","CLAIM","AMOUNT","DEBITORCREDIT",
"TAXAMOUNT","CONTRAGDC","CONTRAACCOUNT","CONTRADEBITCREDIT","CONTRADESCRIPTI
ON",
"CONTRATAXTYPE","CONTRATAXAMOUNT","THISCURRTAXAMOUNT","BANKTAXAMOUNT",
"THISCURRAMOUNT","BANKCURRAMT","RECONFLAG","DESCRIPTION","TAXTYPE",
"LINKACCOUNT","COUNTRY","GENERATED","PAYBASED","USERID","USERDEFINITION",
"CHEQUEFLAG","LINKID","CREATEDININVOICE","EXPORTEDYN","EXPORTEDCONTRAYN"
FROM
"ACC_LEDGERBATCHDETAIL" WHERE 0 = 1
PARSE : CMD :
mfIndex init : 7
PARSEID: OUTPUT: 000785CF 00003E01 3C002C00
START : DATE : 2002-06-18 TIME : 0001:53:10
END : DATE : 2002-06-18 TIME : 0001:53:10
SQLCODE: -712 Too many parameters, SQLMAX too small
Please let me know if you would like to know anything else.
Regards
Marius Snyman
-----Original Message-----
From: Paskamp, Marco [mailto:[EMAIL PROTECTED]]
Sent: 18 June 2002 10:22
To: '[EMAIL PROTECTED]'; [EMAIL PROTECTED]
Subject: RE: SQLMAX too small - error
Hello,
because I don't know the coding of your program and you don't send any
traces I can only make some assumptions about the reason of the error.
I think the error you get is "-712 Too many parameters, SQLMAX too small".
Right?
SQLMAX is a constant which is defined in cpc.h. It's used for the
precompiler-generated SQLDA as the maximum number of possible
columns/parameters of a resultset/command. The default value of SQLMAX is
300. You can increase/decrease this values by modifying the constant SQLMAX
in the file cpc.h.
If you define your own SQLDA-descriptor for input/output values must set
da.sqlmax explicit to a value <= SQLMAX. Detailed information about dynamic
sql and SQLDA can you find in the precompiler manual
(http://www.sapdb.org/pdf/usmanprecompiler_eng.pdf) in chapter "2.10 Dynamic
SQL Statements"
A precompiler trace or small test program which reproduces the error would
be very helpful for analyzing your error.
Regards,
Marco
----------------------------------------------
Marco PASKAMP
SAP DB, SAP Labs Berlin
> -----Original Message-----
> From: Marius Snyman [mailto:[EMAIL PROTECTED]]
> Sent: Dienstag, 18. Juni 2002 04:15
> To: [EMAIL PROTECTED]
> Subject: SQLMAX too small - error
>
>
> It seems that if I exceed a certain number of
> columns/character width (not
> sure which) in a select statement I get the folowing error.
>
> I have read the C precompiler documentation which contains the only
> reference to it without understanding what to do.
>
> If anyone can please explain what it means or offer a
> possible remedy please
> reply.
>
> Thank you
>
> Marius Snyman
>
> _______________________________________________
> sapdb.general mailing list
> [EMAIL PROTECTED]
> http://listserv.sap.com/mailman/listinfo/sapdb.general
>
_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general
