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:
SQL Connection Issue
Forum rules
Do not post any licensing information in this forum.
Any code longer than three lines should be added as code using the 'Select Code' dropdown menu or attached as a file.
Do not post any licensing information in this forum.
Any code longer than three lines should be added as code using the 'Select Code' dropdown menu or attached as a file.
- Alexander Riedel
- Posts: 8479
- Last visit: Thu Mar 28, 2024 9:29 am
- Been upvoted: 37 times
Re: SQL Connection Issue
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.
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.
Alexander Riedel
SAPIEN Technologies, Inc.
SAPIEN Technologies, Inc.
Re: SQL Connection Issue
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.
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 $_
}
- wayne_Avery
- Posts: 30
- Last visit: Tue Feb 01, 2022 2:25 pm
Re: SQL Connection Issue
Hi Alexander, To answer your questionsAlexander Riedel wrote: ↑Mon Nov 26, 2018 11:41 pm 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.
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
My example above had one error. Here is the fix.
It is hard to test your code without your database. This code runs on my database with no issues in or out of PrimalScript.
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 $_
}
Re: SQL Connection Issue
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.
- wayne_Avery
- Posts: 30
- Last visit: Tue Feb 01, 2022 2:25 pm
Re: SQL Connection Issue
Using your code and my version of PrimalScript 7.4.118 64bit it still fails if there is no blank variable present.jvierra wrote: ↑Tue Nov 27, 2018 4: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.
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
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.
I can run your exact code in PrimalScript with no issues.