ODBC 2.0.0 Comments

[Ingela Anderton]

Always go to the man pages for the documentation the source code may
in some cases not be sufficient for the end user.

Rudolph van Graan wrote:
> Another thing I noticed is that the documentation for param_query seems  
> to be incomplete:
> %%---------------------------------------------------------------------- 
> ----
> %% param_query(ConnectionReference, SQLQuery, Params, <TimeOut>) ->
> %%                             ok | {error, Reason}
> %%
> %% Description: Executes a parameterized update/delete/insert-query.
> %%---------------------------------------------------------------------- 
> ----
> param_query(ConnectionReference, SQLQuery, Params) ->
> 
> What is the format of Params? I cannot seem to get it working:
> 
> 11> odbc:param_query(R,"select * from subscriberstat where msisdn =  
> ?",["27829057496"]).
> 
> I got a
> ** {stopped,{'EXIT',<0.24.0>,
>                      {{badmatch,["27829057496"]},
>                       [{odbc,param_query,4},
>                        {erl_eval,do_apply,5},
>                        {shell,eval_loop,2}]}}}
> 
> Obviously  I am not feeding it the params correctly.

>From the documentation of odbc 2.0 (Note only on zero)

param_query(ConnectionReference, SQLQuery, Params) ->
param_query(ConnectionReference, SQLQuery, Params, TimeOut) -> ResultTuple | {error, Reason}

Types:

SQLQuery = String() - a SQL query with parameter markers/place holders
	   in form of question marks.
Params = [{ODBCDataType, [Values]}]
         Values = term() - Must be consistent with the Erlang data
	 type that corresponds to the ODBC data type ODBCDataType

See also common data types. (Pasted below ...)

Executes a parameterized SQL query. For an example see the "Using the
Erlang API" in the Erlang ODBC User's Guide.

Note!

Use the function describe_table/[2,3] to find out which ODBC data type
  that is expected for each column of that table. If a column has a
  data type that is described with capital letters, alas it is not
  currently supported by the param_query function. Too know which
  Erlang data type corresponds to an ODBC data type see the Erlang to
  ODBC data type mapping in the User's Guide.  Use a parameterized
  query to insert many rows in one go.


... From common types:

 ODBCDataType = sql_integer | sql_smallint | sql_tinyint |
          {sql_decimal, Precison, Scale} | {sql_numeric, Precison, Scale} |
          {sql_char, Size} | {sql_varchar, Size} | {sql_float, Precision} |
          {sql_float, Precision} | sql_real | sql_double | sql_bit | atom()
    

 6 > odbc:param_query(Ref,"INSERT INTO EMPLOYEE (NR, FIRSTNAME, "
                          "LASTNAME, GENDER) VALUES(?, ?, ?, ?)",
                           [{sql_integer,[2,3,4,5,6,7,8]},
                            {{sql_varchar, 20},
                             ["John", "Monica", "Ross", "Rachel",
                             "Piper", "Prue", "Louise"]},
                           {{sql_varchar, 20},
                             ["Doe","Geller","Geller", "Green",
                              "Halliwell", "Halliwell", "Lane"]},
                           {{sql_char, 1}, ["M","F","M","F","F","F","F"]}]).
      {updated, 7}
   



> Any ideas (The documentation on erlang.org still shows version 1.0.8 of  
> ODBC)
That has to do with that there is no unix patch for the latest odbc as
all commercial customers that use odbc use windows.

-- 
/Ingela

Ericsson AB - OTP team