If I want to use App Inventor App connect MSSQL ,how to I do?
HI
First of all you need to have an active web server (i use IIS) with PHP manager and MSSQL drivers installed (hardest part).
This way you can reach the PHP files that does the queries.
Blocks to perform query:
PHP script:
<?php
//Get data
$dati=$_GET;
$Articolo=str_replace("?","%", $dati['Campo1'])."%";
$Descr=str_replace("?","%", $dati['Campo2'])."%";
$Vecchio=str_replace("?","%", $dati['Campo3'])."%";
//SQL Server connection
$serverName = "192.168.5.40\PANTH01";
$connectionInfo = array("Database"=>"PANTH01","Uid"=>"username", "PWD"=>"password");
$conn = sqlsrv_connect($serverName, $connectionInfo);
//Test
if( $conn ){}else{
echo "bad connection.<br />";
die( print_r( sqlsrv_errors(), true));
}
//headers
$Csv_tot= "ARTICOLO;DESCRIZIONE;COD M,";
//Query
$tsql= "SELECT TOP(100) * FROM EUROMECC.Y_APP_CERCA_ARTICOLI WHERE ID_ARTICOLO LIKE '$Articolo' AND UPPER(VECCHIO_ARTIC) LIKE '$Vecchio' AND UPPER(DESCR_ESTESA) LIKE '$Descr'";
//Excecute query
$getResults= sqlsrv_query($conn, $tsql, array(), array( "Scrollable" => 'keyset' ));
if ($getResults == FALSE) echo(sqlsrv_errors());
$remove = array("\n", "\r\n", "\r", "<p>", "</p>", "<h1>", "</h1>", "<br>", "<br />", "<br/>");
//Data
while ($row = sqlsrv_fetch_array($getResults, SQLSRV_FETCH_BOTH)) {
$Row_singola=($row[0]. ";" .str_replace(",",".",str_replace($remove,"", $row[1])). ";".str_replace(",",".",str_replace($remove,"", $row[2])). ",");
$Csv_tot=$Csv_tot . $Row_singola;
}
sqlsrv_free_stmt($getResults);
$Csv_tot=substr($Csv_tot,0,-1);
echo $Csv_tot;
sqlsrv_close($conn);
?>
Receive data from web srtinga,Connect to MSSQL database,Check connection, initialize table with headers,run query.
The &remove is to replace in the text by possible special characters that can drive the tables crazy.
I use ; to separate cells an , to separate rows but it's up to you. (Automatic Excel import if needed)
so I also replaced , with . in text fields.
Concatenate all cells and remove the last comma:
$Csv_tot=substr($Csv_tot,0,-1);
Send to WEB the result:
echo $Csv_tot;
Don't forget to release the data and close the connection:
sqlsrv_free_stmt($getResults);
sqlsrv_close($conn);
Blocks to get data:
I use TableView V4 extension by @KEN and this is the results:
This is another example of PHP whit certain input:
<?php
//Ricevi i dati
$dati=$_GET;
$Anno=$dati['ANNO'];
$Numero_OE=$dati['OE'];
//SQL SRV connection
$serverName = "192.168.5.40\PANTH01";
$connectionInfo = array("Database"=>"PANTH01","Uid"=>"user", "PWD"=>"password");
$conn = sqlsrv_connect($serverName, $connectionInfo);
//Test
if( $conn ){}else{
echo "Errore connessione.<br />";
die( print_r( sqlsrv_errors(), true));
}
//Headers
$Csv_tot= "COMMESSA;CLIENTE;ARTICOLO;DATA OE;DATA INIZIO;DESCRIZIONE;DESCRIZIONE RIDOTTA;DATA FINE;STATO,";
//Query
$tsql= "SELECT TOP(1) * FROM EUROMECC.Y_APP_OE WHERE ID_ANNO_ORD='$Anno' AND ID_NUMERO_ORD='$Numero_OE'";
//Execute query
$getResults= sqlsrv_query($conn, $tsql, array(), array( "Scrollable" => 'keyset' ));
if ($getResults == FALSE) echo(sqlsrv_errors());
$remove = array("\n", "\r\n", "\r", "<p>", "</p>", "<h1>", "</h1>", "<br>", "<br />", "<br/>");
//Data
while ($row = sqlsrv_fetch_array($getResults, SQLSRV_FETCH_BOTH)) {
$Row_singola=($row[2].";".$row[3]. ";".$row[4]. ";".date_format($row[5], 'd-m-Y').";".str_replace("01-01-1753",NULL, date_format($row[6], 'd-m-Y')).";".str_replace(",",".",str_replace($remove,"", $row[7])). ";".str_replace(",",".",str_replace($remove,"", $row[8])).";".str_replace("31-12-9999",NULL, date_format($row[9], 'd-m-Y')). ";".$row[10]. ";");
$Csv_tot=$Csv_tot . $Row_singola;
}
sqlsrv_free_stmt($getResults);
$Csv_tot=substr($Csv_tot,0,-1);
echo $Csv_tot;
sqlsrv_close($conn);
?>
Pay attention to the dates and use:
date_format($row[5], 'd-m-Y')
I hope to be helpful
5 Likes
(added to FAQ)