Connection | Scripting and security

Crear la base de datos

[void][System.Reflection.Assembly]::LoadWithPartialName("MySql.Data")
$Connection = New-Object MySql.Data.MySqlClient.MySqlConnection
$ConnectionString = "server=" + "localhost" + ";port=3306;uid=" + "root" + ";pwd=" + "root"
$Connection.ConnectionString = $ConnectionString
$Connection.Open()

$Query = 'create database bbc'
$Command = New-Object MySql.Data.MySqlClient.MySqlCommand($Query, $Connection)
$DataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($Command)
$DataSet = New-Object System.Data.DataSet
$RecordCount = $dataAdapter.Fill($dataSet, "data")
$DataSet.Tables[0]

$Connection.Close()

[void][System.Reflection.Assembly]::LoadWithPartialName("MySql.Data")

$Connection = New-Object MySql.Data.MySqlClient.MySqlConnection

$ConnectionString = "server=" + "localhost" + ";port=3306;uid=" + "root" + ";pwd=" + "root"

$Connection.ConnectionString = $ConnectionString

$Connection.Open()

$Query = 'create database bbc'

$Command = New-Object MySql.Data.MySqlClient.MySqlCommand($Query, $Connection)

$DataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($Command)

$DataSet = New-Object System.Data.DataSet

$RecordCount = $dataAdapter.Fill($dataSet, "data")

$DataSet.Tables[0]

$Connection.Close()

Crear e insertar cada noticia en una tabla de la base de datos

[void][System.Reflection.Assembly]::LoadWithPartialName("MySql.Data")
$Connection = New-Object MySql.Data.MySqlClient.MySqlConnection
$ConnectionString = "server=" + "localhost" + ";port=3306;uid=" + "root" + ";pwd=" + "root" +  ";database="+"bbc"
$Connection.ConnectionString = $ConnectionString
$Connection.Open()

$Query = 'create table titulo(titulo varchar(500) primary key)'
$Command = New-Object MySql.Data.MySqlClient.MySqlCommand($Query, $Connection)
$DataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($Command)
$DataSet = New-Object System.Data.DataSet
$RecordCount = $dataAdapter.Fill($dataSet, "data")
$DataSet.Tables[0]

$url='https://www.bbc.com/news/'
$result = Invoke-WebRequest $url
$result.AllElements | Where Class -eq 'gs-c-promo-heading__title gel-pica-bold nw-o-link-split__text' | %{$_.innerText} | %{
$Query = "INSERT INTO titulo (titulo) values (""$_"")"
$Query
$Command = New-Object MySql.Data.MySqlClient.MySqlCommand($Query, $Connection)
$DataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($Command)
$DataSet = New-Object System.Data.DataSet
$RecordCount = $dataAdapter.Fill($dataSet, "data")
$DataSet.Tables[0]
}

$Connection.Close()

[void][System.Reflection.Assembly]::LoadWithPartialName("MySql.Data")

$Connection = New-Object MySql.Data.MySqlClient.MySqlConnection

$ConnectionString = "server=" + "localhost" + ";port=3306;uid=" + "root" + ";pwd=" + "root" + ";database="+"bbc"

$Connection.ConnectionString = $ConnectionString

$Connection.Open()

$Query = 'create table titulo(titulo varchar(500) primary key)'

$Command = New-Object MySql.Data.MySqlClient.MySqlCommand($Query, $Connection)

$DataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($Command)

$DataSet = New-Object System.Data.DataSet

$RecordCount = $dataAdapter.Fill($dataSet, "data")

$DataSet.Tables[0]

$url='https://www.bbc.com/news/'

$result = Invoke-WebRequest $url

$result.AllElements | Where Class -eq 'gs-c-promo-heading__title gel-pica-bold nw-o-link-split__text' | %{$_.innerText} | %{

$Query = "INSERT INTO titulo (titulo) values (""$_"")"

$Query

$Command = New-Object MySql.Data.MySqlClient.MySqlCommand($Query, $Connection)

$DataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($Command)

$DataSet = New-Object System.Data.DataSet

$RecordCount = $dataAdapter.Fill($dataSet, "data")

$DataSet.Tables[0]

}

$Connection.Close()

Realizado por Ignacio Barranquero

