1

When I run following code on VB.Net (Visual Studio 2019) a get 789 and never I receive 789.1234. Decimal part il always missing !

Imports System.Data.OleDb

Dim cs = "Provider=PostgreSQL OLE DB Provider;" _
       & "DataSource=127.0.0.1;" _
       & "location=extraits;" _
       & "User ID=postgres;" _
       & "Password='Sp1r1tus'"

Dim oleconx = New OleDbConnection(cs)
Dim cmd = New OleDbCommand("SELECT 789.1234 as account", oleconx)
Dim rxSheet = cmd.ExecuteReader()
Dim n = rxSheet.GetOrdinal("account")
Dim dAccount = rxSheet.GetDecimal(n)

it is same thing if I use following code

Dim dAccount = rxSheet("account")

Variable dAccount contains 789 ! Decimal part is lost !

In real situation (previous code is only a simple code to explain the problem), the only solution I have found to obtain complete decimal number using same code is to change SQL SELECT command

SELECT account * 1000 as account

and to write folloging VB.Net code

Dim dAccount as Decimal = rxSheet("account") / 1000

But this is a workaround.

I have just installed Postgres 14.1 using standard Setup exe program . Before this version, I use version 8.4 on same PC on Windows 10. Perhaps some DLL are not installed in correct place (as libpq.dll).

This problem already existed with version 8.4.

Can some body help me ?

I have posted this bug on PostGres but they responded following

PostgreSQL does not ship a built-in OLE DB provider. You need to contact the authors of your OLE DB driver.

Is there a solution to my issue ?

Who is responsible PostGres or Microsoft ?

schlebe
  • 3,387
  • 5
  • 37
  • 50
  • It is spelled "789,1234" in Belgium – Hans Passant Jan 15 '22 at 17:02
  • Yes in Belgium 789,1234 is spelled with comma but in VB.Net I must use a point in SELECT command. – schlebe Jan 15 '22 at 17:09
  • So where are you getting `PostgreSQL OLE DB Provider` from? – Adrian Klaver Jan 15 '22 at 17:12
  • @Adrian: I don't know ! I have installed PostGres 8.4 and then 9.6 and now 14.1 and my VB.net program have somes issues with GetDecimal(). I have desinstalled older Postgres versions (8.4 and 9.6). How can I know from where is coming used PostgresSQL OLE DB Provider ? – schlebe Jan 15 '22 at 17:15
  • Where did you get the Postgres install from and how did you install it? – Adrian Klaver Jan 15 '22 at 17:18
  • 2
    I strongly recommend using npgsql available via Nuget. It is a purpose built .net library for accessing PostgreSQL, rather than OleDb. – Jonathan Willcock Jan 15 '22 at 18:46
  • Also have you tried using GetDouble instead of GetDecimal? – Jonathan Willcock Jan 15 '22 at 18:49
  • Now, I have installed npgsql and I don't have any problem with Decimal. I can now say that it is a bug in OLE DB Provider ! But who is responsible Microsoft or Postgres or another party ? Who can I contact so that this is bug is corrected in OLE DB Provider ? – schlebe Jan 15 '22 at 20:19
  • In my opinion the “workaround” you’ve described is the more correct way. I feel it’s always best where possible to use explicit type declarations as it makes everything a lot more readable. – Hursey Jan 15 '22 at 22:57
  • 1
    AFAIK the OleDb classes are provided by Microsoft. But they provide minimalistic functionality on a lowest common denominator basis. Apart from use with MS Office products, I would always advise against using them. You can submit a bug report to MS, but don't be surprised if it is not high on their list. Npgsql on the other hand is a purpose built .Net library supporting many data types specific to PostgreSQL. Although it is independent of the PostgreSQL project, it is actively maintained and supported. I have used it in many projects, and will have no hesitation in using it again. – Jonathan Willcock Jan 16 '22 at 08:01
  • I have seen on PostGres site that OLE DB Provider is supported by www.pgoledb.com. Their product that is named PostgreSQL Native OLEDB Provider (PGNP) is a commercial product and is not free. – schlebe Jan 16 '22 at 19:45

1 Answers1

1

I've tried the scenario with Intellisoft OLEDB Provider for PostgreSQL (PGNP), and it worked as expected. I had to slightly change the connection string, and add line containing rxSheet.Read(). Here is copy of working code:

Dim cs = "Provider=PGNP.1;" _
            & "Data Source=127.0.0.1;" _
            & "Initial Catalog=postgres;" _
            & "User ID=postgres;" _
            & "Password=123!"

    Dim oleconx = New OleDbConnection(cs)
    oleconx.Open()
    Dim cmd = New OleDbCommand("SELECT 789.1234 as account", oleconx)
    Dim rxSheet = cmd.ExecuteReader()
    Dim n = rxSheet.GetOrdinal("account")
    While rxSheet.Read()
        Dim dAccount = rxSheet.GetDecimal(n)
        Console.WriteLine("dAccount={0}", dAccount)
    End While

and here is the result: dAccount=789.1234

Terry
  • 310
  • 3
  • 9
  • Thanks for this information. The problem with PGNP is that it is a commercial product that costs $519.99 for a desktop version. The trial version is limited. For your information, I now use npgsql assembly downloaded using Nuget. – schlebe Jan 18 '22 at 06:00