max-ieremenko/sqldatabase

Command-line tool and PowerShell module for MSSQL Server and PostgreSQL.

sqldatabase's Issues

postgresql support

Type [System.DateTimeOffset] is not supported

Hello @max-ieremenko,

I started to use your cool library for exporting data from MSSQL database, however I got into this error in tables using DateTimeOffset column.

Microsoft SQL Server 2017 (RTM-GDR) (KB4505224) - 14.0.2027.2 (X64)
        Jun 15 2019 00:26:19
        Copyright (C) 2017 Microsoft Corporation
        Express Edition (64-bit) on Windows 10 Pro 10.0 <X64> (Build 19041: ) (Hypervisor)

export from1.sql ...
Type [System.DateTimeOffset] is not supported.
   at SqlDatabase.Export.SqlWriter.Value(Object value) in D:\Work\SqlDatabase\Sources\SqlDatabase\Export\SqlWriter.cs:line 145
   at SqlDatabase.Export.DataExporter.Export(IDataReader source, String tableName) in D:\Work\SqlDatabase\Sources\SqlDatabase\Export\DataExporter.cs:line 47
   at SqlDatabase.Commands.DatabaseExportCommand.ExportScript(IDataExporter exporter, IScript script, Int32& readerIndex) in D:\Work\SqlDatabase\Sources\SqlDatabase\Commands\DatabaseExportCommand.cs:line 90
   at SqlDatabase.Commands.DatabaseExportCommand.ExecuteCore() in D:\Work\SqlDatabase\Sources\SqlDatabase\Commands\DatabaseExportCommand.cs:line 44
   at SqlDatabase.Commands.DatabaseCommandBase.Execute() in D:\Work\SqlDatabase\Sources\SqlDatabase\Commands\DatabaseCommandBase.cs:line 21
   at SqlDatabase.Program.ExecuteCommand(ICommandLine cmd, ILogger logger) in D:\Work\SqlDatabase\Sources\SqlDatabase\Program.cs:line 47

netcoreapp2.1 out of support

In the next version 4.1.0 of SqlDatabase.GlobalTool netcoreapp2.1 will be removed.

Log file option

For all commands add optional parameter -log "path\to\log\file.log"

$ SqlDatabase execute|create|upgrade|export -log=c:\log.txt

Expectation is to see output in c:\log.txt

.ps1 scripts support

Implement powershell (.ps1) scripts support for commands execute, create and upgrade, like .dll

For example

$ SqlDatabase execute ^
      "-database=Data Source=server;Initial Catalog=database;Integrated Security=True" ^
      -from=c:\Scripts\script.ps1 ^
      -varTableName=dbo.DemoTable

PS> Execute-SqlDatabase `
      -database "Data Source=server;Initial Catalog=database;Integrated Security=True" `
      -from c:\Scripts\script.ps1 `
      -var TableName=dbo.DemoTable `
      -InformationAction Continue

where script.ps1

[CmdletBinding(SupportsShouldProcess=$true)] # indicates that the script implementation supports -WhatIf scenario
param (
    $Command, # instance of SqlCommand, $null in case -WhatIf
    $Variables # access to variables
)

if (-not $Variables.TableName) {
    throw "Variable TableName is not defined."
}

if ($WhatIfPreference) {
    # handle -WhatIf scenario
    return
}

Write-Information "start execution"

$Command.CommandText = ("print 'current database name is {0}'" -f $Variables.DatabaseName)
$Command.ExecuteNonQuery()

$Command.CommandText = ("drop table {0}" -f $Variables.TableName)
$Command.ExecuteNonQuery()

Write-Information "finish execution"

SqlDatabase.GlobalTool net6.0

Handle dataselection in .sql scripts

example scripts.sql

CREATE TABLE dbo.Demo(Id INT, Name NVARCHAR(20))
GO

INSERT INTO dbo.Demo VALUES(1, 'name 1')
INSERT INTO dbo.Demo VALUES(2, 'name 2')
GO

PRINT 'dbo.Demo content'
SELECT * FROM dbo.Demo

run script

$ SqlDatabase execute|create|upgrade -from=c:\Scripts\script.sql ...

Expectation is to see in the log output values, provided by "SELECT * FROM dbo.Demo"

dbo.Demo content // already supported
1, name 1
2, name 2

Any options how to suppress output from Execute-SqlDatabase or run silently?

I am developing some pester scripts and I want to suppress the output of the Execute-SqlDatabase function. Any ideas on how to do that? I am running the following Powershell

$PSVersionTable.PSVersion
Major Minor Build Revision

---

5 1 17763 316

Script 1.7.6 SqlDatabase {Invoke-SqlDatabase, New-SqlDatabase, Update-SqlDatabase, Create-SqlDatabase...}

mysql support

Powershell: Directory not found.

PS> cd "c:\my-scripts"
PS> New-SqlDatabase -Database ... -from .\new\
Directory C:\Program Files\PowerShell\7\.\new not found.

Expected:
New-SqlDatabase runs scripts from c:\my-scripts\new folder