Page 1 of 1

SQL Connection Issue

Posted: Mon Nov 26, 2018 8:55 pm
by wayne_Avery
To help you better we need some information from you.

Product, version and build: PrimalScript Version 7.4.118
32 or 64 bit version of product: 64 bit
Operating system: Windows 7
32 or 64 bit OS: 64 Bit

*** Please add details and screenshots as needed below. ***

I have a script that connects to a SQL database and retrieves data. I may have stumbled over a possible bug. Below is the script.

If I run this script without the variable $zzzzzzzzzzzzzzzzz or have a value assigned to that variable it fails, error is after the script. If I have the (or any) variable included but don’t assign a value as shown it runs correctly and returns the desired data. Is this a bug or am I missing something?
Many Thanks is advance.
Wayne

# ****** SQL Connection Script******
$SQL_Database = "MyDB"
$SQL_Table = "MyTable"
$position = "CPE010"
$Revision = "26"
$zzzzzzzzzzzzzzzzz
#
#
$connCD = New-Object System.Data.SqlClient.SqlConnection
$connCD.ConnectionString = "Data Source=" + $SQL_dataSource + ";Database="+$SQL_Database+";Integrated Security=SSPI;"
$cmdTextCD = "Select * FROM " + $SQL_Table + " WHERE [PD_PosNum] = '" + $position + "' AND [Status] = '1' AND [Revision] = '"+ $Revision +"' Order By [PD_PosNum]"
$connCD.Open()
$cmdCD = New-Object System.Data.SqlClient.SqlCommand($cmdTextCD,$connCD)
$rdrCD = $cmdCD.ExecuteReader()
if ($rdrCD.Read()){
$rdrCD["PD_Pos_Auth"].ToString()
}else{
Write-Host "Nothing"
}
$connCD.Close()

# ****** SQL Connection Script Error******

