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)
Did you ever find a solution. I'm stuck for the last 2 days.
I need me MIT App Inventor App to connect directly to a MS SQL Server.
Carlos
Do you mean without using php ?
You will need some sort of intermediary to connect and query data...
Explain what you are trying to achieve.
I created an API in asp hosted at Goddady and i get TLS security err.
Then I tried php script from Digital Ocean (I think GoDaddy doesn't allow connections to external databases) and i get the same.
So i was thinking maybe and can try directly from the App I am creating.
Carlos
Have you tried this:
I use some 5 extensions from him.
The App i'm creating already talks to my asp and php scripts and work great with my MySQL DB but now i need to connect to my customer's Live DB in MS SQL and that's where i have Security issued (TLS).
Taifun's DB Ext is the same as mine: Connects to an API endpoint created by us. I was wondering if there is an Ext to connect App directly to MS SQL.
This might help
Taifun
Thank you Taifun. I did create an API for my MIT App to POST to. My API works great with MySQL but the Customer is MS SQL.
I have created and asp (GoDaddy) and a php (Digital Ocean) scripts but both fail due to some security issue TLS related. I was hopping to try from my MIT App directly to MS SQL but looks like API is the only way, which I already have.
Currently working on adding MS OLE DB extension to my php Server so see if that solves the problem. Customer has to fix his security issue on his side as well.
Thanks again Taifun
Carlos Bieberach
Miami, Florida USA