Reading data from SQL Server

Since we can't use Data read to connect to an external SQL Server (Post about), with CFv5 i use Custom Code with PHP tags and Joomla JDatabasePDO to make the job.

My server have FreeTDS and ODBC installed to make the PDO connections with SQL Server works.

$con=new PDO("odbc:odbc-name", "$dbuser", "$dbpwd");

With CFv6, how can we make this?

Let's supouse my model is:

Server srv-sql
User appusr
Pass appusr@1
Database clientbase
Tabel Clients(id int,name varchar(100),dateBirth datetime)

Hi Austre,

if you use your own code to read the data, then you can use this code to set it under a variable and use it as a data provider any where:

$this->set("my_data", $data_retrieved);

Best regards,
Max

So, if i put a PHP code at load

include('../libraries/phputil/phputilcon.php');
include('../libraries/phputil/phputil.php');

//This block was tested and no connection erro occurs
$con=new PDO("odbc:siac", "$dbuser", "$dbpwd");
$sql = "exec RecTodosEstados";
$stm=$con->prepare($sql);
$stm->execute();
$res=$stm->fetch(PDO::FETCH_ASSOC);

//Just an exemple, not getting from fetch
$this->set("estado", "Rio Grande do Norte");

At Designer i can put a field text that it will populate with var estado, just putting {var:estado} at value property?

Hi Austre,

Yes, you can also set the var to an array then call it using {var:estado.key.subkey}

But the correct way is to provide the form with an array matching the fields names and call the var in the "Data provider" of the form under the "Display section" action.

Best regards,
Max

This new version is a pain to learn 😶

I se this PHP code after Display Section

$values=array("nome"=>"Austregecilio Cruz Neto");
$this->set("formData", $values);

Under the Display Section, in the Data Provider i set {var:formData}

When i call form my field nome are not filled up.

Got it!

PHP Code have to stay before Display Section, it's obviously, but didn't realized that. Now my field nome was filled up.

Next step it try fo filled up a Dropdown. How could it be? Just an array? Need use some special name at the keys?

Hi Austre,

You mean fill the dropdown with options ?

Best regards,
Max

Yes, fill the dropdown with options.

Your FAQ is assuming that my options are from MySQL, but i'ts not so simple for me.🤣

My options are from SQL Server. I need to know what variable structure (array, array of arrays...) i need mount with my PHP code.

Hi Austre,

I have updated the FAQ.

Best regards,
Max

Hi Max!
Got it, syntax are awesome. I think now things are starting to make sense to me.

But have another doubts.

Imagine that i loading the customer data and i need mark one of the Dropdown options with selected. How could it be made?

Imagine we have two Dropdowns: Country and City. With CFv6 how can we populate City Dropdown based at Country Dropdown selection?

Hi Austre,

There is a setting in the dropdown field to set the selected option, this also accepts a variable shortcode.

In order to change another dropdown you need to reload it, please check this FAQ:
https://www.chronoengine.com/faqs/74-chronoforms6/5299-how-to-reload-a-field.html

Best regards,
Max

In order to change another dropdown you need to reload it, please check this FAQ:
https://www.chronoengine.com/faqs/74-chronoforms6/5299-how-to-reload-a-field.html

I set the Reload Event at my field Estado, point to field cidade


I create a PHP function before Display Section named getCidade, with code:

$estado=21;
$options=array();

if($estado!=0){
	$con=new PDO("odbc:siac", "$dbuser", "$dbpwd");
	$sql="exec RecTodasCidades $estado";
	$stm=$con->prepare($sql);
	$stm->execute();
	$res=$stm->fetchAll(PDO::FETCH_ASSOC);
	
	if(count($res)){
		foreach($res as $row){
			$id=$row["codcid"];
			$desc=$row["DescCid"];
			$options[$id]=$desc;
		}
	}
}

return $options;

Create an event named reloadCidade.
[IMG]http://i68.tinypic.com/dr5a1c.jpg[/IMG]

Configured field cidade to call the evento
[IMG]http://i67.tinypic.com/2rf7i3s.jpg[/IMG]

Configured field cidade to call the PHP function
[attachment=81582_20170530122440_cfv604-jpg.jpg][/attachment]

When i change field Estado, field Cidade comes to reload, but not stop. When push F5, i got logged out and error below appear at server log.

PHP Warning: session_start(): Failed to decode session object. Session has been destroyed in /mysite/libraries/joomla/session/handler/native.php on line 260, referer: http://mydomain/administrator/index.php?option=com_chronoforms6&cont=manager&chronoform=cadastro

Hi Austre,

