Как провильно передать CommandText?

Question

[Evgeny Kotov]

Доброго времени суток. Немогу разобраться с синтаксисом запросса на импорт в базы данных.

База данных MySql установленна локально. Если я произвожу все действия руками все работает.

Проблемма в кавычках, Powershell ругается что не все ковычки и апострофы закрыты.

Выражение MySql

LOAD DATA LOCAL INFILE '"$Путь к файлу"' INTO TABLE `bga` FIELDS ENCLOSED BY '"' TERMINATED BY ';' LINES TERMINATED BY '\n' IGNORE 1 LINES;

Код программы:

[void][system.reflection.Assembly]::LoadWithPartialName("MySql.Data")
$dir = 'C:\Temp\1'
$dir2 = 'C:\Temp\2'
$dbusername = 'root' 
$dbpassword = '123456' 
$dbname = 'curamed' 
$MySQLHost = 'localhost'


$connStr ="server=localhost;database=" + $dbname + ";Persist Security Info=false;user id=" + $dbusername + ";pwd=" + $dbpassword + ";"
$conn = New-Object MySql.Data.MySqlClient.MySqlConnection($connStr)
$conn.Open()


foreach ($i in ls -name $dir\*.csv)
{
	Write-Host "$i"
	$file_content = Get-Content "$dir\$i";
	[System.IO.File]::WriteAllLines("$dir2\$i", $file_content);
	Remove-Item "$dir\$i"
	conn.CommandText = "LOAD DATA LOCAL INFILE '"$dir2\$i"' INTO TABLE `bga` FIELDS ENCLOSED BY '"' TERMINATED BY ';' LINES TERMINATED BY '\n' IGNORE 1 LINES;"
	$conn.ExecuteNonQuery()
}

$conn.Close()

За другие элегантные решения моей проблеммы буду благодарен.

Знания Powershell поверхностные...

Код ошибки правда на немецком.

Die Zeichenfolge hat kein Abschlusszeichen: '.
    + CategoryInfo          : ParserError: (:) [], ParentContainsErrorRecordException
    + FullyQualifiedErrorId : TerminatorExpectedAtEndOfString

Comments

[Shoolcs]

Вы за экранирования спец-символов случайно не забыли ?

Answer 1

[azarij]

а так?
conn.CommandText = "LOAD DATA LOCAL INFILE '$dir2\$i' INTO TABLE `bga` FIELDS ENCLOSED BY '`"' TERMINATED BY ';' LINES TERMINATED BY '\n' IGNORE 1 LINES;"

да, а нужны ли обратные кавычки вокруг bga?

Comments

[Evgeny Kotov]

Текст запроса был сгенерирован HeidiSQL и опробириван в консоли

Парсер больше не ругается, но походу так оно тоже не работает.
Выдает следующую ошибку:

Die Eigenschaft "CommandText" wurde für dieses Objekt nicht gefunden. Vergewissern Sie sich, dass die Eigenschaft vorhanden ist und festgelegt werden kann.
In Zeile:21 Zeichen:2
+     $conn.CommandText = "LOAD DATA LOCAL INFILE '$dir2\$i' INTO TABLE ...
+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : PropertyAssignmentException
 
Fehler beim Aufrufen der Methode, da [MySql.Data.MySqlClient.MySqlConnection] keine Methode mit dem Namen "ExecuteNonQuery" enthält.
In Zeile:23 Zeichen:5
+     $conn.ExecuteNonQuery()
+     ~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : MethodNotFound

[Evgeny Kotov]

На данный момент нашел статью с примерами надо разбираться
https://vwiki.co.uk/MySQL_and_PowerShell

[Evgeny Kotov]

Переделал по примеру но наверное что то неучел так как нет соединения с базой

# Connection Variables 
$user = 'root' 
$pass = '123456' 
$database = 'curamed' 
$MySQLHost = 'localhost' 
$dir = 'C:\Temp\1'
$dir2 = 'C:\Temp\2'