Crear una comunicación entre un cliente en PowerShell de Windows y un servidor en Node.js utilizando TCP/IP

02/06/201831/05/2018

Servidor en Node.js

var net = require('net');

var HOST = '127.0.0.1';
var PORT = 6969;

// Create a server instance, and chain the listen function to it
// The function passed to net.createServer() becomes the event handler for the 'connection' event
// The sock object the callback function receives UNIQUE for each connection
net.createServer(function(sock) {
    
    // We have a connection - a socket object is assigned to the connection automatically
    console.log('CONNECTED: ' + sock.remoteAddress +':'+ sock.remotePort);
    
    // Add a 'data' event handler to this instance of socket
    sock.on('data', function(data) {
        
        console.log('DATA ' + sock.remoteAddress + ': ' + data);
        // Write the data back to the socket, the client will receive it as data from the server
        sock.write('You said "' + data + '"');
        
    });
    
    // Add a 'close' event handler to this instance of socket
    sock.on('close', function(data) {
        console.log('CLOSED: ' + sock.remoteAddress +' '+ sock.remotePort);
    });
    
}).listen(PORT, HOST);

console.log('Server listening on ' + HOST +':'+ PORT);

var net = require('net');

var HOST = '127.0.0.1';

var PORT = 6969;

// Create a server instance, and chain the listen function to it

// The function passed to net.createServer() becomes the event handler for the 'connection' event

// The sock object the callback function receives UNIQUE for each connection

net.createServer(function(sock) {

// We have a connection - a socket object is assigned to the connection automatically

console.log('CONNECTED: ' + sock.remoteAddress +':'+ sock.remotePort);

// Add a 'data' event handler to this instance of socket

sock.on('data', function(data) {

console.log('DATA ' + sock.remoteAddress + ': ' + data);

// Write the data back to the socket, the client will receive it as data from the server

sock.write('You said "' + data + '"');

});

// Add a 'close' event handler to this instance of socket

sock.on('close', function(data) {

console.log('CLOSED: ' + sock.remoteAddress +' '+ sock.remotePort);

});

}).listen(PORT, HOST);

console.log('Server listening on ' + HOST +':'+ PORT);

Cliente en PowerShell

##Client
$port=6969
$TcpClient=New-Object System.Net.Sockets.TcpClient([IPAddress]::Loopback, $port)

$GetStream = $TcpClient.GetStream()
$StreamWriter = New-Object System.IO.StreamWriter $GetStream

$positions='Hi'

$StreamWriter.Write($positions)

$StreamWriter.Dispose()
$GetStream.Dispose()
$TcpClient.Dispose()

##Client

$port=6969

$TcpClient=New-Object System.Net.Sockets.TcpClient([IPAddress]::Loopback, $port)

$GetStream = $TcpClient.GetStream()

$StreamWriter = New-Object System.IO.StreamWriter $GetStream

$positions='Hi'

$StreamWriter.Write($positions)

$StreamWriter.Dispose()

$GetStream.Dispose()

$TcpClient.Dispose()

Bash Network

Can DNS speed up your Internet connection? – Hak5 2405

22/04/201819/04/2018

Database PowerShell

Conectar a MySQL desde PowerShell

13/12/201710/12/2017

#Conectar con MySQL

#Cargar el espacio de nombres MySql.Data que proporciona acceso a clases que representan la arquitectura ADO.NET
[void][System.Reflection.Assembly]::LoadWithPartialName("MySql.Data")

#Crear un objeto MySql.Data.MySqlClient.MySqlConnection que representa una conexión abierta a una base de datos del servidor MySQL
#Indicar en el constructor los paramétros necesarios para realizar la conexión: server, port, uid, pwd y database
$Connection = ([MySql.Data.MySqlClient.MySqlConnection]::new("server=" + "localhost" + ";port=3306;uid=" + "root" + ";pwd=" + ";database="+"estado"))
#Ejecutar el método Open para conectar a la base de datos de MySQL
$Connection.Open()

#Hacer una consulta a la base de datos

#Crear un objeto MySql.Data.MySqlClient.MySqlCommand que representa una instrucción SQL para ejecutar contra una base de datos MySQL
#Indicar en el constructor la instrucción SQL para ejecutar y la conexión (realizada en el paso anterior)
$Query = 'Select Estado from Persons'
$Command = [MySql.Data.MySqlClient.MySqlCommand]::new($Query, $Connection)

