[Deprecated] DMZ 1.6.2 (DataMapper OverZealous Edition)

[eluser]OverZealous[/eluser]
@mrtavo
First, (I'm sure this is a typo in the example only) your example has unbalanced parentheses on the third line.

Ignoring that, what error, specifically, are you getting? What does the generated query look like?

It should work, but without more information I can't really help much. :-(

(Another option, in the short run, is to look at defining a custom SQL function. That's especially useful in more complex cases such as this. Usually done with CREATE FUNCTION on most DB servers.)

[eluser]mrtavo[/eluser]
Well yes, that was a typing error; i'm sorry about it.

I'm sorry for giving you no examples on generated code too. Here is one.

Don't worry about field names or join names, they are not relevant on this case.

Just look after the strong text in both querys. I don't know why it's doing that...

SELECT `A`.*, `B`.`city` AS B_city, `B`.`country` AS B_country, `B`.`t` AS B_t, FUNCTION_1(`A`.`arrival_date`, FUNCTION_2(`B`.`t`, `'4'`, `'0'`, `':')`, `'+00:00')` AS arrival_GMT_0 FROM (`trip_step`) LEFT OUTER JOIN `B` as B ON `B`.`id` = `A`.`B_id`

It should be...

SELECT `A`.*, `B`.`city` AS B_city, `B`.`country` AS B_country, `B`.`t` AS B_t,
FUNCTION_1(`A`.`a_d`, FUNCTION_2(`B`.`t`, '4', '0', ':'), '+00:00') AS arrival_GMT_0 FROM (`A`) LEFT OUTER JOIN `B` as B ON `B`.`id` = `A`.`B_id`

an that one works.

It seems to work fine with 1 parameter, after the first one it start to do something with ' and `.

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' `'+00:00')` AS arrival_GMT_0 FROM (`A`) LEFT OUTER JOIN `B` as Bi' at line 1

It's doing something extrange with function closure.

Thanks again!

[eluser]mrtavo[/eluser]
This is the definition code with DMZ of that part.

Code:

$trip_s->select_func(
        'FUNCTION_1', '@arrival_date',
    array('FUNCTION_2' => array( '@B/t', '4', '0', ':')),
    '+00:00',
    'arrival_GMT_0'
);

[eluser]OverZealous[/eluser]
At first I though it looks like it's CI's escaping mechanism getting in the way again.

Now I'm wondering if my example is wrong. Try this instead:

Code:

$trip_s->select_func(
    'FUNCTION_1',
    '@arrival_date',
    'FUNCTION_2' => array( '@B/t', '4', '0', ':'),
    '+00:00',
    'arrival_GMT_0'
);

Just to see if that fixes it.

[eluser]mrtavo[/eluser]
Gives this error on Line "FUNCTION_2" => array(...),

Parse error: syntax error, unexpected T_DOUBLE_ARROW

I think I can't use that kind of asignation, in a non array.

[eluser]OverZealous[/eluser]
Doh - I'm tired! I've been upgrading my main workstation to Snow Leopard..., and it's now 7AM.

Well, I'll keep thinking about it.

[eluser]mrtavo[/eluser]
Maybe... that function only works with one parameter ?

If thats the problem... then I should try to create a full query.

[Edit]
Thanks for your effort, I'll be careful if you have any solution.

[eluser]Conerck[/eluser]
[quote author="OverZealous" date="1265133095"]Well, I'll keep thinking about it.[/quote]

I just ran into the same problem as mrtavo and it seems to apply to all _func() calls where the SQL Function has more than 1 parameter.

I tracked the problem down to CodeIgniter's ActiveRecord. In the select() function CI splits the passed select arguement with

Code:

$select = explode(',', $select)

(DB_active_record.php @ l 83)
which hacks up the multi-argument SQL function into seperate select fields as far as CI is concerned (This is kinda ugly, but not exactly the source of the problem).
Then when you call get() later, CI goes ahead and calls protect_identifiers on every field again (in _compile_select(), l. 1511), which causes the Syntax errors.
So, the problem is that protect_identifers is only temporarily disabled by DMZ during the select_func() call, it is turned back on before get() is called.
You'd have to keep protect_identifiers set to FALSE until after the query has been completed.

Since I don't know the inner workings of DMZ too well I didn't attempt to make a patch for it, since I don't know what side effects this could cause, but I hope I could help you with tracking down the problem.

[eluser]OverZealous[/eluser]
Thank you for tracking that down. Sadly, that's about what I expected to find.

CodeIgniter's often unnecessary and aggressive "identifier protection" has caused me more headaches than it ever has done good.

The way it insists on splitting values with commas is my other headache. I don't understand why they insist on parsing the SQL string when you need a full lexer to really do any good. (I've asked then to write the code to ignore any strings that contain open parentheses, which would allow a developer to override the protections whenever they choose. The select methods should also listen to this.)

The problem with turning off protect-identifiers for the duration is that stops it from protecting even when you might want it to. I'll look at adding (yet another) flag that tells me to set protect_identifiers to FALSE just before compiling the SQL query.

I'm tempted to completely stop using CodeIgniter's ActiveRecord, and just replicate those components manually in DMZ.

[eluser]mrtavo[/eluser]
Thanks for the interest on this problem.

I don't know very well how DMZ works, I get a little bit dizzy reading the code; I haven't got enough php knoledge to patch it by my self.

Please, keep me informed on any change because I would like to use this functionality instead of typing a plain query (I'll patch my code to that...).

Thanks again and good luck!