Thread: Writing Large Objects to Postgresql via ODBC using VB

Writing Large Objects to Postgresql via ODBC using VB

From
Matthew Dormer
Date:
I am having problems writing to a postgres database using ODBC.  Here are
the steps I have taken:

1.  I have greated a lo type in the database

create type lo (
internallength=4, externallength=10,
input=int4in, output=int4out,
default='', passedbyvalue
);


2.  I create a table in the database

Create table matt ( matt_id integer not null unique primary key, matt_text
lo );

3.  I have a simple form with 1 rich text box, RichTextBox1 and a command
button Command 1  Here is the code on the form:


Private Sub Command1_Click()

     Dim query As rdoQuery
     Dim rst As rdoResultset

     Set query = cn.CreateQuery("Add_Pic", "select * from matt;")
     Set rst = query.OpenResultset(1, 3)

     rst.AddNew
     Save_RichText RichTextBox1, rst![matt_pic]
     rst.Update

End Sub


Public Function Save_RichText(TextBox1 As RichTextBox, WhatField As rdoColumn)

     Dim DataFile As Integer ' Free File Number fo Reading
     Dim Fl As Long ' Length of the File
     Dim Chunks As Integer ' The Number of Chunks
     Dim Fragment As Integer ' The Size of the Remainder
     Dim Chunk() As Byte ' To hold each Chunk
     Dim I As Integer ' Variable for For Loop

     Const FileName = "c:\tmpsavetext.rtf"
     Const ChunkSize As Integer = 1024

     TextBox1.SaveFile FileName, rtfRTF
     DataFile = FreeFile

     Open FileName For Binary Access Read As DataFile
     Fl = LOF(DataFile)    ' Length of data in file
     If Fl = 0 Then Close DataFile: Exit Function
     Chunks = Fl \ ChunkSize
     Fragment = Fl Mod ChunkSize

     'Put Null into Field
     WhatField.AppendChunk Null

     'Get the Fragment
     ReDim Chunk(Fragment)
     Get DataFile, , Chunk()
     WhatField.AppendChunk Chunk()

     'Get the Chunks
     ReDim Chunk(ChunkSize)
     For I = 1 To Chunks
         Get DataFile, , Chunk()
         WhatField.AppendChunk Chunk()
     Next I

     Close DataFile
     Kill FileName

End Function


What is wrong with the above.  when I run it i get the error:

S1C00: Only SQL_POSITION/REFRESH is supported for SQLSetPos

I am running postgresql 6.5.3, PostgreSQL ODBC Driver v 6.50.0000 and am
using vb 6.0 Enterprise. The postgresql was installed from a rpm and is
running on RedHat 6.2

Any help would be greatly Appreciated.

Matt Dormer


RE: Writing Large Objects to Postgresql via ODBC using VB

From
"Henshall, Stuart - WCP"
Date:
Hello,
    I'm not sure of your problem, but 6.5 seems a little old so maybe an
update would help?
    Also you don't give you primary key a value (and its set to Not
Null) so this look to be a problem. Try having a look at the serial type
rather than integer for matt.matt_id
Hope this helps,
- Stuart

P.S. do you need to do anything like rst.Edit using before entering data?

