On 14-Sep-2005 Tim Bunce wrote: > On Wed, Sep 14, 2005 at 02:48:18PM +0200, Paskamp, Marco wrote: >> Hi, >> >> > You mean DEFAULT as in >> > >> > INSERT INTO foo VALUES (1, DEFAULT, "bar"); >> > >> > where the _SQL_keyword_ DEFAULT triggers the use of whatever value was >> > declared as the DEFAULT value in the table definition? >> >> Yes, this is what I mean. But I want to set the default value as a >> parameter for a prepared statement like binding undef for NULL values. >> >> > > If DBI does not support default values as parameter are >> > there any other >> > > driver that support this. If yes, how is it solved? >> > >> > Well here's one way that might work: >> > >> > INSERT INTO foo VALUES (?, IFNULL(?,DEFAULT), "bar"); >> > >> > (substitute something else for IFNULL isn't that's not quite right) >> > but I'm not sure if that's valid and/or viable. >> In this case you can no longer insert the NULL value, it will always be >> substituted by the DEFAULT value. > > Sure, it was just an example of a technique you can extend > > CASE ? WHEN 'trigger_value' THEN DEFAULT ELSE ? END CASE > > (or whatever syntax/functions suit). > >> > How does the MaxDB API expose the functionality? >> >> The DBD::MaxDB uses the SQLDBC interface (c++ client interface for >> MaxDB). When you bind a parameter for a prepared statement with SQLDBC >> you can set an indicator value. This indicator value normally defines >> the length of the bounded buffer. But there are some special indicator >> values SQLDBC_NULL_DATA and SQLDBC_DEFAULT_PARAM that signals that the >> NULL VALUE/DEFAULT VALUE should be set for the parameter. As far as I >> know ODBC does it in the same way. > > Can someone point me to the relevant part of the ODBC spec?
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcsq lputdata.asp Note the: "SQL_DEFAULT_PARAM. A procedure is to use the default value of a parameter, rather than a value retrieved from the application. This value is valid only in a procedure called in ODBC canonical syntax, and then only if the InputOutputType argument is SQL_PARAM_INPUT or SQL_PARAM_INPUT_OUTPUT. When *StrLen_or_IndPtr is SQL_DEFAULT_PARAM, the ValueType, ParameterType, ColumnSize, DecimalDigits, BufferLength, and ParameterValuePtr arguments are ignored for input parameters and are used only to define the output parameter value for input/output parameters." >> A DBD::MaxDB specific solution would be to define a global constant >> variable DBD::MaxDB::default_parameter which can be used to bind as >> parameter. But this would be MaxDB specific and not common for all DBD >> driver. It would be better if we could find a DBD driver independend >> solution in the DBI interface. What do you think? > > I'm not aware of any drivers that support binding DEFAULT parameters and > I think you're the first person to ask for it, so it can't be in great > demand. I've never seen SQL_DEFAULT_PARAM used in ODBC code and it is for procedures only. > Usually for such things I like at least two drivers to implement > something as a private extension before considering defining a common > API in the DBI spec. > > This is a fairly simple though so maybe that's not needed. I would like > to hear what other driver authors have to say... > > Tim. Sounds like this use of a default parameter is different from ODBC. Martin -- Martin J. Evans Easysoft Ltd, UK Development