function Connect-MySQL([string]$user,[string]$pass,[string]$MySQLHost,[string]$database) { 
  # Load MySQL .NET Connector Objects 
  [void][system.reflection.Assembly]::LoadWithPartialName("MySql.Data") 

  # Open Connection 
  $connStr = "server=" + $MySQLHost + ";port=3306;uid=" + $user + ";pwd=" + $pass + ";database="+$database+";Pooling=FALSE" 
  $conn = New-Object MySql.Data.MySqlClient.MySqlConnection($connStr) 
 try {
        $conn = New-Object MySql.Data.MySqlClient.MySqlConnection($connStr) 
        $conn.Open()
    } catch [System.Management.Automation.PSArgumentException] {
        Log "Unable to connect to MySQL server, do you have the MySQL connector installed..?"
        Log $_
        Exit
    } catch {
        Log "Unable to connect to MySQL server..."
        Log $_.Exception.GetType().FullName
        Log $_.Exception.Message
        exit
    }
    Log "Connected to MySQL database $MySQLHost\$database"

    return $conn 

} 

function Disconnect-MySQL($conn) {
  $conn.Close()
}

function Execute-MySQLNonQuery($conn, [string]$query) { 
  $command = $conn.CreateCommand()                  # Create command object
  $command.CommandText = $query                     # Load query into object
  $RowsInserted = $command.ExecuteNonQuery()        # Execute command
  $command.Dispose()                                # Dispose of command object
  if ($RowsInserted) { 
    return $RowInserted 
  } else { 
    return $false 
  } 
} 

foreach ($i in ls -name $dir\*.csv)
{
	Write-Host "$i"
	$file_content = Get-Content "$dir\$i";
	[System.IO.File]::WriteAllLines("$dir2\$i", $file_content);
	Remove-Item "$dir\$i"
# So, to insert records into a table 
    $query = "LOAD DATA LOCAL INFILE '$dir2\$i' INTO TABLE `bga` FIELDS ENCLOSED BY '`"' TERMINATED BY ';' LINES TERMINATED BY '\n' IGNORE 1 LINES;"
    $Rows = Execute-MySQLNonQuery $conn $query 
    Write-Host $Rows " inserted into database"
}

[azarij]

так вы же и не вызываете Connect-MySQL функцию.

что будет если перед foreach вставить:

Connect-MySQL -user $user -pass $pass -mysqlhost localhost -database $database

[Evgeny Kotov]

azarij, 2 Ошибки одна незначительная

2 с SQL

Log : Die Benennung "Log" wurde nicht als Name eines Cmdlet, einer Funktion, einer Skriptdatei oder eines ausführbaren Programms erkannt. Überprüfen 
Sie die Schreibweise des Namens, oder ob der Pfad korrekt ist (sofern enthalten), und wiederholen Sie den Vorgang.
In Zeile:29 Zeichen:5
+     Log "Connected to MySQL database $MySQLHost\$database"
+     ~~~
    + CategoryInfo          : ObjectNotFound: (Log:String) [], CommandNotFoundException
    + FullyQualifiedErrorId : CommandNotFoundException

Ausnahme beim Aufrufen von "ExecuteNonQuery" mit 0 Argument(en):  "You have an error in your SQL syntax; check the manual that corresponds to your 
MySQL server version for the right syntax to use near 'ga FIELDS ENCLOSED BY '"' TERMINATED BY ';' LINES TERMINATED BY '\n' IGNORE 1 L' at line 1"
In Zeile:42 Zeichen:3
+   $RowsInserted = $command.ExecuteNonQuery()        # Execute command
+   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : MySqlException

[azarij]

а так? только дайте полный вывод после прогона скрипта, а не только ошибки на немецком.

# Connection Variables 
$user = 'root' 
$pass = '123456' 
$database = 'curamed' 
$MySQLHost = 'localhost' 
$dir = 'C:\Temp\1'
$dir2 = 'C:\Temp\2'

function Connect-MySQL([string]$user,[string]$pass,[string]$MySQLHost,[string]$database) { 
  # Load MySQL .NET Connector Objects 
  [void][system.reflection.Assembly]::LoadWithPartialName("MySql.Data") 

  # Open Connection 
  $connStr = "server=" + $MySQLHost + ";port=3306;uid=" + $user + ";pwd=" + $pass + ";database="+$database+";Pooling=FALSE" 
  $conn = New-Object MySql.Data.MySqlClient.MySqlConnection($connStr) 
 try {
        $conn = New-Object MySql.Data.MySqlClient.MySqlConnection($connStr) 
        $conn.Open()
    } catch [System.Management.Automation.PSArgumentException] {
        "Unable to connect to MySQL server, do you have the MySQL connector installed..?"
        $_
        Exit
    } catch {
        "Unable to connect to MySQL server..."
        $_.Exception.GetType().FullName
        $_.Exception.Message
        exit
    }
    "Connected to MySQL database $MySQLHost\$database"

    return $conn 

} 

