Using PowerShell's BigInt with SQLAnywhere

[former_SQLA_member1694920]

Got the below code:

Add-Type -AssemblyName ("Sap.Data.SQLAnywhere.v4.5, Version=17.0.0.10624, Culture=neutral, PublicKeyToken=f222fc4333e0d400")

[bigint] $myvalue = 123456789123456789

$connectionString = "Host=10.10.10.10:12345;Server=testserver;DatabaseName=testdb;UserID=DBA;Password=123"

[Sap.Data.SQLAnywhere.SAConnection] $conn = New-Object Sap.Data.SQLAnywhere.SAConnection($connectionString)
$conn.Open()

[Sap.Data.SQLAnywhere.SACommand] $command = New-Object Sap.Data.SQLAnywhere.SACommand("UPDATE table SET value = ? WHERE value IS NOT NULL", $conn)

[Sap.Data.SQLAnywhere.SAParameter] $parameter = New-Object Sap.Data.SQLAnywhere.SAParameter
$parameter.SADbType = [Sap.Data.SQLAnywhere.SADbType]::BigInt
$parameter.Value = $myvalue
[void] $command.Parameters.Add($parameter)

try {
    [void] $command.ExecuteNonQuery()
} catch {
    Write-Error $_
}

And got this exception:

Exception calling "ExecuteNonQuery" with "0" argument(s): "Unable to cast object of type 'System.Numerics.BigInteger' to type 'System.IConvertible'."

I managed to work around this problem by setting $myValue to a string instead of a BigInt.

Are BigInts supported natively with SQLAnywhere .NET Connector?

[Vlad]

I do not like writing answers, because I am not the SA developer 🙂 But I have another question for you:

How big is yours BigInteger? MSDN says this number is very large. So large that you cannot even imagine the half of it. Nevertheless, SA supports following numeric types. If you ask yourself, whether 8-bytes integer fits your requirements, so you shouldn't use BigInteger in this case, otherwise I suggest you to use binary types.

That is why BigInteger cannot be converted to any primitive data type such as shortint/int/longint/verylongint/veryverylongint.

[former_SQLA_member1694920]

Whoops - sometimes I forget PowerShell's BigInt is not the same thing as SA's BigInt 🙂