#Crear un objeto MySql.Data.MySqlClient.MySqlDataAdapter que representa un conjunto de comandos de datos y una conexión de base de datos que se utilizan para llenar un conjunto de datos y actualizar una base de datos MySQL
#Indicar en el constructor el objeto MySql.Data.MySqlClient.MySqlCommand creado anteriormente
$DataAdapter = [MySql.Data.MySqlClient.MySqlDataAdapter]::new($Command)

#Crear un objeto System.Data.DataSet que representa una caché de datos en memoria en donde se almacenan los datos obtenidos del objeto MySql.Data.MySqlClient.MySqlDataAdapter
$DataSet = [System.Data.DataSet]::new()
#Agregar en System.Data.DataSet los datos del objeto MySql.Data.MySqlClient.MySqlDataAdapter
$DataAdapter.Fill($DataSet)
#Mostrar los datos que contiene System.Data.DataSet
$DataSet.Tables

#Cerrar la conexión con MySQL cerrando el objeto MySql.Data.MySqlClient.MySqlConnection
$Connection.Close()

#Conectar con MySQL

#Cargar el espacio de nombres MySql.Data que proporciona acceso a clases que representan la arquitectura ADO.NET

[void][System.Reflection.Assembly]::LoadWithPartialName("MySql.Data")

#Crear un objeto MySql.Data.MySqlClient.MySqlConnection que representa una conexión abierta a una base de datos del servidor MySQL

#Indicar en el constructor los paramétros necesarios para realizar la conexión: server, port, uid, pwd y database

$Connection = ([MySql.Data.MySqlClient.MySqlConnection]::new("server=" + "localhost" + ";port=3306;uid=" + "root" + ";pwd=" + ";database="+"estado"))

#Ejecutar el método Open para conectar a la base de datos de MySQL

$Connection.Open()

#Hacer una consulta a la base de datos

#Crear un objeto MySql.Data.MySqlClient.MySqlCommand que representa una instrucción SQL para ejecutar contra una base de datos MySQL

#Indicar en el constructor la instrucción SQL para ejecutar y la conexión (realizada en el paso anterior)

$Query = 'Select Estado from Persons'

$Command = [MySql.Data.MySqlClient.MySqlCommand]::new($Query, $Connection)

#Crear un objeto MySql.Data.MySqlClient.MySqlDataAdapter que representa un conjunto de comandos de datos y una conexión de base de datos que se utilizan para llenar un conjunto de datos y actualizar una base de datos MySQL

#Indicar en el constructor el objeto MySql.Data.MySqlClient.MySqlCommand creado anteriormente

$DataAdapter = [MySql.Data.MySqlClient.MySqlDataAdapter]::new($Command)

#Crear un objeto System.Data.DataSet que representa una caché de datos en memoria en donde se almacenan los datos obtenidos del objeto MySql.Data.MySqlClient.MySqlDataAdapter

$DataSet = [System.Data.DataSet]::new()

#Agregar en System.Data.DataSet los datos del objeto MySql.Data.MySqlClient.MySqlDataAdapter

$DataAdapter.Fill($DataSet)

#Mostrar los datos que contiene System.Data.DataSet

$DataSet.Tables

#Cerrar la conexión con MySQL cerrando el objeto MySql.Data.MySqlClient.MySqlConnection

$Connection.Close()

Network PowerShell

Changes the network category of a connection profile

10/10/201729/08/2017

$a=Get-NetConnectionProfile -InterfaceIndex 11
$a.NetworkCategory = "Private"
Set-NetConnectionProfile -InputObject $a

$a=Get-NetConnectionProfile -InterfaceIndex 11

$a.NetworkCategory = "Private"

Set-NetConnectionProfile -InputObject $a

Network PowerShell

Gets a connection profile

09/10/201729/08/2017

Get-NetConnectionProfile

1	Get-NetConnectionProfile

Automation PowerShell

Crear un fichero HTML con el contenido de la tabla posts de WordPress con PowerShell

20/09/201709/09/2017

Antes de ejecutar el código en PowerShell es necesario arrancar MySQL Comprobar la base de datos a la que nos vamos a conectar Ejecutar el código para crear el HTML con los datos de la base de datos