> -----Original Message-----
> From:    Matthew Dormer [SMTP:matt@infosource.com.au]
> Sent:    Wednesday, May 02, 2001 7:32 AM
> To:    pgsql-odbc@postgresql.org
> Subject:    Writing Large Objects to Postgresql via ODBC using VB
>
> I am having problems writing to a postgres database using ODBC.  Here are
> the steps I have taken:
>
> 1.  I have greated a lo type in the database
>
> create type lo (
> internallength=4, externallength=10,
> input=int4in, output=int4out,
> default='', passedbyvalue
> );
>
>
> 2.  I create a table in the database
>
> Create table matt ( matt_id integer not null unique primary key, matt_text
>
> lo );
>
> 3.  I have a simple form with 1 rich text box, RichTextBox1 and a command
> button Command 1  Here is the code on the form:
>
>
> Private Sub Command1_Click()
>
>      Dim query As rdoQuery
>      Dim rst As rdoResultset
>
>      Set query = cn.CreateQuery("Add_Pic", "select * from matt;")
>      Set rst = query.OpenResultset(1, 3)
>
>      rst.AddNew
>      Save_RichText RichTextBox1, rst![matt_pic]
>      rst.Update
>
> End Sub
>
>
> Public Function Save_RichText(TextBox1 As RichTextBox, WhatField As
> rdoColumn)
>
>      Dim DataFile As Integer ' Free File Number fo Reading
>      Dim Fl As Long ' Length of the File
>      Dim Chunks As Integer ' The Number of Chunks
>      Dim Fragment As Integer ' The Size of the Remainder
>      Dim Chunk() As Byte ' To hold each Chunk
>      Dim I As Integer ' Variable for For Loop
>
>      Const FileName = "c:\tmpsavetext.rtf"
>      Const ChunkSize As Integer = 1024
>
>      TextBox1.SaveFile FileName, rtfRTF
>      DataFile = FreeFile
>
>      Open FileName For Binary Access Read As DataFile
>      Fl = LOF(DataFile)    ' Length of data in file
>      If Fl = 0 Then Close DataFile: Exit Function
>      Chunks = Fl \ ChunkSize
>      Fragment = Fl Mod ChunkSize
>
>      'Put Null into Field
>      WhatField.AppendChunk Null
>
>      'Get the Fragment
>      ReDim Chunk(Fragment)
>      Get DataFile, , Chunk()
>      WhatField.AppendChunk Chunk()
>
>      'Get the Chunks
>      ReDim Chunk(ChunkSize)
>      For I = 1 To Chunks
>          Get DataFile, , Chunk()
>          WhatField.AppendChunk Chunk()
>      Next I
>
>      Close DataFile
>      Kill FileName
>
> End Function
>
>
> What is wrong with the above.  when I run it i get the error:
>
> S1C00: Only SQL_POSITION/REFRESH is supported for SQLSetPos
>
> I am running postgresql 6.5.3, PostgreSQL ODBC Driver v 6.50.0000 and am
> using vb 6.0 Enterprise. The postgresql was installed from a rpm and is
> running on RedHat 6.2
>
> Any help would be greatly Appreciated.
>
> Matt Dormer
>

RE: Writing Large Objects to Postgresql via ODBC using VB

From
"Hiroshi Inoue"
Date:
> -----Original Message-----
> From: Matthew Dormer
>
> I am having problems writing to a postgres database using ODBC.  Here are
> the steps I have taken:
>
> 1.  I have greated a lo type in the database
>
> create type lo (
> internallength=4, externallength=10,
> input=int4in, output=int4out,
> default='', passedbyvalue
> );
>
>
> 2.  I create a table in the database
>
> Create table matt ( matt_id integer not null unique primary key,
> matt_text
> lo );
>
> 3.  I have a simple form with 1 rich text box, RichTextBox1 and a command
> button Command 1  Here is the code on the form:
>

Hmm you are using RDO.
Are you setting rdoEnvironments.CursorDriver to rdUseOdbc ?

regards,
Hiroshi Inoue


RE: Writing Large Objects to Postgresql via ODBC using VB