ERROR: New-Object : Cannot find type [System.Data.SqlClient.SqlConnection]: verify that the assembly containing this type is loaded.
ERROR: At M:\Documents\SAPIEN\Scripts\Samples\SQL.ps1:9 char:11
ERROR: + $connCD = New-Object System.Data.SqlClient.SqlConnection
ERROR: + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ERROR: + CategoryInfo : InvalidType: (:) [New-Object], PSArgumentException
ERROR: + FullyQualifiedErrorId : TypeNotFound,Microsoft.PowerShell.Commands.NewObjectCommand
ERROR:
ERROR: The property 'ConnectionString' cannot be found on this object. Verify that the property exists and can be set.
ERROR: At M:\Documents\SAPIEN\Scripts\Samples\SQL.ps1:10 char:1
ERROR: + $connCD.ConnectionString = "Data Source=" + $SQL_dataSource + ";Database="+$SQL_ ...
ERROR: + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ERROR: + CategoryInfo : InvalidOperation: (:) [], RuntimeException
ERROR: + FullyQualifiedErrorId : PropertyNotFound
ERROR:
ERROR: You cannot call a method on a null-valued expression.
ERROR: At M:\Documents\SAPIEN\Scripts\Samples\SQL.ps1:12 char:1
ERROR: + $connCD.Open()
ERROR: + ~~~~~~~~~~~~~~
ERROR: + CategoryInfo : InvalidOperation: (:) [], RuntimeException
ERROR: + FullyQualifiedErrorId : InvokeMethodOnNull
ERROR:
ERROR: Exception calling "ExecuteReader" with "0" argument(s): "ExecuteReader: Connection property has not been initialized."
ERROR: At M:\Documents\SAPIEN\Scripts\Samples\SQL.ps1:14 char:1
ERROR: + $rdrCD = $cmdCD.ExecuteReader()
ERROR: + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ERROR: + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
ERROR: + FullyQualifiedErrorId : InvalidOperationException
ERROR:
ERROR: You cannot call a method on a null-valued expression.
ERROR: At M:\Documents\SAPIEN\Scripts\Samples\SQL.ps1:15 char:6
ERROR: + if ($rdrCD.Read()){
ERROR: + ~~~~~~~~~~~~~
ERROR: + CategoryInfo : InvalidOperation: (:) [], RuntimeException
ERROR: + FullyQualifiedErrorId : InvokeMethodOnNull
ERROR:
ERROR: You cannot call a method on a null-valued expression.
ERROR: At M:\Documents\SAPIEN\Scripts\Samples\SQL.ps1:20 char:1
ERROR: + $connCD.Close()
ERROR: + ~~~~~~~~~~~~~~~
ERROR: + CategoryInfo : InvalidOperation: (:) [], RuntimeException
ERROR: + FullyQualifiedErrorId : InvokeMethodOnNull
ERROR:

Re: SQL Connection Issue

Posted: Tue Nov 27, 2018 12:41 am
by Alexander Riedel
I am admittedly stumped on that one. No idea. I will move the post to the general PowerShell area to see if anyone else has any input.
Questions:
- Does the script run correctly (without the $zzzzzzzzzz variable) from a powershell console?
- The use of the variable as described creates basically empty output. Replace it with "This is just some output" (with the quotes of course). Does that also work?
- If not, leave the variable in place but assign it some text value above. $zzz = "make it work" and then use $zzz on a line by itself.

Re: SQL Connection Issue

Posted: Tue Nov 27, 2018 7:29 am
by jvierra
I cannot reproduce this behavior. It may be because the file is damaged so try creating a new file and try again.

Here is the correct way to create a command in PowerShell. Your method will work but can create issues. You will find that this method eliminates "run-on" exceptions and makes it easier to build and manage ADO scripts.

Code: Select all

# ****** SQL Connection Script******
$SQL_dataSource = '<server instance>'
$SQL_Database = 'MyDB'
$SQL_Table = 'MyTable'
$position = 'CPE010'
$Revision = '26'
$connStr = "Data Source=$SQL_dataSource;Database=$SQL_Database;Integrated Security=SSPI;"
$cmdText = "Select * FROM $SQL_Table WHERE PD_PosNum='$position' AND Status=1 AND Revision='$Revision' Order By [PD_PosNum]"
$dt = New-Object System.Data.DataTable

Try{
    $conn = New-Object System.Data.SqlClient.SqlConnection($connStr)
    $conn.Open()
    $cmd = $conn.CreateCommand()
    $cmd.CommandText = $cmdText
    $rdr = $cmd.ExecuteReader()
    $dt.Load($rdr)
    $conn.Close()
    # test table value
    $dt['PD_Pos_Auth']
}
Catch{
    Throw $_
}

Re: SQL Connection Issue

Posted: Tue Nov 27, 2018 5:28 pm
by wayne_Avery
Alexander Riedel wrote:
Tue Nov 27, 2018 12:41 am
I am admittedly stumped on that one. No idea. I will move the post to the general PowerShell area to see if anyone else has any input.
Questions:
- Does the script run correctly (without the $zzzzzzzzzz variable) from a powershell console?
- The use of the variable as described creates basically empty output. Replace it with "This is just some output" (with the quotes of course). Does that also work?
- If not, leave the variable in place but assign it some text value above. $zzz = "make it work" and then use $zzz on a line by itself.
Hi Alexander, To answer your questions
Yes - The script does run successfully with or without the $zzzzzzz variable in a normal powershell console. This is why I thought there may be a bug in the PrimalScript application.
No - The script does not run successfully in PrimalScript with the variable having a value, only if there is no value.
I have also tried running the PS1 file outside of PrimalScript and it does work with or without the $zzzzzzz variable.
Thanks for looking into this.
Regards
Wayne

Re: SQL Connection Issue

Posted: Tue Nov 27, 2018 5:43 pm
by jvierra
My example above had one error. Here is the fix.

Code: Select all

# ****** SQL Connection Script******
$SQL_dataSource = '<server instance>'
$SQL_Database = 'MyDB'
$SQL_Table = 'MyTable'
$position = 'CPE010'
$Revision = '26'
$connStr = "Data Source=$SQL_dataSource;Database=$SQL_Database;Integrated Security=SSPI;"
$cmdText = "Select * FROM $SQL_Table WHERE PD_PosNum='$position' AND Status=1 AND Revision='$Revision' Order By [PD_PosNum]"
$dt = New-Object System.Data.DataTable

Try{
    $conn = New-Object System.Data.SqlClient.SqlConnection($connStr)
    $conn.Open()
    $cmd = $conn.CreateCommand()
    $cmd.CommandText = $cmdText
    $rdr = $cmd.ExecuteReader()
    $dt.Load($rdr)
    $conn.Close()
    # test table value
    $dt  | select PD_Pos_Auth
}
Catch{
    Throw $_
}
It is hard to test your code without your database. This code runs on my database with no issues in or out of PrimalScript.

Re: SQL Connection Issue

Posted: Tue Nov 27, 2018 5:47 pm
by jvierra
I can suggest that you are not running under a correct version of Net Framework or that you have system issues that prevent the code from running correctly. Perhaps there are bad characters in you PS1 file. Open in binary mode to see if there are unprintable characters in the code.

Re: SQL Connection Issue

Posted: Tue Nov 27, 2018 6:07 pm
by wayne_Avery
jvierra wrote:
Tue Nov 27, 2018 5:47 pm
I can suggest that you are not running under a correct version of Net Framework or that you have system issues that prevent the code from running correctly. Perhaps there are bad characters in you PS1 file. Open in binary mode to see if there are unprintable characters in the code.
Using your code and my version of PrimalScript 7.4.118 64bit it still fails if there is no blank variable present.
I have opened the exact same file in Visual Studio Code V1.27.2 and it runs fine with or without the variable. It also runs OK in a PowerShell console or Windows PowerShell ISE. To me it looks like a bug in the application because PrimalScript is the only place it fails on my system.
Thanks for looking at this.
Regards
Wayne

Re: SQL Connection Issue

Posted: Tue Nov 27, 2018 6:22 pm
by jvierra
I am not saying that you are not getting an error. I am just pointing out that the issue is not the script. I recommend trying it on a different system with a fresh copy of PrimalScript Something in your installation is broken.

I can run your exact code in PrimalScript with no issues.