#Conexión a MySQL
[void][System.Reflection.Assembly]::LoadWithPartialName("MySql.Data")
$Connection = New-Object MySql.Data.MySqlClient.MySqlConnection
$ConnectionString = "server=" + "localhost" + ";port=3306;uid=" + "root" + ";pwd=" + ";database="+"jesusninoc"
$Connection.ConnectionString = $ConnectionString
$Connection.Open()

#Consulta para obtener título y contenido a la base de datos de Wordpress
$Query='select post_title, post_content from wp_posts where id=5'
$Command = New-Object MySql.Data.MySqlClient.MySqlCommand($Query, $Connection)
$DataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($Command)
$DataSet = New-Object System.Data.DataSet
$RecordCount = $dataAdapter.Fill($dataSet, "data")
$DataSet.Tables[0].post_content

#Para cada elemento de la tabla añadir al documento HMTL
$DataSet.Tables[0] | %{
"<title>"+$_.post_title+"</title>" | Out-File output.html
"<H1>"+$_.post_title+"</H1>" | Out-File output.html -Append
$_.post_content | Out-File output.html -Append
}

#Cerrar conexión a MySQL
$Connection.Close()

#Abrir el PDF creado
Start-Process .\output.html

#Conexión a MySQL

[void][System.Reflection.Assembly]::LoadWithPartialName("MySql.Data")

$Connection = New-Object MySql.Data.MySqlClient.MySqlConnection

$ConnectionString = "server=" + "localhost" + ";port=3306;uid=" + "root" + ";pwd=" + ";database="+"jesusninoc"

$Connection.ConnectionString = $ConnectionString

$Connection.Open()

#Consulta para obtener título y contenido a la base de datos de Wordpress

$Query='select post_title, post_content from wp_posts where id=5'

$Command = New-Object MySql.Data.MySqlClient.MySqlCommand($Query, $Connection)

$DataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($Command)

$DataSet = New-Object System.Data.DataSet

$RecordCount = $dataAdapter.Fill($dataSet, "data")

$DataSet.Tables[0].post_content

#Para cada elemento de la tabla añadir al documento HMTL

$DataSet.Tables[0] | %{

"<title>"+$_.post_title+"</title>" | Out-File output.html

"<H1>"+$_.post_title+"</H1>" | Out-File output.html -Append

$_.post_content | Out-File output.html -Append

}

#Cerrar conexión a MySQL

$Connection.Close()

#Abrir el PDF creado

Start-Process .\output.html

Automation PowerShell

Crear un fichero PDF con el contenido de la tabla posts de WordPress con PowerShell

08/09/201707/09/2017

Antes de ejecutar el código en PowerShell es necesario arrancar MySQL Comprobar la base de datos a la que nos vamos a conectar Ejecutar el código para crear el PDF con los datos de la base de datos

#Conexión a MySQL
[void][System.Reflection.Assembly]::LoadWithPartialName("MySql.Data")
$Connection = New-Object MySql.Data.MySqlClient.MySqlConnection
$ConnectionString = "server=" + "localhost" + ";port=3306;uid=" + "root" + ";pwd=" + ";database="+"jesusninoc"
$Connection.ConnectionString = $ConnectionString
$Connection.Open()

#Consulta para obtener título y contenido a la base de datos de Wordpress
$Query='select post_title, post_content from wp_posts where id=5'
$Command = New-Object MySql.Data.MySqlClient.MySqlCommand($Query, $Connection)
$DataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($Command)
$DataSet = New-Object System.Data.DataSet
$RecordCount = $dataAdapter.Fill($dataSet, "data")
$DataSet.Tables[0].post_content

#Crear el PDF con el título y el contenido
#Descargar https://sourceforge.net/projects/itextsharp/
[System.Reflection.Assembly]::LoadFrom("E:\programas\itextsharp-dll-core\itextsharp.dll")

#Crear el documento PDF
$document=New-Object itextsharp.text.document
$stream=[IO.File]::OpenWrite("output.pdf")
[itextsharp.text.pdf.PdfWriter]::GetInstance($document, $stream)

#Para cada elemento de la tabla añadir al documento PDF
$document.Open()
$DataSet.Tables[0] | %{
$title=New-Object itextsharp.text.Paragraph($_.post_title)
$document.Add($title)
$content=New-Object itextsharp.text.Paragraph($_.post_content)
$document.Add($content)
}