From
Matthew Dormer
Date:
At 12:15 AM 5/5/01 +0900, you wrote:
> > -----Original Message-----
> > From: Matthew Dormer
> >
> > I am having problems writing to a postgres database using ODBC.  Here are
> > the steps I have taken:
> >
> > 1.  I have greated a lo type in the database
> >
> > create type lo (
> > internallength=4, externallength=10,
> > input=int4in, output=int4out,
> > default='', passedbyvalue
> > );
> >
> >
> > 2.  I create a table in the database
> >
> > Create table matt ( matt_id integer not null unique primary key,
> > matt_text
> > lo );
> >
> > 3.  I have a simple form with 1 rich text box, RichTextBox1 and a command
> > button Command 1  Here is the code on the form:
> >
>
>Hmm you are using RDO.
>Are you setting rdoEnvironments.CursorDriver to rdUseOdbc ?
>
>regards,
>Hiroshi Inoue
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Don't 'kill -9' the postmaster
When I connect to the database I use:


     Dim Server As New Database_Servers
     Dim Connect_String As String

     If Server.dbDriver(DBid) = "SQL Server" Then

         Connect_String = "UID=" & Server.User_Name(DBid) & _
                      ";PWD=" & Server.User_Password(DBid) & _
                      ";Driver={" & Server.dbDriver(DBid) & _
                      "};SERVER=" & Server.dbServer(DBid) & _
                      "," & Server.dbServerPort(DBid) & _
                      ";DATABASE=" & Server.dbName(DBid)

     Else

         Connect_String = "UID=" & Server.User_Name(DBid) & _
                      ";PWD=" & Server.User_Password(DBid) & _
                      ";Driver={" & Server.dbDriver(DBid) & _
                      "};SERVER=" & Server.dbServer(DBid) & _
                      ";DATABASE=" & Server.dbName(DBid)

     End If

     Set en = rdoEnvironments(0)
     en.CursorDriver = rdUseOdbc
     Set cn = en.OpenConnection( _
         dsname:="", _
         Connect:=Connect_String _
         , Prompt:=rdDriverNoPrompt)

Ok... I added in the en=CoursorDriver = rdUseODBC and now I am getting:

S1090: [Microsoft][ODBC Driver Manager] Invalid String or buffer length

Any Ideas?

Matt.


Re: RE: Writing Large Objects to Postgresql via ODBC using VB

From
Matthew Dormer
Date:
At 08:57 AM 5/4/01 +0100, you wrote:
>Hello,
>         I'm not sure of your problem, but 6.5 seems a little old so maybe an
>update would help?
>         Also you don't give you primary key a value (and its set to Not
>Null) so this look to be a problem. Try having a look at the serial type
>rather than integer for matt.matt_id
>Hope this helps,
>- Stuart
>
>P.S. do you need to do anything like rst.Edit using before entering data?
>
> > -----Original Message-----
> > From: Matthew Dormer [SMTP:matt@infosource.com.au]
> > Sent: Wednesday, May 02, 2001 7:32 AM
> > To:   pgsql-odbc@postgresql.org
> > Subject:      Writing Large Objects to Postgresql via ODBC using VB
> >
> > I am having problems writing to a postgres database using ODBC.  Here are
> > the steps I have taken:
> >
> > 1.  I have greated a lo type in the database
> >
> > create type lo (
> > internallength=4, externallength=10,
> > input=int4in, output=int4out,
> > default='', passedbyvalue
> > );
> >
> >
> > 2.  I create a table in the database
> >
> > Create table matt ( matt_id integer not null unique primary key, matt_text
> >
> > lo );
> >
> > 3.  I have a simple form with 1 rich text box, RichTextBox1 and a command
> > button Command 1  Here is the code on the form:
> >
> >
> > Private Sub Command1_Click()
> >
> >      Dim query As rdoQuery
> >      Dim rst As rdoResultset
> >
> >      Set query = cn.CreateQuery("Add_Pic", "select * from matt;")
> >      Set rst = query.OpenResultset(1, 3)
> >
> >      rst.AddNew
> >      Save_RichText RichTextBox1, rst![matt_pic]
> >      rst.Update
> >
> > End Sub
> >
> >
> > Public Function Save_RichText(TextBox1 As RichTextBox, WhatField As
> > rdoColumn)
> >
> >      Dim DataFile As Integer ' Free File Number fo Reading
> >      Dim Fl As Long ' Length of the File
> >      Dim Chunks As Integer ' The Number of Chunks
> >      Dim Fragment As Integer ' The Size of the Remainder
> >      Dim Chunk() As Byte ' To hold each Chunk
> >      Dim I As Integer ' Variable for For Loop
> >
> >      Const FileName = "c:\tmpsavetext.rtf"
> >      Const ChunkSize As Integer = 1024
> >
> >      TextBox1.SaveFile FileName, rtfRTF
> >      DataFile = FreeFile
> >
> >      Open FileName For Binary Access Read As DataFile
> >      Fl = LOF(DataFile)    ' Length of data in file
> >      If Fl = 0 Then Close DataFile: Exit Function
> >      Chunks = Fl \ ChunkSize
> >      Fragment = Fl Mod ChunkSize
> >
> >      'Put Null into Field
> >      WhatField.AppendChunk Null
> >
> >      'Get the Fragment
> >      ReDim Chunk(Fragment)
> >      Get DataFile, , Chunk()
> >      WhatField.AppendChunk Chunk()
> >
> >      'Get the Chunks
> >      ReDim Chunk(ChunkSize)
> >      For I = 1 To Chunks
> >          Get DataFile, , Chunk()
> >          WhatField.AppendChunk Chunk()
> >      Next I
> >
> >      Close DataFile
> >      Kill FileName
> >
> > End Function
> >
> >
> > What is wrong with the above.  when I run it i get the error:
> >
> > S1C00: Only SQL_POSITION/REFRESH is supported for SQLSetPos
> >
> > I am running postgresql 6.5.3, PostgreSQL ODBC Driver v 6.50.0000 and am
> > using vb 6.0 Enterprise. The postgresql was installed from a rpm and is
> > running on RedHat 6.2
> >
> > Any help would be greatly Appreciated.
> >
> > Matt Dormer
> >
>
>---------------------------(end of broadcast)---------------------------
>TIP 5: Have you checked our extensive FAQ?
>
>http://www.postgresql.org/users-lounge/docs/faq.html


