Featured Post

Supermarche simulator

Tonight I would like you to introduce my very old project, which was created during my studies. Project was made at the second year of studies as a group project. This is just a simulation of the working supermarket. Program is in Polish language, it has a short documentations and statistics. You...

Read More

1001 form inputs in PHP and 1001 items inside IN operator in Oracle database

Posted by evolic | Posted in Databases, Programming, Web development | Posted on 10-08-2014

Tags: , , , , , , , , , , , , , , , , , , , , , , ,

0

Thousand number was a magical for many people among the ages. In Latin it was described by M letter (lac. Millennium).

In 2000 year we had experienced year 2 kilo bug (Y2K bug) also called Millennium bug caused by dating convention made many years earlier by describing year with two digits instead of four e.g. 1992 was described as 92. Year 2000 could be described in this system as 00, and current year 2014 as 14. But what with financial or population systems? Somebody could be born in both 1901 and 2001. Because of this problem whole world was updating the software.

But attentive reader could ask about the relation between number 2000 and current time, because the problem described above had passed and had been solved long time ago. After all, we have 2014.

In this post I would like to describe two cases, very actual and related to number 1000, which act as the limit, and makes some problems if exceeded (1001 and more) in web based applications.

Limit of the inputs send in the forms

In PHP configuration exists a limit for inputs sent in the form to the server and by default it equals 1000.
If we have a side with the form containing:

  • 2 text fields,
  • 1050 checkboxes
  • and next 3 text fields, where two of them stores the dates

we could encounter invalid behavious of the application if following case happen: selecting all checkboxes and entering data into the rest of fields will cause that only 1000 sent fields of the form will be read by the PHP script. So in mentioned case there will be read 2 first fields and 998 checkboxes. Next 52 checkboxes will be unchecked and 3 last fields will be left empty.

We can solve this issue by increasing the limit

  • either in php.ini file

  • or inside .htaccess file

Changing this parameter may be not enough in some cases, because of the limit of the data sent from the form (POST). This limit can be increased with following PHP parameter: post_max_size.

Default value of this option is equal 2MB and sometimes it cannot be enough.

Value of this parameter can be changed in similar way as we did with max_input_size:

  • in php.ini file,
  • or in .htaccess file.

Limit in Oracle database

In the last week I encountered also the second issue with number 1001 and this time it was related to Oracle database, which was used with the form described earlier.

It shows up that this database has a limit of the items specified in the IN operator in the SQL queries.

For instance:

All is OK until there is less than 1001 items. Oracle will raise an exception, if the items list will be larger than 1000.

To walk around this issue we can divide e.g. list of 2500 items into three parts:

  • two containing 1000 items,
  • and third including 50 items.

SQL query for this example would be as follow:

Second solution is to replace Oracle database with PostgreSQL, which is not less functional than its commercial competitor and hasn’t such limit (the same as MySQL database, which currently is in a possession of Oracle Corporation).

But migration into the world’s most advanced open source database is linked to many technical changes, although a big plus is cost reduction, because most frequently we have to bear additional costs during the extension company’s infrastructure with new servers, which occurs very expensive for some companies.

Useful links:

Write a comment