#Cerrar documento PDF
$document.Close()
$stream.Close()

#Cerrar conexión a MySQL
$Connection.Close()

#Abrir el PDF creado
Start-Process .\output.pdf

#Conexión a MySQL

[void][System.Reflection.Assembly]::LoadWithPartialName("MySql.Data")

$Connection = New-Object MySql.Data.MySqlClient.MySqlConnection

$ConnectionString = "server=" + "localhost" + ";port=3306;uid=" + "root" + ";pwd=" + ";database="+"jesusninoc"

$Connection.ConnectionString = $ConnectionString

$Connection.Open()

#Consulta para obtener título y contenido a la base de datos de Wordpress

$Query='select post_title, post_content from wp_posts where id=5'

$Command = New-Object MySql.Data.MySqlClient.MySqlCommand($Query, $Connection)

$DataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($Command)

$DataSet = New-Object System.Data.DataSet

$RecordCount = $dataAdapter.Fill($dataSet, "data")

$DataSet.Tables[0].post_content

#Crear el PDF con el título y el contenido

#Descargar https://sourceforge.net/projects/itextsharp/

[System.Reflection.Assembly]::LoadFrom("E:\programas\itextsharp-dll-core\itextsharp.dll")

#Crear el documento PDF

$document=New-Object itextsharp.text.document

$stream=[IO.File]::OpenWrite("output.pdf")

[itextsharp.text.pdf.PdfWriter]::GetInstance($document, $stream)

#Para cada elemento de la tabla añadir al documento PDF

$document.Open()

$DataSet.Tables[0] | %{

$title=New-Object itextsharp.text.Paragraph($_.post_title)

$document.Add($title)

$content=New-Object itextsharp.text.Paragraph($_.post_content)

$document.Add($content)

}

#Cerrar documento PDF

$document.Close()

$stream.Close()

#Cerrar conexión a MySQL

$Connection.Close()

#Abrir el PDF creado

Start-Process .\output.pdf

PowerShell

Obtener datos de una base de datos y almacenar el contenido en un fichero de texto

07/08/201707/07/2017

#Leer datos de una base de datos y almacenar en un fichero con el nombre del ID de cada registro
[void][System.Reflection.Assembly]::LoadWithPartialName("MySql.Data")
$Connection = New-Object MySql.Data.MySqlClient.MySqlConnection
$ConnectionString = "server=" + "localhost" + ";port=3306;uid=" + "root" + ";pwd=" + ";database="+"gps"
$Connection.ConnectionString = $ConnectionString
$Connection.Open()

$Query='select * from result'
$Command = New-Object MySql.Data.MySqlClient.MySqlCommand($Query, $Connection)
$DataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($Command)
$DataSet = New-Object System.Data.DataSet
$RecordCount = $dataAdapter.Fill($dataSet, "data")
$Connection.Close()

#Almacenar el contenido de cada registro obtenido de la consulta a la base de datos en un fichero
foreach($valor in $DataSet.Tables[0])
{
$valor.ID
$valor | Out-File $valor.id 
}

#Leer datos de una base de datos y almacenar en un fichero con el nombre del ID de cada registro

[void][System.Reflection.Assembly]::LoadWithPartialName("MySql.Data")

$Connection = New-Object MySql.Data.MySqlClient.MySqlConnection

$ConnectionString = "server=" + "localhost" + ";port=3306;uid=" + "root" + ";pwd=" + ";database="+"gps"

$Connection.ConnectionString = $ConnectionString

$Connection.Open()

$Query='select * from result'

$Command = New-Object MySql.Data.MySqlClient.MySqlCommand($Query, $Connection)

$DataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($Command)

$DataSet = New-Object System.Data.DataSet

$RecordCount = $dataAdapter.Fill($dataSet, "data")

$Connection.Close()

#Almacenar el contenido de cada registro obtenido de la consulta a la base de datos en un fichero

foreach($valor in $DataSet.Tables[0])

{

$valor.ID

$valor | Out-File $valor.id

}

Database PowerShell

Ejercicios de PowerShell: crear una base de datos llamada gps y almacenar posiciones GPS

04/08/201707/07/2017

