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

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(at)infosource(dot)com(dot)au]
> > Sent: Wednesday, May 02, 2001 7:32 AM
> > To: pgsql-odbc(at)postgresql(dot)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.