function Disconnect-MySQL($conn) {
  $conn.Close()
}

function Execute-MySQLNonQuery($conn, [string]$query) { 
  $command = $conn.CreateCommand()                  # Create command object
  $command.CommandText = $query                     # Load query into object
  $RowsInserted = $command.ExecuteNonQuery()        # Execute command
  $command.Dispose()                                # Dispose of command object
  if ($RowsInserted) { 
    return $RowInserted 
  } else { 
    return $false 
  } 
} 

Connect-MySQL -user $user -pass $pass -mysqlhost localhost -database $database

foreach ($i in ls -name $dir\*.csv)
{
  Write-Host "$i"
  $file_content = Get-Content "$dir\$i";
  [System.IO.File]::WriteAllLines("$dir2\$i", $file_content);
  Remove-Item "$dir\$i"
# So, to insert records into a table 
    $query = "LOAD DATA LOCAL INFILE '$dir2\$i' INTO TABLE `bga` FIELDS ENCLOSED BY '`"' TERMINATED BY ';' LINES TERMINATED BY '\n' IGNORE 1 LINES;"
$query
    $Rows = Execute-MySQLNonQuery $conn $query 
    Write-Host $Rows " inserted into database"
}

[Evgeny Kotov]

azarij,

Connected to MySQL database localhost\curamed


ServerThread      : 10
DataSource        : localhost
ConnectionTimeout : 15
Database          : curamed
UseCompression    : False
State             : Open
ServerVersion     : 8.0.14
ConnectionString  : server=localhost;port=3306;user id=root;database=curamed;pooling=False
IsPasswordExpired : False
Site              : 
Container         : 

test.csv
LOAD DATA LOCAL INFILE 'C:\Temp\2\test.csv' INTO TABLE ga FIELDS ENCLOSED BY '"' TERMINATED BY ';' LINES TERMINATED BY '\n' IGNORE 1 LINES;
Ausnahme beim Aufrufen von "ExecuteNonQuery" mit 0 Argument(en):  "Fatal error encountered during command execution."
In Zeile:42 Zeichen:3
+   $RowsInserted = $command.ExecuteNonQuery()        # Execute command
+   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : MySqlException
 
False  inserted into database
test1.csv
LOAD DATA LOCAL INFILE 'C:\Temp\2\test1.csv' INTO TABLE ga FIELDS ENCLOSED BY '"' TERMINATED BY ';' LINES TERMINATED BY '\n' IGNORE 1 LINES;
Ausnahme beim Aufrufen von "ExecuteNonQuery" mit 0 Argument(en):  "Connection must be valid and open."
In Zeile:42 Zeichen:3
+   $RowsInserted = $command.ExecuteNonQuery()        # Execute command
+   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : InvalidOperationException
 
False  inserted into database



PS C:\Windows\system32>

[azarij]

ок, значит к базe оно подключилось. прогресс.
в базe есть таблица с именем ga?

[Evgeny Kotov]

azarij, bga

[azarij]

тогда меняйте:

$query = "LOAD DATA LOCAL INFILE '$dir2\$i' INTO TABLE `bga` FIELDS ENCLOSED BY '`"' TERMINATED BY ';' LINES TERMINATED BY '\n' IGNORE 1 LINES;"

на

$query = "LOAD DATA LOCAL INFILE '$dir2\$i' INTO TABLE bga FIELDS ENCLOSED BY '`"' TERMINATED BY ';' LINES TERMINATED BY '\n' IGNORE 1 LINES;"

мне обратные кавычки вокруг имени таблицы с самого начала не нравились.

[Evgeny Kotov]

azarij, Выкинул все и оставил самое необходимое.

Убрал обратные ковычки, добавил еще один запрос для подстраховки.
Возможно команнда "LOAD DATA LOCAL" была заблокированна.

Получилось примерно следующее:

