0

What is the maximum number of placeholders is allowed in a single statement? I.e. the upper limit of attribute NUM_OF_PARAMS.

I'm experiencing odd issue where I try to tune the maximum number of multiple rows insert, ie set the number to 20,000 gives me an error because $sth->{NUM_OF_PARAMS} becomes negative.

Reducing the max inserts to 5000 works fine.

Thanks.

est
  • 557
  • 1
  • 4
  • 17
  • Dont know if i got your question right but if you need to know its value then why not "print $sth->{NUM_OF_PARAMS};" ? – Arunmu Oct 26 '11 at 07:43
  • @ArunMu: my question, in other words, is there a limitation on the number of placeholders (?) that DBI accept? For example, can I pass 10000 question marks in a single query? – est Oct 26 '11 at 13:28
  • 3
    I don't see any limits imposed by DBI in its source code. If you are finding a case where there is a limit it's likely that your running into a limit in the DBD for your database. – Ven'Tatsu Oct 26 '11 at 15:11
  • @Ven'Tatsu: you're correct, it seems to be coming from DBD::DB2 -- I still don't understand why though... – est Oct 27 '11 at 02:31

2 Answers2

1

As far as I am aware the only limitation in DBI is that the value is placed into a Perl scalar so it is what can be held in that. However, for DBDs it is totally different. I doubt many, if any databases support 20000 parameters. BTW, NUM_OF_PARAMS is readonly so I've no idea what you mean by "set the number to 20,000". I presume you just mean you create a SQL statement with 20000 parameters and then read NUM_OF_PARAMS and it gives you a negative value. If the latter I suggest you report (with an example) that on rt.cpan.org as it does not sound right at all.

I cannot imagine creating a SQL statement with 20000 parameters is going to be very efficient in any database. Far better to try and reduce that to a range or something like it if you can. In ODBC, 20000 parameters would mean 20000 IPDs and APDs and they are quite big structures. Since DB2 cli library is very like ODBC I would imagine you are going to eat up loads of memory.

bohica
  • 5,932
  • 3
  • 23
  • 28
0

Given that 20,000 causes negative problems and 5,000 doesn't, there's a signed 16-bit integer somewhere in the system, and the upper bound is therefore approximately 16383.

However, the limit depends on the underlying DBMS and the API used by the DBD module for the DBMS (and possibly the DBD code itself); it is not affected by DBI.

Are you sure that's the best way to deal with your problem?

Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278