Feeds, issues, packages and code source about emulation and pograming, of VENENUX proyects, Debian related distros and massenkoh!

gambas3: collections of collections as php array of arrays

Those guys that code in php will find funny that making objects of data in tables are easy.. arrays, arrays.. that's why in php an array can handle any value, and the index can be any type, not only integer indexs. The article was inspired in a problem in gambas mail list:

But wait, in php, an array can handle multiple arrays, that its not the same of a multidimensional array! care! That its the better in this special case of this article!

I mean: an array inside an array! and the values and key of those arrays can be any type.. great!
In java, ther's some difficulty, a collection its the only way to do that and there's another, only one type of values can handle in a collection.

Unfortunatelly in Gambas its more harded yet: must be collections and must iterate twice to handle values in.

An Example:

..lets get simple: a registry "order" that have many "products" so :
In java, there's DAO objects, but with collections, and in php more easy, only an array in array! 

The Data example:

* Main object: object (1 element with many attrs) and one of those attrs, its the cod_contenido , of the products of the order 
* Second objects: attr:cod_contenido-> (object with many elements, and each element many atrrs) 

|   ORD101                    |
| cod_orde:101                |
| des_order:description bla bl|----------------------------|
| cod_contents:1011           |        CON1011             |
                              |    cod_product:101,        |
                              |    cod_order:101,          |
                              |    can_product:2           |
                              |    cod_product:102         |
                              |    cod_order:101           |
                              |    can_product:12          |

The make it on php:

In php only has to convert to array each recordset and then iterate each element,, the trick that makes php easy to use its that in the interesting key "cod_contenido" we can stop, iterate again and put inside the main array.. lets see:

$conn = mysqli_connect($servername, $username, $password, $dbname);
$orders = "SELECT * FROM almorder WHERE cod_order = '001'";
$ordersproducts = "SELECT * FROM alorderproducts WHERE cod_order = '001'";
$result = mysqli_query($conn, $orders);
foreach($result as $keyname => $valuename) 
    $orderarray[$keyname] = $valuename; 
    if ( $keyname == 'cod_contenido' )
        $resultdet = mysqli_query($conn, $ordersproducts);
        foreach($resultdet as $keynamedet => $valuenamedet)
            $orderdetail[$keynamedet] = $valuenamedet;
        $orderarray[$keyname] = $valuename;

The make it on Gambas:

In gambas there's quite more complicated due inside the key name, we will have and reference, an the real value will be another object, in php we can access directly no matter if are other king of data, in gambas we must iterate for each object that will put in collection:

orders = "SELECT * FROM almorder WHERE cod_order = '001'";
ordersproducts = "SELECT * FROM alorderproducts WHERE cod_order = '001'";
rset = $conexion.Exec(orders)
For Each rset
    columnas = New Collection(gb.IgnoreCase)
    For Each hField In rset.Fields
        columnas.Add(hField.Name), rset[hField.Name])
        if ( Comp(hField.Name, "cod_contenido") == 0 ) then
            rsetdet = $conexion.Exec(ordersproduts)
            filasdet = New Collection(gb.IgnoreCase)
            For Each rsetdet
                columnasdet = New Collection(gb.IgnoreCase)
                For Each hFieldet In rsetdet.Fields
                    columnasdet.Add(hFieldet.Name), rsetet[hFieldet.Name])
                filasdet.Add(columnaset, rset!cod_contenido)
            columnas.Add(filasdet, "cod_contenido")
    filas.Add(columnas, rset!cod_despacho)

In the mail list the error was stupid, a typo in the line that added the "cod_contenido" object to the main collection object.. was added the same eveer!

mysql to sqlite: COMMENTS error near - SOLUTION

When export or try to run a sql script into sqlite and have the "COMMENTS" keyword usage, sqlite arent able to procesed the script, due stupid developers does not handle support since many years:
LOOK HERE: http://sqlite.1065341.n5.nabble.com/Feature-request-add-support-for-COMMENT-statement-td69401.html

so the solution its to grep and replace (take easy, u can made it in geany of venenux) using a regular expresion:

1) open geany
2) load the script
3) menu->search->replace
4) marks use regular expr
5) the word to search will be COMMENT \'.*\'
6) the word to replace it will be empty
7) pull down avanced "Replace all" and select Document
8) that's all

now at least COMMENT keyword was eliminated from script and sqlite can procesed the sql.

mysql to sybase : ISNULL vs. COALESCE

In MySQL, and also Transact-SQL (Sybase or SQLServer), you can use both ISNULL and COALESCE to use a default in case you have NULL values:
select IFNULL('pepe', 123);

For right interaction with proxy-tables in SAP Sybase, must use better than null simple check:

USE COALESCE: due is ANSI standard. So if you think you might have to port the code to another DBMS, it’s a safer bet. Works as espected in both sybase and mysql, also another stupid like micosoft.

AVOIT ISNULL: due in MySQL returns boolean depends of configuration an version. This boolean if the result are miscinterpreted can be trheated as "0" char and think if zero value. Sybase does not assume "question" behavior, whientime MySQL assumes a "question" like "this fiel are null "or not" and return "true" (yes its null) or false (no ther'n).

The problem resides in that coalesce it for experts...
sybase> select IFNULL('pepe', 123);

mysql> select IFNULL('pepe', 123);

here with coalesce in mysql expected are good:
sybase> select COALESCE('pepe', 123);

mysql> select COALESCE('pepe', 123), ;