Re: [PATCH] Binding sql variables in dbpool

Hi Robert,

through the patch:
+    /* prepare ocibind list */
+    for (i = 0; i < binds_len; i++) {
+        OCIBind *bndhp = NULL;
+        list_append(bind_list, bndhp);
+    }

why in hell are you trying to append NULL pointers to the list?
just do it here:
+    /* bind variables */
+    for (i = 0; i < binds_len; i++) {
+        Octstr *bind = list_get(binds, i);
+        OCIBind *bndhp;
                              ^^^^^
+        status = OCIBindByPos(stmt, &bndhp,
+                              conn->errhp, (i+1), (dvoid *) 
octstr_get_cstr(bind),
+                              (sword) octstr_len(bind)+1, SQLT_STR, (dvoid *) 
0, (ub2 *)0,
+                              (ub2 *)0, (ub4)0, (ub4 *)0, OCI_DEFAULT);
+        if (OCI_SUCCESS != status) {
+            oracle_checkerr(conn->errhp, status);
+            OCIHandleFree(stmt, OCI_HTYPE_STMT);
+            return -1;
+        }
           list_append(bndhp); <--------------- inserted line
+    }


would you please send some banchmakrs w/ and w/o this patch applied?
I believe that this patch will get only very very small performance advantage, 
because binds will get performance advantage only with prepared statements 
that is not a case in your patch. But dbpool has no any support for that 
feature yet, feel free to extend...

On Wednesday 10 March 2004 19:41, Robert Galach wrote:
> hi again,
>
> here is a patch to dbpool[.c, .h, _p.h, _oracle.h] and dlr_oracle.c
>
> this patch enables binding variable in sql statements
> and contains implementation for oracle.
> in other types of dbpool database select and update operations are not
> handled,
> so patch doesn't affect them.
>
> variables are bound as strings only for now,
> but it works good for current purposes and doesn't complicate dbpool
> interface too much
>
> this makes oracle sql history short and clean
> and lets oracle engine cache statements and speed up execution.
>
> as oracle documentation says:
> "Statements that are the same except for literals that are used in the
> "WHERE" clause could potentially be rewritten to use bind variables.
> Reparsing of basically the same SQL statement wastes time and resources. "
>
> it is also a good starting point to implement reusing prepared oci
> statements
> with different bound variables against preparing them with every
> select/update operation
>
> in dlr_oracle.c there is also some cosmetic changes with formatting sql,
> rewriten queries to use binds
> and added "where" condition to check destination field in dlr search/update
> statements
> (there may my few dlr rows with the same timestamp from the same smsc). I
> remember that it was checked in earlier dlr driver,
> but disapeared somwhere and the dst parameter left in functions only. It is
> important to me to identify excactly dlr, because of different cgi variable
> value in every dlrurl.
> if you agree, i guess it should be changed in other dlr_[mem/mysql/sdb].c
> files too, to keep semantic clear - I can add it if needed.
>
> patch attached.
>
> please vote & commit :)
>
> regards
> Robert

-- 
Best regards / Mit besten Grüßen aus Düsseldorf

Dipl.-Ing.
Alexander Malysh
___________________________________________

Centrium GmbH
Vogelsanger Weg 80
40470 Düsseldorf

Fon: +49 (0211) 74 84 51 80
Fax: +49 (0211) 277 49 109

email: [email protected]
web: www.centrium.de
msn: [email protected]
icq: 98063111
___________________________________________

Please avoid sending me Word, Excel or PowerPoint attachments.
See http://www.fsf.org/philosophy/no-word-attachments.html