Crear la base de datos gps en MySQL desde PowerShell

[void][System.Reflection.Assembly]::LoadWithPartialName("MySql.Data")
$Connection = New-Object MySql.Data.MySqlClient.MySqlConnection
$ConnectionString = "server=" + "localhost" + ";port=3306;uid=" + "root" + ";pwd=" + ";"
$Connection.ConnectionString = $ConnectionString
$Connection.Open()

$Query = 'create database gps'
$Command = New-Object MySql.Data.MySqlClient.MySqlCommand($Query, $Connection)
$DataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($Command)
$DataSet = New-Object System.Data.DataSet
$RecordCount = $dataAdapter.Fill($dataSet, "data")
$DataSet.Tables[0]
$Connection.Close()

[void][System.Reflection.Assembly]::LoadWithPartialName("MySql.Data")

$Connection = New-Object MySql.Data.MySqlClient.MySqlConnection

$ConnectionString = "server=" + "localhost" + ";port=3306;uid=" + "root" + ";pwd=" + ";"

$Connection.ConnectionString = $ConnectionString

$Connection.Open()

$Query = 'create database gps'

$Command = New-Object MySql.Data.MySqlClient.MySqlCommand($Query, $Connection)

$DataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($Command)

$DataSet = New-Object System.Data.DataSet

$RecordCount = $dataAdapter.Fill($dataSet, "data")

$DataSet.Tables[0]

$Connection.Close()

Crear la tabla result con las columnas: ID, address, lat, lng y date

[void][System.Reflection.Assembly]::LoadWithPartialName("MySql.Data")
$Connection = New-Object MySql.Data.MySqlClient.MySqlConnection
$ConnectionString = "server=" + "localhost" + ";port=3306;uid=" + "root" + ";pwd=" +  ";database="+"gps"
$Connection.ConnectionString = $ConnectionString
$Connection.Open()

$Query = 'create table result(ID int auto_increment primary key, address varchar(50), lat varchar(10), lng varchar(10), date datetime)'
$Command = New-Object MySql.Data.MySqlClient.MySqlCommand($Query, $Connection)
$DataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($Command)
$DataSet = New-Object System.Data.DataSet
$RecordCount = $dataAdapter.Fill($dataSet, "data")
$DataSet.Tables[0]

$Connection.Close()

[void][System.Reflection.Assembly]::LoadWithPartialName("MySql.Data")

$Connection = New-Object MySql.Data.MySqlClient.MySqlConnection

$ConnectionString = "server=" + "localhost" + ";port=3306;uid=" + "root" + ";pwd=" + ";database="+"gps"

$Connection.ConnectionString = $ConnectionString

$Connection.Open()

$Query = 'create table result(ID int auto_increment primary key, address varchar(50), lat varchar(10), lng varchar(10), date datetime)'

$Command = New-Object MySql.Data.MySqlClient.MySqlCommand($Query, $Connection)

$DataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($Command)

$DataSet = New-Object System.Data.DataSet

$RecordCount = $dataAdapter.Fill($dataSet, "data")

$DataSet.Tables[0]

$Connection.Close()

Insertar datos en la tabla gps obtenidos de una consulta a Google Maps Consulta a Google Maps

$calle='SW 4th St, Miami, FL, EE. UU.'
$date=Get-Date
$urlsi="https://maps.googleapis.com/maps/api/geocode/json?address=" + $calle
$result=(Invoke-WebRequest -Uri $urlsi).Content | ConvertFrom-JSON

$calle
$result.results."geometry".location.lat
$result.results."geometry".location.lng
$date

$calle='SW 4th St, Miami, FL, EE. UU.'

$date=Get-Date

$urlsi="https://maps.googleapis.com/maps/api/geocode/json?address=" + $calle

$result=(Invoke-WebRequest -Uri $urlsi).Content | ConvertFrom-JSON

$calle

$result.results."geometry".location.lat

$result.results."geometry".location.lng

$date

Insertar datos en la tabla gps

$calle='SW 4th St, Miami, FL, EE. UU.'
$date=Get-Date
$urlsi="https://maps.googleapis.com/maps/api/geocode/json?address=" + $calle
$result=(Invoke-WebRequest -Uri $urlsi).Content | ConvertFrom-JSON
$date=(Get-Date).Year.tostring()+'-'+(Get-Date).Month.tostring()+'-'+(Get-Date).Day.tostring()+'-'+(Get-Date).Hour.ToString()+'-'+(Get-Date).Minute.ToString()+'-'+(Get-Date).Second.tostring()