$dbusername = 'root' 
$dbpassword = '123456' 
$dbname = 'curamed' 
$MySQLHost = 'localhost' 
$dir = 'C:\Temp\1'
$dir2 = 'C:\Temp\2'
[void][system.reflection.Assembly]::LoadWithPartialName("MySql.Data")
$connStr ="server=localhost;database=" + $dbname + ";Persist Security Info=false;user id=" + $dbusername + ";pwd=" + $dbpassword + ";"
$conn = New-Object MySql.Data.MySqlClient.MySqlConnection($connStr)
$conn.Open()
$cmd = New-Object MySql.Data.MySqlClient.MySqlCommand
$cmd.Connection  = $conn

Foreach ($i in ls -name $dir\*.csv)
{
	Write-Host "$i"
	$file_content = Get-Content "$dir\$i";
	[System.IO.File]::WriteAllLines("$dir2\$i", $file_content);
	#Remove-Item "$dir\$i"
# So, to insert records into a table 
    $cmd.CommantText ="SET @@GLOBAL. local_infile := 1;
    $cmd.CommandText ="LOAD DATA LOCAL INFILE '$dir2\$i' INTO TABLE bga FIELDS ENCLOSED BY '`"' TERMINATED BY ';' LINES TERMINATED BY '\n' IGNORE 1 LINES;"
    $cmd.ExecuteNonQuery()
    Write-Host  " inserted into database"
}

И ошибка :

