OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register] 
 [Profile]   [Log in to check your private messages]   [Log in] 

[Solved] SQL insert of a memo field: is it possible?

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Macros and API
View previous topic :: View next topic  
Author Message
maccn
Newbie
Newbie


Joined: 17 Oct 2010
Posts: 2
PostPosted: Sun Oct 17, 2010 1:06 pm    Post subject: [Solved] SQL insert of a memo field: is it possible? Reply with quote
Hi everyone,
I got this piece of code to insert a record in a table copying some field from another recordset

Code:

while result.next()
  queryInsert = "INSERT INTO ""tabName2"" ( ""cognome"", ""nome"", ""note"") values ( '" & result.getString(2) & "' , '" & result.getString(3) & "', '" & result.getString(9) & "')"
  stmtInsert.executeUpdate( queryInsert )   
wend


but result.getString(9) is a Memo field (called NOTE) and whenever this field is filled with some text the sql query fails and gives me an exception error.

So.. how can I insert memo fields?
Thanks in advance,
Maccn

Last edited by maccn on Tue Oct 19, 2010 5:46 am; edited 1 time in total
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2474
Location: 3rd Rock From The Sun
PostPosted: Sun Oct 17, 2010 2:45 pm    Post subject: Reply with quote
I am not certain, but instead of using result.getString(9), try, result.getCharacterStream(9) .

Per documentation found at:

http://api.openoffice.org/docs/common/ref/com/sun/star/sdb/XColumn.html#getCharacterStream

getCharacterStrream: http://api.openoffice.org/docs/common/ref/com/sun/star/sdb/XColumn.html#getCharacterStream wrote:

getCharacterStream
::com::sun::star::io::XInputStream
getCharacterStream()
raises( ::com::sun::star::sdbc::SQLException );

Description
gets the value of a column in the current row as a stream of uninterpreted bytes. The value can then be read in chunks from the stream. This method is particularly suitable for retrieving large LONGVARCHAR values.

Note: All the data in the returned stream must be read prior to getting the value of any other column. The next call to a get method implicitly closes the stream. Also, a stream may return 0 when the method ::com::sun::star::io::XInputStream::available() is called whether there is data available or not.

Returns
the column value


Am I sure this will work, no. But it is worth a try.

Furthermore, another alternative might be rather than proceeding row by row in a while statement . . . I am wondering if your object, result was retreived via a Select statement. If so, perhaps you could code it via a different INSERT statement. I do not know the names of your initial table, nor, columns . . . but . . . substitute your real names with my made up ones below ( and if necessary, you could add a Where clause Smile ):
  1. Table: "MyTable"
  2. Field: "Field2"
  3. Field: "Field3"
  4. Field: "Field9"

Code:
queryInsert = "INSERT INTO ""tabName2"" (""cognome"",""nome"",""note"") Select ""Field2"", ""Field3"", ""Field9"" From ""MyTable"""
RowsAdded = stmtInsert.executeUpdate( queryInsert )

'<<< Optional MsgBox will show pop-up with # Rows Added
MsgBox(RowsAdded + " rows Inserted into table tabName2",0,"# Rows Added")

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your first post Title ( edit button ) if your issue has been fixed / resolved.
Back to top
View user's profile Send private message
maccn
Newbie
Newbie


Joined: 17 Oct 2010
Posts: 2
PostPosted: Tue Oct 19, 2010 5:46 am    Post subject: Reply with quote
sliderule wrote:
I am not certain, but instead of using result.getString(9), try, result.getCharacterStream(9) .

Am I sure this will work, no. But it is worth a try.


This doesn't work for me.. or simply I can't get it to work..

sliderule wrote:

Code:
queryInsert = "INSERT INTO ""tabName2"" (""cognome"",""nome"",""note"") Select ""Field2"", ""Field3"", ""Field9"" From ""MyTable"""
RowsAdded = stmtInsert.executeUpdate( queryInsert )

'<<< Optional MsgBox will show pop-up with # Rows Added
MsgBox(RowsAdded + " rows Inserted into table tabName2",0,"# Rows Added")

I hope this helps, please be sure to let me / us know.


This worked like a charm instead. Thank you sliderule.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Macros and API All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group