[medoman]

hello there
i'm creating a dynaform with in my company, the user will input the client number in a field with a variable named (client_code) then i want to fill some fields in the same form with the client information data from Oracle database, however the sql query always return error , and here is the sql statement i'm using to get the client name :

select distinct t.Name from imcit_clients_bi t where t.imc_code = @@client_code

when i replace the (@@client_code) in the statement with any client code number it returns the company name so that i know that the select statement is working.
please help me solving this problem.
thanks

[michaeltimoteo]

Hi,

Try to open the debug mode by going to the designer and right click the process and "enable debug mode" upon activating it create a case and you will be able to see the variables assigned.

Also try if doing var_dump or print works

[amosbatto]

Another field in your DynaForm needs to have the ID "client_code". (It must be the ID of the field.)

If the field is a textbox, textarea or hidden, then you can return one field. If any other type of field, then you need to return two fields in your query:
select distinct Name, Name from imcit_clients_bi where imc_code = @@client_code

If that isn't the problem, then as Timoteo suggested, create a trigger like this to test it:

Code: Select all

$db = 'XXXXXXXXXXXXXXXXXXXX'; //set to the ID of your DB connection
$res = executeQuery("select Name from imcit_clients_bi", $db);
print "<pre>"; var_dump($res); die;

Finally, make sure that the case of the field and table names is correct.

[medoman]

thanks for your replies, but also nothing works, i'm sure that the sql statement is right because when i write the follwing query:

select distinct t.Name from imcit_clients_bi t where t.imc_code = 'A-0293-CL-EG'

it returns the company name from the oracle database, but when i change it to

select distinct t.Name from imcit_clients_bi t where t.imc_code = @@client_code

it's not working, even when i put it between ("") or ('') nothing it working i have version 3.0.8.1-community.

please help

[michaeltimoteo]

Hi,

Have you enabled your debug mode?

Try this code

Code: Select all

@@test_query = "select distinct t.Name from imcit_clients_bi t where t.imc_code = @@client_code";

Then create a case and see on the debug mode the value of @@test_query; if @@test_query returns select distinct t.Name from imcit_clients_bi t where t.imc_code = 'A-0293-CL-EG' and still returns null value then there must have been a connection problem to your database connection, try to check your database connection via clicking "database connection" on the designer and perform a test connection. Also set the charset of your Oracle DB to utf8.

Best Regards,
Michael

[medoman]

i got this error when i copied the code in my process i have tried with (@@textVar) variable

Untitled.jpg (213.54 KiB) Viewed 6960 times

[michaeltimoteo]

Hi,

That error says you are using double quote instead of a single quote. How about

Code: Select all

@@test_query = "select distinct t.Name from imcit_clients_bi t where t.imc_code = '@@client_code'";

I inserted a single quote before t.imc_code = ; the reason why I want you to add that code is to check in your debug mode if the string is parsing correctly for your sql command.

[medoman]

sorry, but where i should put this code?
i'm putting it in the text field sql but both lines give me the same error as the picture above

[michaeltimoteo]

Hi medoman,

Create a trigger and add that code, then place the trigger before dynaform loads.

Check the debug mode of the result of @@test_query

[medoman]

Untitled1.jpg (60.89 KiB) Viewed 6957 times

this is the result

[michaeltimoteo]

Hi,

That means your query is wrong and cannot parse correctly the @@client_code because it's array. Try

Code: Select all

@@test_query = "select distinct t.Name from imcit_clients_bi t where t.imc_code = '@@client_code[1]'";

if the @@test_query returns a proper query string like

Code: Select all

@@test_query = "select distinct t.Name from imcit_clients_bi t where t.imc_code = 'A-0293-CL-EG'";

then that will work fine

[amosbatto]

It looks like you are trying to do a query with a checkgroup as the independent field (id=client_code), which won't work because checkgroups can have zero, one or multiple values, which is why the value is stored as an array.

You need to use a dropdown, suggest or radio button as your independent field in the DynaForm. Then use this SQL query in the dependent field in your DynaForm:

Code: Select all

select distinct t.Name from imcit_clients_bi t where t.imc_code = '@#client_code'

You need to use single quotes to enclose your strings, because Oracle uses double quotation marks enclose identifiers (like table and field names).

If you want to test this in a trigger, you need to do it this way:

Code: Select all

@@test_query = "select distinct t.Name from imcit_clients_bi t where t.imc_code = '" .@@client_code."'";

[medoman]

ok, here is a small light coming from the end of the tunnel, i put this code in the trigger
$db = '441925048582dae0acadcd4011183523';
@@test_query = executeQuery ("select distinct t.Name from imcit_clients_bi t where t.imc_code = '" .@@clinet_code."'", $db);
as shown in the debuging i can see in the @@test_query value (Alexandria Buisinessmens Association (ABA)) and this is the equivalent value for the code i entered in the client code field... however i need to assign this value to a field i found (object.Object ) as shown in the pic.

Untitled2.jpg (134.7 KiB) Viewed 6947 times

[medoman]

OMG finally worked .... thanks yo so much Dear friends.... this is the way i ran it:

1- created a trigger with the code:-
$db = '441925048582dae0acadcd4011183523';
@@test_query = executeQuery ("select distinct t.Name from imcit_clients_bi t where t.imc_code = '" .@@clinet_code."'", $db);
@@test_query1 = @@test_query[1]['NAME'];

2- assigned the variable @@test_query1 to the field (company name)

thanks so much and i think i will be back again with a new problem