Thread: 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
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
>
> -----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
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.
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.