Lotus连接和SQL字段

Question

我正在研究Lotus 9，我在lotus数据库中几乎没有记录，并将其附加到文档中。

现在，我想将这个文档记录移到带有所有文本和数字字段等的SQL server中。我想知道是否有人可以帮助我处理附件。如何使用LotusScript或JDBC (使用LS2J代码)将附件附加到server字段类型？

我知道使用ODBC连接可以实现一些东西，但不确定如何实现。如果有人做过类似的练习，任何帮助都是非常感谢的。

下面是代码，在SQL表中，我设置了字段FileNameAtt is varbinary(max)。

Sub Initialize

    Dim cn As Variant
    Dim rs As Variant

    ' Variable declarations
    Dim file_length As Variant
    Dim NumBlocks As Variant
    Dim leftover As Variant
    Dim inBuff As Variant, outBuff() As Byte

    'VB Constants
    Const adOpenKeyset = 1
    Const adLockOptimistic = 3
    Const adTypeBinary = 1
    Const adSaveCreateOverWrite = 2
    Const adBlocksize = 4096

    'Connect to the serve server
    Set cn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")

    ' Open provider
    cn.open "SQLConnectionCSIDB", "user1", ""

    Dim session As New NotesSession
    Set stream = session.CreateStream
    Call stream.open("C:\\Image1.PNG")
    file_length=stream.Bytes

    If file_length > 0 Then
        num_blocks = Int(file_length / adBlocksize)
        left_over = file_length Mod adBlocksize

        Call rs.Open("SELECT TOP 1 * FROM tblFiles", cn, adOpenKeyset, adLockOptimistic)

        rs.AddNew
        'rs.Fields("dsize").Value = file_length
        rs.Fields("FileNameAtt").Value ="C:\\Image1.PNG"

        ' Get blocks of the file
        ReDim outBuff(adBlocksize)
        For block_num = 1 To num_blocks
            ' Get data into a variant
            inBuff = stream.Read(adBlocksize)

            ' Move to byte array
            counter% = 0
            ForAll b In inBuff
                outBuff(counter%) = b
                counter% = counter% + 1
            End ForAll

            ' Save to field
            rs.Fields("dimage").AppendChunk(outBuff)
        Next block_num

        ' Get the remainder of the file
        If left_over > 0 Then
            inBuff = stream.Read(left_over) 

            ' Move to byte array
            counter% = 0
            ReDim outBuff(left_over)
            ForAll b In inBuff
                outBuff(counter%) = b
                counter% = counter% + 1
            End ForAll

            ' Save to field
            rs.Fields("dimage").AppendChunk(outBuff)
        End If  

        rs.Update
        Call rs.Close
    End If

    Call cn.Close
    Set stream = Nothing
    Set session = Nothing
End Sub

它在行号处给出了如下错误

rs.Fields("FileNameAtt").Value ="C:\\Image1.PNG"

Microsoft驱动程序提供程序:多步OLE DB操作生成错误，如果未完成工作，请检查每个OLE DB状态值。

谢人

Answer 1

无法通过NotesSQL访问附件。

LotusScript可以使用NotesEmbeddedObject类和NotesDocument、NotesRichTextItem和NotesRichTextNavigator类中的相关方法处理附件。这些相同的功能也可以通过Lotus类为用VB、C#和其他语言编写的程序使用，也可以使用等效的Java类。

Answer 2

我在您的代码中修正了一些变量，并使其能够工作。

这是我的特工：

Option Public
Option Declare

Sub Initialize
    
    Dim session As New NotesSession
    Dim db As NotesDatabase

    Dim cn As Variant
    Dim rs As Variant
    
    ' Variable declarations
    Dim file_length As Variant
    Dim num_blocks As Variant
    Dim left_over As Variant
    Dim inBuff As Variant, outBuff() As Byte

    'VB Constants
    Const adOpenKeyset = 1
    Const adLockOptimistic = 3
    Const adTypeBinary = 1
    Const adSaveCreateOverWrite = 2
    Const adBlocksize = 4096
    
    'Connect to the serve server
    Set cn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
    
    cn.ConnectionString = "Provider=SQLNCLI11;" _  
    & "Server=(localdb)\.;" _  
    & "Database=Test;" _   
    & "Integrated Security=SSPI;"
    
    '& "Trusted_Connection=yes;"   
    
    ' Open provider
    cn.open 
    Dim stream As Variant
    Set stream = session.CreateStream
    Call stream.open("c:\Temp\Image.jpg")
    file_length=stream.Bytes
        
    If file_length > 0 Then
        num_blocks = Int(file_length / adBlocksize)
        left_over = file_length Mod adBlocksize
        
        Call rs.Open("SELECT * FROM Files", cn, adOpenKeyset, adLockOptimistic)

        rs.AddNew
        rs.Fields("Id").Value = 1

        ' Get blocks of the file
        ReDim outBuff(adBlocksize)
        dim block_num As Integer
        Dim counter%
        For block_num = 1 To num_blocks
            ' Get data into a variant
            inBuff = stream.Read(adBlocksize)

            ' Move to byte array
            counter% = 0
            ForAll b In inBuff
                outBuff(counter%) = b
                counter% = counter% + 1
            End ForAll

            ' Save to field
            rs.Fields("Data").AppendChunk(outBuff)
        Next block_num

        ' Get the remainder of the file
        If left_over > 0 Then
            inBuff = stream.Read(left_over) 

            ' Move to byte array
            counter% = 0
            ReDim outBuff(left_over)
            ForAll b In inBuff
                outBuff(counter%) = b
                counter% = counter% + 1
            End ForAll

            ' Save to field
            rs.Fields("Data").AppendChunk(outBuff)
        End If  

        rs.Update
        Call rs.Close
    End If

    Call cn.Close
    Set stream = Nothing
    Set session = Nothing
    
End Sub

表Files具有以下结构：

Id: int PK
Data: varbinary(max)

由此产生的排：

​

​

检查Lotus中有关附件的其他答案，并使用LotusScript搜索附件导出。没那么复杂。