$calle
$lat=$result.results."geometry".location.lat
$lng=$result.results."geometry".location.lng
$date

[void][System.Reflection.Assembly]::LoadWithPartialName("MySql.Data")
$Connection = New-Object MySql.Data.MySqlClient.MySqlConnection
$ConnectionString = "server=" + "localhost" + ";port=3306;uid=" + "root" + ";pwd=" +  ";database="+"gps"
$Connection.ConnectionString = $ConnectionString
$Connection.Open()

###################################
#Insertar en tabla result
###################################

$Query = "INSERT INTO result (address, lat, lng, date) values ('"+$calle+"','"+$lat+"','"+$lng+"','"+$date+"')"
$Query
$Command = New-Object MySql.Data.MySqlClient.MySqlCommand($Query, $Connection)
$DataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($Command)
$DataSet = New-Object System.Data.DataSet
$RecordCount = $dataAdapter.Fill($dataSet, "data")
$DataSet.Tables[0]

$Connection.Close()

$calle='SW 4th St, Miami, FL, EE. UU.'

$date=Get-Date

$urlsi="https://maps.googleapis.com/maps/api/geocode/json?address=" + $calle

$result=(Invoke-WebRequest -Uri $urlsi).Content | ConvertFrom-JSON

$date=(Get-Date).Year.tostring()+'-'+(Get-Date).Month.tostring()+'-'+(Get-Date).Day.tostring()+'-'+(Get-Date).Hour.ToString()+'-'+(Get-Date).Minute.ToString()+'-'+(Get-Date).Second.tostring()

$calle

$lat=$result.results."geometry".location.lat

$lng=$result.results."geometry".location.lng

$date

[void][System.Reflection.Assembly]::LoadWithPartialName("MySql.Data")

$Connection = New-Object MySql.Data.MySqlClient.MySqlConnection

$ConnectionString = "server=" + "localhost" + ";port=3306;uid=" + "root" + ";pwd=" + ";database="+"gps"

$Connection.ConnectionString = $ConnectionString

$Connection.Open()

###################################

#Insertar en tabla result

###################################

$Query = "INSERT INTO result (address, lat, lng, date) values ('"+$calle+"','"+$lat+"','"+$lng+"','"+$date+"')"

$Query

$Command = New-Object MySql.Data.MySqlClient.MySqlCommand($Query, $Connection)

$DataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($Command)

$DataSet = New-Object System.Data.DataSet

$RecordCount = $dataAdapter.Fill($dataSet, "data")

$DataSet.Tables[0]

$Connection.Close()

Realizar una consulta a la base de datos gps y mostrar los datos que tiene la tabla result

[void][System.Reflection.Assembly]::LoadWithPartialName("MySql.Data")
$Connection = New-Object MySql.Data.MySqlClient.MySqlConnection
$ConnectionString = "server=" + "localhost" + ";port=3306;uid=" + "root" + ";pwd=" + ";database="+"gps"
$Connection.ConnectionString = $ConnectionString
$Connection.Open()

$Query='select * from result'
$Command = New-Object MySql.Data.MySqlClient.MySqlCommand($Query, $Connection)
$DataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($Command)
$DataSet = New-Object System.Data.DataSet
$RecordCount = $dataAdapter.Fill($dataSet, "data")
$DataSet.Tables[0]
$Connection.Close()

[void][System.Reflection.Assembly]::LoadWithPartialName("MySql.Data")

$Connection = New-Object MySql.Data.MySqlClient.MySqlConnection

$ConnectionString = "server=" + "localhost" + ";port=3306;uid=" + "root" + ";pwd=" + ";database="+"gps"

$Connection.ConnectionString = $ConnectionString

$Connection.Open()

$Query='select * from result'

$Command = New-Object MySql.Data.MySqlClient.MySqlCommand($Query, $Connection)

$DataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($Command)

$DataSet = New-Object System.Data.DataSet

$RecordCount = $dataAdapter.Fill($dataSet, "data")

$DataSet.Tables[0]

$Connection.Close()

Older Posts