The company's database has been Sybase, it is also very good, the latest customer scene appeared a problem, the data can not be transmitted, look at the log is very simple, a bit to locate the reason.
It is simple, with more than 2000 parameters, because this function queries the child table based on the primary key of the primary table, using the Where primary table ID in (?,?,?,?......) Way, the parameter question mark is up to 2000, but it is strange that the direct use of SQL query sybase,5000 parameters is not a problem, it seems that the JDBC driver problem.
Our company with Sybase also has more than 10 years, this aspect is still very research, consulted a few people, JDBC connection Sybase Drive before with the official Jconn, later because of the storage of large objects when there is a bug, then unified replaced by the Jtds driver, Jtds drive parameters are limited, I tried the local two drive:Jconn, in my local environment, parameters can be more than 2000, up to 4,096, and then more error:
apparently Sybase put in (?,?) This is regarded as the use of or, each expression limit is 1024, but do not know why I this is the largest 4096, but let the field after the driver, the field environment up to 1024, more than 1024 on the report above the error, and please ask a few people, they know there is this restriction, may be related to the specific environment, But no one can say anything. Jtds Drive is relatively simple, parameters can not exceed 2000, more than will be an error. later on-line check, dbaspot inside a person also encountered this problem, someone told him can use the following command to view the server restrictions:
DBCC TRACEON (3604) DBCC SERVERLIMITS
as a result, the site environment is as follows, with up to 1024 expressions 4096,and/or:
know the limit size is good to do, on-site problem solving method, the first part of the data moved to other tables, and then the remaining data volume is small, SQL can be supported, these data transfer is finished, then the data back, then transfer, a little bit, finally solved the problem. Code also modified, because the interface has been fixed, can not modify too much, so only a large number of Main Table ID query the method of the child table, the query before splitting these IDs, every 800 (configurable) query once, more than a few times on the line. The problem is solved, but according to the actual situation does not appear this problem, the average dozens of on the hair once, why save so much, see log discovery is full of FTP space, file upload to FTP error, resulting in the database status is not updated, so has been stuck, saved one months of data, Later, FTP expanded, but too much data sent failed. Can be seen, the real environment, what is the situation, what can happen, the development of the consideration of not all-round really cause a lot of problems ah.