The new event name should not have capital letters, please try that.

You should also use a "Custom code" action to call the view, but not a php action.

Best regards,
Max

Hi Max,

I create the event again with no capital letters and Custom Code now. I let my PHP code getCidade empty for take sure it isn't the cause and so.

Error not occurs! Now i can see the field reloading with no erros.

Until here i understand. Now i need to know how can i populate my Dropdown cidade based on Dropdown Estado option selected.

I don't know if my PHP code goes on Custom Code reloadcidade, returning an array or json, or need to crear an PHP code before Display Section and call it at Dropdown cidade config option.

Hi Austre,

If your reloaded field is using a variable in the options box then you must have this variable available in the new event before the view is called.

And since you get the variable from a PHP function, then you can simply call it before the view is called inside the custom code action:

{fn:getCidade}

Best regards,
Max

My Dropdown Estado is like that:
[attachment=81582_20170531113747_cfv601-jpg.jpg][/attachment]
[attachment=81582_20170531114030_cfv602-jpg.jpg][/attachment]

That's my PHP function that populate Dropdown Estado, it's working fine:

include('../libraries/phputil/phputilcon.php');
include('../libraries/phputil/phputil.php');

$con=new PDO("odbc:myodbc", "$dbuser", "$dbpwd");
$sql = "exec RecTodosEstados";
$stm=$con->prepare($sql);
$stm->execute();
$res=$stm->fetchAll(PDO::FETCH_ASSOC);

$options=array();

if(count($res)){
	foreach($res as $row){
		$id=$row["CodEst"];
		$desc=$row["AbrevEst"];
		$options[$id]=$desc;
	}
}

return $options;

My Dropdown Cidade is like that:
[attachment=81582_20170531114421_cfv603-jpg.jpg][/attachment]
[attachment=81582_20170531114440_cfv604-jpg.jpg][/attachment]

And that's my Reload Event named reloadcidade:
[attachment=81582_20170531114519_cfv605-jpg.jpg][/attachment]

With browser Developer Tools i can see the event called and parameter estado.
[attachment=81582_20170531115025_cfv606-jpg.jpg][/attachment]

At my Reload Event Custom Code, i can get the parameter estado with this code?

$this->get("estado");

How can i call the PHP function getCidade sending the parameter estado?

{fn:getCidade:$estado}

Hi Austre,

In the new event you can call your PHP function and pass the estado request data as a variable this way:

{fn:getCidade$estado=(data:estado)}

Then you can get the estado in the PHP function using: $this->get("estado");

Best regards,
Max

Hi Max!

I got the syntax, code run with no error at browser or apache log. But i still can't populate the Dropdown Cidade, i don't know if this way we are discussing is the best to populate a Dropdown based on another Dropdown selected value.

Just to exemplifly, in CFv5 i have

Event getCidade (PHP)

$estado = $form->data["estado"];
$cidade = $form->data["cidade"];
$return = "";
$selected = "";

if($estado!=0){
	$con=new PDO("odbc:myodbc", "$dbuser", "$dbpwd");
	$sql = "exec RecTodasCidades $estado";
	$stm=$con->prepare($sql);
	$stm->execute();
	$res=$stm->fetchAll(PDO::FETCH_ASSOC);
	
	if(count($res)){
		foreach($res as $row){
			if($row['codcid'] == $cidade)
				$selected = "selected";
			else
				$selected = "";
			$return .= "<option value='".$row['codcid']."' $selected>".utf8_encode($row['DescCid'])."</option>";
		}
	}
}

echo $return;

At JS i have the Change Event bind to Dropdown Estado:

jQuery('#estado').change(function(){
estado = jQuery('#estado').val();
cidade = 0;
getCidade(estado,cidade);
});

And the JS function getCidade that calls the Chronoforms Event getCidade

function getCidade(estado,cidade){
	var formName = "cadastro";
	var eventName = "getCidade";
	var url = "index.php?option=com_chronoforms5&chronoform="+formName+"&event="+eventName+"&tvout=ajax";
	
	jQuery.ajax({
		url:url,
		type:'get',
		data:{'estado':estado,'cidade':cidade},
  		dataType: 'html',
		success:function(res){
			jQuery('#cidade option:gt(0)').remove();
			jQuery('#cidade').append(res);
		},
		error:function(e){
		}
	});
}

Hi Austre,

You do not need this custom code, the form should be doing that for you.

Could you please try to follow the FAQ carefully ? if the field gets reloaded then you are almost done, you just need to have the variable which sets the options available at the point just before the field is rebuilt.

Best regards,
Max