In Zeile:22 Zeichen:24
+     $cmd.CommandText ="LOAD DATA LOCAL INFILE '$dir2\$i' INTO TABLE b ...
+                        ~~~~
Unerwartetes Token "LOAD" in Ausdruck oder Anweisung.
In Zeile:24 Zeichen:19
+     Write-Host  " inserted into database"
+                   ~~~~~~~~
Unerwartetes Token "inserted" in Ausdruck oder Anweisung.
In Zeile:24 Zeichen:41
+     Write-Host  " inserted into database"
+                                         ~
Die Zeichenfolge hat kein Abschlusszeichen: ".
In Zeile:15 Zeichen:1
+ {
+ ~
Die schließende "}" fehlt im Anweisungsblock oder der Typdefinition.
    + CategoryInfo          : ParserError: (:) [], ParentContainsErrorRecordException
    + FullyQualifiedErrorId : UnexpectedToken
 

PS C:\Windows\system32>

[azarij]

слишком много выкинули. возвращайте как было:

# Connection Variables
$user = 'root'
$pass = '123456'
$database = 'curamed'
$MySQLHost = 'localhost'
$dir = 'C:\Temp\1'
$dir2 = 'C:\Temp\2'

function Connect-MySQL([string]$user,[string]$pass,[string]$MySQLHost,[string]$database) {
# Load MySQL .NET Connector Objects
[void][system.reflection.Assembly]::LoadWithPartialName("MySql.Data")

# Open Connection
$connStr = "server=" + $MySQLHost + ";port=3306;uid=" + $user + ";pwd=" + $pass + ";database="+$database+";Pooling=FALSE"
$conn = New-Object MySql.Data.MySqlClient.MySqlConnection($connStr)
try {
$conn = New-Object MySql.Data.MySqlClient.MySqlConnection($connStr)
$conn.Open()
} catch [System.Management.Automation.PSArgumentException] {
"Unable to connect to MySQL server, do you have the MySQL connector installed..?"
$_
Exit
} catch {
"Unable to connect to MySQL server..."
$_.Exception.GetType().FullName
$_.Exception.Message
exit
}
"Connected to MySQL database $MySQLHost\$database"

return $conn

}

function Disconnect-MySQL($conn) {
$conn.Close()
}

function Execute-MySQLNonQuery($conn, [string]$query) {
$command = $conn.CreateCommand() # Create command object
$command.CommandText = $query # Load query into object
$RowsInserted = $command.ExecuteNonQuery() # Execute command
$command.Dispose() # Dispose of command object
if ($RowsInserted) {
return $RowInserted
} else {
return $false
}
}

Connect-MySQL -user $user -pass $pass -mysqlhost localhost -database $database

foreach ($i in ls -name $dir\*.csv)
{
Write-Host "$i"
$file_content = Get-Content "$dir\$i";
[System.IO.File]::WriteAllLines("$dir2\$i", $file_content);
Remove-Item "$dir\$i"
# So, to insert records into a table
$query = "LOAD DATA LOCAL INFILE '$dir2\$i' INTO TABLE bga FIELDS ENCLOSED BY '`"' TERMINATED BY ';' LINES TERMINATED BY '\n' IGNORE 1 LINES;"
$query
$Rows = Execute-MySQLNonQuery $conn $query
Write-Host $Rows " inserted into database"
}

[Evgeny Kotov]

azarij, ok

Connected to MySQL database localhost\curamed


ServerThread      : 35
DataSource        : localhost
ConnectionTimeout : 15
Database          : curamed
UseCompression    : False
State             : Open
ServerVersion     : 8.0.14
ConnectionString  : server=localhost;port=3306;user id=root;database=curamed;pooling=False
IsPasswordExpired : False
Site              : 
Container         : 

test.csv
LOAD DATA LOCAL INFILE 'C:\Temp\2\test.csv' INTO TABLE bga FIELDS ENCLOSED BY '"' TERMINATED BY ';' LINES TERMINATED BY '\n' IGNORE 1 LINES;
Ausnahme beim Aufrufen von "ExecuteNonQuery" mit 0 Argument(en):  "Connection must be valid and open."
In Zeile:42 Zeichen:1
+ $RowsInserted = $command.ExecuteNonQuery() # Execute command
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : InvalidOperationException
 
False  inserted into database
test1.csv
LOAD DATA LOCAL INFILE 'C:\Temp\2\test1.csv' INTO TABLE bga FIELDS ENCLOSED BY '"' TERMINATED BY ';' LINES TERMINATED BY '\n' IGNORE 1 LINES;
Ausnahme beim Aufrufen von "ExecuteNonQuery" mit 0 Argument(en):  "Connection must be valid and open."
In Zeile:42 Zeichen:1
+ $RowsInserted = $command.ExecuteNonQuery() # Execute command
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : InvalidOperationException
 
False  inserted into database



PS C:\Windows\system32>

[azarij]

хорошо, синтаксических ошибок вроде больше нет.
дайте полный вывод вот этого кода:

# Connection Variables
$user = 'root'
$pass = '123456'
$database = 'curamed'
$MySQLHost = 'localhost'
$dir = 'C:\Temp\1'
$dir2 = 'C:\Temp\2'

function Connect-MySQL([string]$user,[string]$pass,[string]$MySQLHost,[string]$database) {
# Load MySQL .NET Connector Objects
[void][system.reflection.Assembly]::LoadWithPartialName("MySql.Data")

# Open Connection
$connStr = "server=" + $MySQLHost + ";port=3306;uid=" + $user + ";pwd=" + $pass + ";database="+$database+";Pooling=FALSE"
$conn = New-Object MySql.Data.MySqlClient.MySqlConnection($connStr)
try {
$conn = New-Object MySql.Data.MySqlClient.MySqlConnection($connStr)
$conn.Open()
} catch [System.Management.Automation.PSArgumentException] {
"Unable to connect to MySQL server, do you have the MySQL connector installed..?"
$_
Exit
} catch {
"Unable to connect to MySQL server..."
$_.Exception.GetType().FullName
$_.Exception.Message
exit
}
"Connected to MySQL database $MySQLHost\$database"

return $conn

}

function Disconnect-MySQL($conn) {
$conn.Close()
}

function Execute-MySQLNonQuery($conn, [string]$query) {
$command = $conn.CreateCommand() # Create command object
$command.CommandText = $query # Load query into object
$RowsInserted = $command.ExecuteNonQuery() # Execute command
$command.Dispose() # Dispose of command object
if ($RowsInserted) {
return $RowInserted
} else {
return $false
}
}

Connect-MySQL -user $user -pass $pass -mysqlhost localhost -database $database

foreach ($i in ls -name $dir\*.csv)
{
Write-Host "$i"
$file_content = Get-Content "$dir\$i";
[System.IO.File]::WriteAllLines("$dir2\$i", $file_content);
Remove-Item "$dir\$i"
# So, to insert records into a table
$query = "LOAD DATA LOCAL INFILE '$dir2\$i' INTO TABLE bga FIELDS ENCLOSED BY '`"' TERMINATED BY ';' LINES TERMINATED BY '\n' IGNORE 1 LINES;"
"QUERY:"
$query
"CONNECTION:"
$conn
$Rows = Execute-MySQLNonQuery -conn $conn -query $query
Write-Host $Rows " inserted into database"
}

[Evgeny Kotov]

azarij,

Connected to MySQL database localhost\curamed


ServerThread      : 14
DataSource        : localhost
ConnectionTimeout : 15
Database          : curamed
UseCompression    : False
State             : Open
ServerVersion     : 8.0.14
ConnectionString  : server=localhost;port=3306;user id=root;database=curamed;pooling=False
IsPasswordExpired : False
Site              : 
Container         : 

test.csv
QUERY:
LOAD DATA LOCAL INFILE 'C:\Temp\2\test.csv' INTO TABLE bga FIELDS ENCLOSED BY '"' TERMINATED BY ';' LINES TERMINATED BY '\n' IGNORE 1 LINES;
CONNECTION:
ServerThread      : 39
DataSource        : localhost
ConnectionTimeout : 15
Database          : curamed
UseCompression    : False
State             : Closed
ServerVersion     : 8.0.14
ConnectionString  : server=localhost;database=curamed;persistsecurityinfo=False;user id=root
IsPasswordExpired : False
Site              : 
Container         : 

Ausnahme beim Aufrufen von "ExecuteNonQuery" mit 0 Argument(en):  "Connection must be valid and open."
In Zeile:42 Zeichen:1
+ $RowsInserted = $command.ExecuteNonQuery() # Execute command
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : InvalidOperationException
 
False  inserted into database
test1.csv
QUERY:
LOAD DATA LOCAL INFILE 'C:\Temp\2\test1.csv' INTO TABLE bga FIELDS ENCLOSED BY '"' TERMINATED BY ';' LINES TERMINATED BY '\n' IGNORE 1 LINES;
CONNECTION:
ServerThread      : 39
DataSource        : localhost
ConnectionTimeout : 15
Database          : curamed
UseCompression    : False
State             : Closed
ServerVersion     : 8.0.14
ConnectionString  : server=localhost;database=curamed;persistsecurityinfo=False;user id=root
IsPasswordExpired : False
Site              : 
Container         : 

Ausnahme beim Aufrufen von "ExecuteNonQuery" mit 0 Argument(en):  "Connection must be valid and open."
In Zeile:42 Zeichen:1
+ $RowsInserted = $command.ExecuteNonQuery() # Execute command
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : InvalidOperationException
 
False  inserted into database



PS C:\Windows\system32>

[azarij]

понятно. оно открывает подключение к базе и тут же его закрывает зачем-то. так что когда вы пытаетесь слать команды - подключение уже закрыто... будем думать почему.

[azarij]

ок, а так?

# Connection Variables
$user = 'root'
$pass = '123456'
$database = 'curamed'
$MySQLHost = 'localhost'
$dir = 'C:\Temp\1'
$dir2 = 'C:\Temp\2'

function Connect-MySQL([string]$user,[string]$pass,[string]$MySQLHost,[string]$database) {
# Load MySQL .NET Connector Objects
[void][system.reflection.Assembly]::LoadWithPartialName("MySql.Data")

# Open Connection
$connStr = "server=" + $MySQLHost + ";port=3306;uid=" + $user + ";pwd=" + $pass + ";database="+$database+";Pooling=FALSE"
$conn = New-Object MySql.Data.MySqlClient.MySqlConnection($connStr)
try {
$conn = New-Object MySql.Data.MySqlClient.MySqlConnection($connStr)
$conn.Open()
} catch [System.Management.Automation.PSArgumentException] {
"Unable to connect to MySQL server, do you have the MySQL connector installed..?"
$_
Exit
} catch {
"Unable to connect to MySQL server..."
$_.Exception.GetType().FullName
$_.Exception.Message
exit
}
"Connected to MySQL database $MySQLHost\$database"

return $conn

}

function Disconnect-MySQL($conn) {
$conn.Close()
}

function Execute-MySQLNonQuery($conn, [string]$query) {
$command = $conn.CreateCommand() # Create command object
$command.CommandText = $query # Load query into object
$RowsInserted = $command.ExecuteNonQuery() # Execute command
$command.Dispose() # Dispose of command object
if ($RowsInserted) {
return $RowInserted
} else {
return $false
}
}

foreach ($i in ls -name $dir\*.csv)
{
Write-Host "$i"
$file_content = Get-Content "$dir\$i";
[System.IO.File]::WriteAllLines("$dir2\$i", $file_content);
Remove-Item "$dir\$i"
# So, to insert records into a table
$query = "LOAD DATA LOCAL INFILE '$dir2\$i' INTO TABLE bga FIELDS ENCLOSED BY '`"' TERMINATED BY ';' LINES TERMINATED BY '\n' IGNORE 1 LINES;"
"Opening the connection to DB to send data from this file..."
Connect-MySQL -user $user -pass $pass -mysqlhost localhost -database $database
"QUERY:"
$query
"CONNECTION right after open():"
$conn
$Rows = Execute-MySQLNonQuery -conn $conn -query $query
Write-Host $Rows " inserted into database"
"CONNECTION after command was sent:"
$conn
}
"Closing connection to DB"
Disconnect-MySQL -conn $conn

[Evgeny Kotov]

azarij,

test.csv
Opening the connection to DB to send data from this file...
Connected to MySQL database localhost\curamed


ServerThread      : 28
DataSource        : localhost
ConnectionTimeout : 15
Database          : curamed
UseCompression    : False
State             : Open
ServerVersion     : 5.5.5-10.1.37-MariaDB
ConnectionString  : server=localhost;port=3306;user id=root;database=curamed;pooling=False
IsPasswordExpired : False
Site              : 
Container         : 

QUERY:
LOAD DATA LOCAL INFILE 'C:\Temp\2\test.csv' INTO TABLE bga FIELDS ENCLOSED BY '"' TERMINATED BY ';' LINES TERMINATED BY '\n' IGNORE 1 LINES;
CONNECTION right after open():
ServerThread      : 
DataSource        : localhost
ConnectionTimeout : 15
Database          : curamed
UseCompression    : False
State             : Closed
ServerVersion     : 
ConnectionString  : server=localhost;database=curamed;persistsecurityinfo=False;user id=root
IsPasswordExpired : 
Site              : 
Container         : 

Ausnahme beim Aufrufen von "ExecuteNonQuery" mit 0 Argument(en):  "Connection must be valid and open."
In Zeile:42 Zeichen:1
+ $RowsInserted = $command.ExecuteNonQuery() # Execute command
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : InvalidOperationException
 
False  inserted into database
CONNECTION after command was sent:
ServerThread      : 
DataSource        : localhost
ConnectionTimeout : 15
Database          : curamed
UseCompression    : False
State             : Closed
ServerVersion     : 
ConnectionString  : server=localhost;database=curamed;persistsecurityinfo=False;user id=root
IsPasswordExpired : 
Site              : 
Container         : 

test1.csv
Opening the connection to DB to send data from this file...
Connected to MySQL database localhost\curamed
ServerThread      : 29
DataSource        : localhost
ConnectionTimeout : 15
Database          : curamed
UseCompression    : False
State             : Open
ServerVersion     : 5.5.5-10.1.37-MariaDB
ConnectionString  : server=localhost;port=3306;user id=root;database=curamed;pooling=False
IsPasswordExpired : False
Site              : 
Container         : 

QUERY:
LOAD DATA LOCAL INFILE 'C:\Temp\2\test1.csv' INTO TABLE bga FIELDS ENCLOSED BY '"' TERMINATED BY ';' LINES TERMINATED BY '\n' IGNORE 1 LINES;
CONNECTION right after open():
ServerThread      : 
DataSource        : localhost
ConnectionTimeout : 15
Database          : curamed
UseCompression    : False
State             : Closed
ServerVersion     : 
ConnectionString  : server=localhost;database=curamed;persistsecurityinfo=False;user id=root
IsPasswordExpired : 
Site              : 
Container         : 

Ausnahme beim Aufrufen von "ExecuteNonQuery" mit 0 Argument(en):  "Connection must be valid and open."
In Zeile:42 Zeichen:1
+ $RowsInserted = $command.ExecuteNonQuery() # Execute command
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : InvalidOperationException
 
False  inserted into database
CONNECTION after command was sent:
ServerThread      : 
DataSource        : localhost
ConnectionTimeout : 15
Database          : curamed
UseCompression    : False
State             : Closed
ServerVersion     : 
ConnectionString  : server=localhost;database=curamed;persistsecurityinfo=False;user id=root
IsPasswordExpired : 
Site              : 
Container         : 

Closing connection to DB



PS C:\Windows\system32>

[azarij]

неа, закрывает он подключение моментально...
без доступа к хосту я не смогу помочь, а у себя мускул ставить и лень и времени нет.