Sorry that was a problem with that code.  I modified it after I sent the
E-Mail.

the new code:

     Dim query As rdoQuery
     Dim rst As rdoResultset

     'Set query = cn.CreateQuery("New_Pic", "INSERT into matt (matt_id)
Values (1);")
     'query.Execute
     'query.Close
     Set query = cn.CreateQuery("Add_Pic", "select * from matt where
matt_id = 1;")
     Set rst = query.OpenResultset(1, 3)

     If rst.RowCount > 0 Then

         rst.MoveFirst
         rst.Edit
         Save_RichText RichTextBox1, rst![matt_pic]
         rst.Update

     End If

     rst.Close
     query.Close

I open the databases like this:


     Dim Server As New Database_Servers
     Dim Connect_String As String

     If Server.dbDriver(DBid) = "SQL Server" Then

         Connect_String = "UID=" & Server.User_Name(DBid) & _
                      ";PWD=" & Server.User_Password(DBid) & _
                      ";Driver={" & Server.dbDriver(DBid) & _
                      "};SERVER=" & Server.dbServer(DBid) & _
                      "," & Server.dbServerPort(DBid) & _
                      ";DATABASE=" & Server.dbName(DBid)

     Else

         Connect_String = "UID=" & Server.User_Name(DBid) & _
                      ";PWD=" & Server.User_Password(DBid) & _
                      ";Driver={" & Server.dbDriver(DBid) & _
                      "};SERVER=" & Server.dbServer(DBid) & _
                      ";DATABASE=" & Server.dbName(DBid)

     End If

     Set en = rdoEnvironments(0)
     en.CursorDriver = rdUseOdbc
     Set cn = en.OpenConnection( _
         dsname:="", _
         Connect:=Connect_String _
         , Prompt:=rdDriverNoPrompt)



the en.CursorDriver = rdUseOdbc was a surgestion from another user and
seems to change the problem a bit.  Here is the message I get now:

S1090: [Microsoft][ODBC Driver Manager] Invalid String or buffer length

Any Ideas?

Matt.