dcsimg
Results 1 to 35 of 35

Thread: Implementation of RPC server side code

  1. #1

    Thread Starter
    Hyperactive Member Peekay's Avatar
    Join Date
    Sep 2006
    Location
    Witbank, South Africa
    Posts
    362

    Implementation of RPC server side code

    When I open an ADO recordset on the server side, it gives me an Automation Error regardless of the actual error.
    Is there a way to get the real server side error on the client side?
    Thanks
    PK

  2. #2
    PowerPoster
    Join Date
    Jun 2013
    Posts
    4,375

    Re: Implementation of RPC server side code

    Please show the complete Handler-Routine you are using.

    Olaf

  3. #3

    Thread Starter
    Hyperactive Member Peekay's Avatar
    Join Date
    Sep 2006
    Location
    Witbank, South Africa
    Posts
    362

    Re: Implementation of RPC server side code

    Here it is:

    Code:
    Public Sub GetRsByQueryID()
    Dim Cnn As ADODB.Connection, rsADO As ADODB.Recordset, SQL As String, i As Integer, RsGetRecordset As New ADODB.Recordset, QueryID As String
        
        QueryID = RsIn(1)
        SQL = Query(QueryID)
        
        Set Cnn = OpenCnn(strConn)
        If Cnn.State = adStateOpen Then
            Select Case RsIn.Fields.Count - 1
                Case 1: Set RsOut = modADODB.GetRs(OpenCnn(strConn), SQL)
                Case 2: Set RsOut = modADODB.GetRs(OpenCnn(strConn), SQL, RsIn(2).Value)
                Case 3: Set RsOut = modADODB.GetRs(OpenCnn(strConn), SQL, RsIn(2).Value, RsIn(3).Value)
                Case 4: Set RsOut = modADODB.GetRs(OpenCnn(strConn), SQL, RsIn(2).Value, RsIn(3).Value, RsIn(4).Value)
                Case 5: Set RsOut = modADODB.GetRs(OpenCnn(strConn), SQL, RsIn(2).Value, RsIn(3).Value, RsIn(4).Value, RsIn(5).Value)
            End Select
            
            If RsOut.State = adStateOpen Then
                If RsOut.RecordCount = 0 Then Set RsOut = CreateResultRs("GetRsByQueryID", "No records found")
            Else
                Set RsOut = CreateResultRs("GetRsByQueryID", "Recordset could not be opened")
            End If
        Else
            Set RsOut = CreateResultRs("GetRsByQueryID", "Connection could not be opened")
        End If
    
    Exit Sub
    
    End Sub
    This is the function I call it with in modRemoteDB on the server side which looks like this:

    Code:
    Public Function GetRsWithQueryID(MyID As Integer, ParamArray P()) As ADODB.Recordset
      Dim Rs As ADODB.Recordset
      Select Case UBound(P)
        Case -1: Set Rs = RPC.DoRPC("GetRsByQueryID", MyID)
        Case 0:  Set Rs = RPC.DoRPC("GetRsByQueryID", MyID, P(0))
        Case 1:  Set Rs = RPC.DoRPC("GetRsByQueryID", MyID, P(0), P(1))
        Case 2:  Set Rs = RPC.DoRPC("GetRsByQueryID", MyID, P(0), P(1), P(2))
        Case 3:  Set Rs = RPC.DoRPC("GetRsByQueryID", MyID, P(0), P(1), P(2), P(3))
        Case 4:  Set Rs = RPC.DoRPC("GetRsByQueryID", MyID, P(0), P(1), P(2), P(3), P(4))
        Case 5:  Set Rs = RPC.DoRPC("GetRsByQueryID", MyID, P(0), P(1), P(2), P(3), P(4), P(5))
        Case 6:  Set Rs = RPC.DoRPC("GetRsByQueryID", MyID, P(0), P(1), P(2), P(3), P(4), P(5), P(6))
      End Select
      If NoErrorIn(Rs) Then Set GetRsWithQueryID = Rs
    End Function

    I can see it fetches the recordset without error.

    When I open the recordset on the localhost server in immediate mode, then I can see the error, but what if it is a remote server?
    I have saved all the queries by ID on the server in modQueries.Query to save latency delays - a function which returns the correct SQL. However, when there is an error in a query, I need to know on the client side what the error is (I think).

    PK

  4. #4
    PowerPoster
    Join Date
    Jun 2013
    Posts
    4,375

    Re: Implementation of RPC server side code

    Quote Originally Posted by Peekay View Post
    Code:
    Public Sub GetRsByQueryID()
    Dim Cnn As ADODB.Connection, rsADO As ADODB.Recordset, SQL As String, i As Integer, RsGetRecordset As New ADODB.Recordset, QueryID As String
        
        QueryID = RsIn(1)
        SQL = Query(QueryID)
        
        Set Cnn = OpenCnn(strConn)
        If Cnn.State = adStateOpen Then
            Select Case RsIn.Fields.Count - 1
                Case 1: Set RsOut = modADODB.GetRs(OpenCnn(strConn), SQL)
                Case 2: Set RsOut = modADODB.GetRs(OpenCnn(strConn), SQL, RsIn(2).Value)
                Case 3: Set RsOut = modADODB.GetRs(OpenCnn(strConn), SQL, RsIn(2).Value, RsIn(3).Value)
                Case 4: Set RsOut = modADODB.GetRs(OpenCnn(strConn), SQL, RsIn(2).Value, RsIn(3).Value, RsIn(4).Value)
                Case 5: Set RsOut = modADODB.GetRs(OpenCnn(strConn), SQL, RsIn(2).Value, RsIn(3).Value, RsIn(4).Value, RsIn(5).Value)
            End Select
            
            If RsOut.State = adStateOpen Then
                If RsOut.RecordCount = 0 Then Set RsOut = CreateResultRs("GetRsByQueryID", "No records found")
            Else
                Set RsOut = CreateResultRs("GetRsByQueryID", "Recordset could not be opened")
            End If
        Else
            Set RsOut = CreateResultRs("GetRsByQueryID", "Connection could not be opened")
        End If
    
    Exit Sub
    
    End Sub
    Code:
    Public Sub GetRsByQueryID()
    'Dim Cnn As ADODB.Connection, rsADO As ADODB.Recordset, SQL As String, i  As Integer, RsGetRecordset As New ADODB.Recordset, QueryID As String
    'QueryID = RsIn(1)
    'SQL = Query(QueryID)
    
      On Error Resume Next
      Dim SQL As String: SQL = Query(RsIn(1).Value)
      
    '    Set Cnn = OpenCnn(strConn)
    '    If Cnn.State = adStateOpen Then
            Select Case RsIn.Fields.Count - 1
                Case 1: Set RsOut = modADODB.GetRs(OpenCnn(strConn), SQL)
                Case 2: Set RsOut = modADODB.GetRs(OpenCnn(strConn), SQL, RsIn(2).Value)
                Case 3: Set RsOut = modADODB.GetRs(OpenCnn(strConn), SQL, RsIn(2).Value, RsIn(3).Value)
                Case 4: Set RsOut = modADODB.GetRs(OpenCnn(strConn), SQL, RsIn(2).Value, RsIn(3).Value, RsIn(4).Value)
                Case 5: Set RsOut = modADODB.GetRs(OpenCnn(strConn), SQL,  RsIn(2).Value, RsIn(3).Value, RsIn(4).Value, RsIn(5).Value)
                Case 6: Set RsOut = modADODB.GetRs(OpenCnn(strConn), SQL,  RsIn(2).Value, RsIn(3).Value, RsIn(4).Value, RsIn(5).Value, RsIn(6).Value)
                Case 7: Set RsOut = modADODB.GetRs(OpenCnn(strConn), SQL,  RsIn(2).Value, RsIn(3).Value, RsIn(4).Value, RsIn(5).Value, RsIn(6).Value, RsIn(7).Value)
                Case 8: Set RsOut = modADODB.GetRs(OpenCnn(strConn), SQL,  RsIn(2).Value, RsIn(3).Value, RsIn(4).Value, RsIn(5).Value, RsIn(6).Value, RsIn(7).Value, RsIn(8).Value)
            End Select
            
    '        If RsOut.State = adStateOpen Then
    '            If RsOut.RecordCount = 0 Then Set RsOut = CreateResultRs("GetRsByQueryID", "No records found")
    '        Else
    '            Set RsOut = CreateResultRs("GetRsByQueryID", "Recordset could not be opened")
    '        End If
    '    Else
    '        Set RsOut = CreateResultRs("GetRsByQueryID", "Connection could not be opened")
    '    End If
    
    'Exit Sub
    End Sub
    The above contains the fixed version, which will now do proper Error-Backtransport from the Serverside to the Clientside...
    The blue parts are new additions (you've forgot to prolong the optional Param-Handling-Cascade, so that the ParamCount matches the Clientside)
    And everything I've commented out, is not needed at all - especially all these checks which test adStateOpen.

    Please play the Error-Transport through:
    - first by defining a faulty connection-string
    - and in a second step (with a valid connection-string), but a faulty SQL-command (e.g. with an TableName which does not exist).

    The automatic Error-transport is already built-in (into the surrounding Handler-Code in cASP.cls),
    so you don't have to check for error-states in your own Handler-Routines explicitely.

    Quote Originally Posted by Peekay View Post
    This is the function I call it with in modRemoteDB on the server side which looks like this:

    Code:
    Public Function GetRsWithQueryID(MyID As Integer, ParamArray P()) As ADODB.Recordset
      Dim Rs As ADODB.Recordset
      Select Case UBound(P)
        Case -1: Set Rs = RPC.DoRPC("GetRsByQueryID", MyID)
        Case 0:  Set Rs = RPC.DoRPC("GetRsByQueryID", MyID, P(0))
        Case 1:  Set Rs = RPC.DoRPC("GetRsByQueryID", MyID, P(0), P(1))
        Case 2:  Set Rs = RPC.DoRPC("GetRsByQueryID", MyID, P(0), P(1), P(2))
        Case 3:  Set Rs = RPC.DoRPC("GetRsByQueryID", MyID, P(0), P(1), P(2), P(3))
        Case 4:  Set Rs = RPC.DoRPC("GetRsByQueryID", MyID, P(0), P(1), P(2), P(3), P(4))
        Case 5:  Set Rs = RPC.DoRPC("GetRsByQueryID", MyID, P(0), P(1), P(2), P(3), P(4), P(5))
        Case 6:  Set Rs = RPC.DoRPC("GetRsByQueryID", MyID, P(0), P(1), P(2), P(3), P(4), P(5), P(6))
      End Select
      If NoErrorIn(Rs) Then Set GetRsWithQueryID = Rs
    End Function
    The above routine looks OK to me (you only forgot to enhance the serverside routine further above about the max.7 ParamCount) -
    One thing though... (since it does RPC.DoRPC-calls) - it should definitely be located at the clientside, in your Form-Project...
    (as should be the helper-module modRemoteDB.bas, where this routine should be placed in..)

    Quote Originally Posted by Peekay View Post
    When I open the recordset on the localhost server in immediate mode, then I can see the error, but what if it is a remote server?
    As said, the Error-Backtransport from serverside Handler-Routines is already built-in - and done automatically.
    The only thing you need to add in serverside Handler-Subs which call Sub-Routines from within, is an additional On Error Resume Next on top...

    That said, I strongly recommend, that you do not use these (now fixed) Extra-Functions.
    Having to place and predefine all possible SQL-commands under a certain ID, within serverside code -
    is not necessary - and would (everytime you enhance your client about a new SQL-command) require you:
    - to recompile also your Server-Dll
    - to find a way to stop the IIS at the site of your Internet-Provider (so that the COM-Dll is freed and unloaded)
    - then to upload your new compiled Server-Dll (which now contains your additional "ID'ed SQL-Snippet"), overwriting the older COM-dll
    - and then restart the IIS-service again...


    So please - try to listen - (since I'm repeating again):
    At the serverside (and thus implicitely at the clientside, which does the related DoRPC-calls) -
    there is only two Functions needed: GetRs and UpdateBatch, to cover any possible CRUD-scenario.
    (exactly as they were defined in my last code-handout in the other thread),

    That means, that your SQL-Defs should be defined-in and also used-from within the clientside-Project
    (which later on will be separated out of the current Debug-Group - and compiled into a single client-installable Exe).
    This way only your client-installs will be in need of an update (when you enhance on the SQL-functionality) -
    but the server-dll can remain unchanged at the IIS-end (avoiding all the steps I've marked in magenta above).

    Risk-wise (in case you were trying for "more security" with those SQL-strings at the serverside):

    SQL-injection was already covered "by other means" (by using GetRs with an optional Parameter-list - instead of doing concatenations in the SQL-command itself).
    And as soon as you can switch to https-protocol (by applying a certificate on your IIS-installation), no man-in-the-middle attack - and no "listening on the wire" will be possible anymore.

    HTH

    Olaf

  5. #5

    Thread Starter
    Hyperactive Member Peekay's Avatar
    Join Date
    Sep 2006
    Location
    Witbank, South Africa
    Posts
    362

    Re: Implementation of RPC server side code

    Very lucid, thank you Olaf. I will implement.
    PK

  6. #6

    Thread Starter
    Hyperactive Member Peekay's Avatar
    Join Date
    Sep 2006
    Location
    Witbank, South Africa
    Posts
    362

    Re: Implementation of RPC server side code

    I find something amiss in the code.

    When I use your GetRs function in module modRemoteDB which is on the client side and I follow it through, I can see the following:

    1. There is no error in opening the recordset in modADODB on the server side and I can read the values of the GetRs recordset in that in the immediate window.
    2. When I then go to the Handler I can read the recordset values in RsOut in the immediate window.
    3. When I then go to GetByteContentFromRs in modRsSerialization on the serverside, I can see the recordset values in that stream.
    4. When I then return to GetRs in the clientside modRemoteDB, I find that the recordset Rs is Nothing.

    I do not know whether this is because you have two Public GetRs functions, one in both the client and serverside and it may become initialized at some stage which I cannot trace.

    Is there something wrong in my following of this?

    PK

  7. #7
    PowerPoster
    Join Date
    Jun 2013
    Posts
    4,375

    Re: Implementation of RPC server side code

    Quote Originally Posted by Peekay View Post
    1. There is no error in opening the recordset in modADODB on the server side and I can read the values of the GetRs recordset in that in the immediate window.
    2. When I then go to the Handler I can read the recordset values in RsOut in the immediate window.
    3. When I then go to GetByteContentFromRs in modRsSerialization on the serverside, I can see the recordset values in that stream.
    4. When I then return to GetRs in the clientside modRemoteDB, I find that the recordset Rs is Nothing.
    If the only breakpoint in the whole debug-group is on:
    End Function ... (of the clientside modRemoteDB GetRs-routine)

    ...and you still get an empty Rs, then there's something wrong in the project.

    If everything works fine instead (with only that single clientside breakpoint),
    then the problem was caused due to http-waiting for the IIs-response.

    At this point, the serverside GetRs and UpdateBatch are stable -
    so - normally you could now:
    - compile only the Dll-Project-Part of the Group
    - close the Project-Group
    - restart the IIS-instance (over the IIS-Manager-Tool)
    - restart only the client-project over its *.vbp (RPCTest.vbp)

    HTH

    Olaf

  8. #8

    Thread Starter
    Hyperactive Member Peekay's Avatar
    Join Date
    Sep 2006
    Location
    Witbank, South Africa
    Posts
    362

    Re: Implementation of RPC server side code

    Olaf,

    There is an error somewhere which I cannot trace.

    Here is the http.responsetext after filteringtags, from which I can see that there are three records in the recordset:

    Code:
    TG!     ??#  _X        g *c  ?               <?m  _X               
                             |  "\*  Dw=     ?    "        I        <?m  _X                           2          \   " D i a r y S o f t " . . " d a i l y j o u r n a l "  d a i l y j o u r n a l      ?    I D    I D           ?      D i a r y S o f t O    M y D a t e    M y D a t e ? 
             h     D i a r y S o f t   O    U s e r N o    U s e r N o           x       D i a r y S o f t   K    E n t r y    E n t r y           h       D i a r y S o f t   S    P r i v a t e    P r i v a t e                  D i a r y S o f t    2 0 1 9 - 0 5 - 1 1  May 11 entry   2 0 1 9 - 0 5 - 2 2  May 22 entry   2 0 1 9 - 0 1 - 0 6  Entry June 1 2019  
    But in the code line directly thereafter it shows the DoRPC recordset as being Nothing.

    PK

  9. #9
    PowerPoster
    Join Date
    Jun 2013
    Posts
    4,375

    Re: Implementation of RPC server side code

    Quote Originally Posted by Peekay View Post
    There is an error somewhere which I cannot trace.

    Here is the http.responsetext after filteringtags, from which I can see that there are three records in the recordset:
    As said - don't look too long (whilst in breakpoints) at the http-responses.

    The mechanism does real requests - so, try not to disturb the "flow" of the http-Response-Bytestream with breakpoints inside DoRPC...
    The whole thing wants to be treated like "Schroedingers Cat" in that regard ("you don't want to know - don't look").

    How does it behave, when you follow the last 4 instructions of my previous post - and use it in "Client-Project-only" mode?

    Olaf

  10. #10

    Thread Starter
    Hyperactive Member Peekay's Avatar
    Join Date
    Sep 2006
    Location
    Witbank, South Africa
    Posts
    362

    Re: Implementation of RPC server side code

    I have done yesterday what you advised me to do and did not interfere with any process until it reached the client GetRs function EndFunction line, but the recordset Rs received there was equal to Nothing.
    It is thus that I started to search where the recordset might be set to nothing. Everything is fine, even at the end of

    Code:
    Public Function GetRsFromByteContent(ByteData) As ADODB.Recordset'
    in modSerialization on the client side, in that the recordset GetRsFromByteContent still holds the records.
    My only deduction is that DoRPC is not set in the codeline:

    Code:
    If http.Status = 200 Then Set DoRPC = GetRsFromByteContent(http.responseBody)
    and I have checked that the status is 200.

    Thanks
    PK

  11. #11
    PowerPoster
    Join Date
    Jun 2013
    Posts
    4,375

    Re: Implementation of RPC server side code

    Quote Originally Posted by Peekay View Post
    I have done yesterday what you advised me to do and did not interfere with any process until it reached the client GetRs function EndFunction line, but the recordset Rs received there was equal to Nothing.
    That cannot be, because the DoRPC-routine is written in a way, that (even in case of an error) -
    it will always return an ADO-Recordset to you (either one which contains a single record with the Error-infos, or one with valid server-data ).

    The line prior to End Function (where your breakpoint was) is:
    If NoErrorIn(Rs) Then Set GetRs = Rs
    And NoErrorIn(Rs) should have popped up a MessageBox (with the error-info in case DoRPC returned an "Error-Rs").

    Quote Originally Posted by Peekay View Post
    It is thus that I started to search where the recordset might be set to nothing.
    An information, what the error-message was (which NoErrorIn(..) should have shown) - would have been helpful.

    Here a revised DoRPC-function, which now checks - whether the length of the received content is matching
    with the length-info from the http content-length header-field (which is the only thing I can imagine, where something in the transmission was going wrong):
    Code:
    Public Function DoRPC(ProcName As String, ParamArray P()) As Recordset
      Dim i As Long, StatTxt As String, RsParams As New ADODB.Recordset
     
      On Error Resume Next
        RsParams.Fields.Append "ProcName", adBSTR
        For i = 0 To UBound(P)
          If VarType(P(i)) = 8209 Then 'ByteArray
            RsParams.Fields.Append "P" & i + 1, adBinary, -1
          Else
            RsParams.Fields.Append "P" & i + 1, VarType(P(i))
          End If
        Next i
        RsParams.Open
      
        RsParams.AddNew
        RsParams!ProcName.Value = ProcName
        For i = 0 To UBound(P)
          RsParams.Fields(i + 1).Value = P(i)
        Next
    
        Dim http As New WinHttpRequest
            http.Open "POST", RPCUrl, True
            http.SetRequestHeader "Content-Type", "application/octet-stream"
            http.SetRequestHeader "Cache-Control", "private"
     
            http.Send GetByteContentFromRs(RsParams) '...
         
         If TimeOutSec < 3 Then TimeOutSec = 3
         
         If http.WaitForResponse(TimeOutSec) Then
            If http.Status = 200 Then
              Dim B() As Byte: B = http.ResponseBody
              If http.GetResponseHeader("content-length") = UBound(B) + 1 Then
                 Set DoRPC = GetRsFromByteContent(B)
              Else 'something was messed up in the content-transmission
                 Debug.Print http.GetResponseHeader("content-length"), UBound(B) + 1
                 Debug.Print http.GetAllResponseHeaders
                 Set DoRPC = CreateErrorRs("http-Content-mismatch, calling: " & ProcName)
              End If
            End If
         Else 'Request-TimeOut
            Set DoRPC = CreateErrorRs("http-Request-TimeOut, calling: " & ProcName)
         End If
        
        If DoRPC Is Nothing Then
          StatTxt = http.Status & " " & http.StatusText & " " & FilterTags(http.ResponseText)
          Set DoRPC = CreateErrorRs(StatTxt & " " & Err.Description)
        End If
        
        http.Abort
        Set http = Nothing
      If Err Then Err.Clear 'potential errors should be contained in the returned Rs
    End Function
    So, the new lines in DoRPC are the ones in blue, which now check whether at least the length of
    the received content matches with the length-info of the http-response-header...
    To not try to deserialize an ADO-Rs from a potentially incomplete or "cutoff" ByteStream.
    In practice I've so far never ran into things like that, because I formulate my SQL-Selects in a way,
    that the amount of "Rs-Bytes" the IIS has to transfer, is within "reasonable limits" (the ASP-sendbuffer-size is by default at 4MB IIRC).

    The red(ish) marked lines in the DoRPC-routine above are not "new ones" BTW - they are the reason, why I told you that DoRPC is basically "unable",
    not to return a valid Recordset to you...

    Maybe you've posted an SQL-Select of the kind: "Select * From Table"...
    nothing wrong with it, when such a thing is targeting "smaller helper-tables with Enum-like values" -
    but when targetting your "huge tables" you should now be careful, to ensure:
    - that you specify a FieldList with only the Fields you need at the moment (at the clientside)
    - and that you also apply a decent Where-clause, which restricts the amount of returned Records to a reasonable size
    (and "a reasonable size" is one, which in serialized format does not go much above 1MB).

    To give concrete numbers from a concrete example...
    the "Select * From Invoices" request (against the NWind-DemoDB), will be serialized into:
    - ~1MB (991183 Bytes http-response content-length)
    - whilst having a "mixed set" of 26 Column-Types
    - on 2155 returned Records
    And that amount transfers "still well enough" (also depending a bit on your wire-speed)

    So, try to ensure, that you stay roughly within these limits...
    - 200 Fields x 300 records
    - 20 Fields x 3000 records
    - 2 Fields x 30000 records (e.g. for longer ID, NameField listings)
    ... you get the idea...

    Olaf

  12. #12

    Thread Starter
    Hyperactive Member Peekay's Avatar
    Join Date
    Sep 2006
    Location
    Witbank, South Africa
    Posts
    362

    Re: Implementation of RPC server side code

    The line prior to End Function (where your breakpoint was) is:
    If NoErrorIn(Rs) Then Set GetRs = Rs
    And NoErrorIn(Rs) should have popped up a MessageBox (with the error-info in case DoRPC returned an "Error-Rs").
    Indeed, it gave a fatal error as the recordset Rs passed to it is equal Nothing, so I stop before that.

    Maybe you've posted an SQL-Select of the kind: "Select * From Table"...
    Indeed I used that to keep it simple, but I have now changed it by using actual field names and it gives me the same error.

    Here a revised DoRPC-function, which now checks - whether the length of the received content is matching
    with the length-info from the http content-length header-field (which is the only thing I can imagine, where something in the transmission was going wrong):
    I have used your new DoRPC function, but it gives no errors and does not print anything in the debug window.

    For the sake of completeness I have run the process with a few Debug.Print code lines inserted at strategic places in order to show you how the recordset values travel through the process.
    I am also giving you my table and table data used here.


    Name:  MSMSWindowSQLSVR.jpg
Views: 210
Size:  18.5 KB

    Code:
    Value of input parameters in DoRPC on client side:      GetRs         Select dailyjournal.id, mydate, users.username, entry, private from dailyjournal left join users on users.id=dailyjournal.userno WHERE username=?      Peka
    Value of RsIn in Handler on server side:   GetRs            Select dailyjournal.id, mydate, users.username, entry, private from dailyjournal left join users on users.id=dailyjournal.userno WHERE username=?    Peka
    Value of recordset after being opened in GetRs on server side:         2   2019-05-11  Peka        May 11 entry     False
    Value of RsOut in Handler on server side:    2          2019-05-11      PekaMay 11 entry False
    Value of  http.responseBody in DoRPC on client side:     ??! ?????????   `g???????? ?? ? ` A???????? ?A ? ?A ?  ?  ?  ?  ?  ?|???????? ?? ?"   I   ????????        2   ????????????????????????????? ?A??\"DiarySoft".."dailyjournal"dailyjournal ??idid   ?    DiarySoft????mydatemydate?
       h  DiarySoft ???usernameusername   h     DiarySoft ???entryentry   h   DiarySoft ???privateprivate      DiarySoft ???IDID   ?     DiarySoft? '? ??? ?????????????yA???2019-05-22????????? '? ??? ?? ????????????? 
    Value of GetRsFromByteContent in modRsSerializing on client side:       2 2019-05-11  Peka            May 11 entry   False
    Value of  StatText and Error.Description when DoRPC is equal to nothing in DoRPC on client side:     200 OK TG!     ??#  _X        g *c  ?                   Type mismatch
    Last edited by Peekay; Jun 6th, 2019 at 06:20 AM.

  13. #13
    PowerPoster
    Join Date
    Jun 2013
    Posts
    4,375

    Re: Implementation of RPC server side code

    Quote Originally Posted by Peekay View Post
    Value of StatText and Error.Description when DoRPC is equal to nothing in DoRPC on client side:
    200 OK TG! ??ύ# _X g *cϰ ? Type mismatch
    Ok, now with that Err.Description (Type mismatch) there's a clearer Picture, what happens...

    And what happens is, that you have (in all likelihood) a DAO-reference somewhere in your Test-Project (in addition to the needed ADO-ref.).

    So, the only function, which I forgot to give a "fully qualified" Object-Type in its return-value (via LibName.ClassName)...:
    Code:
    Public Function DoRPC(ProcName As String, ParamArray P()) As Recordset
    ...is currently choking, because it tries to assing an ADO-Recordset to a DAO-Recordset-Type.

    Easiest way to fix that, would be to use the full type-qualifier in the return-type of the above function-signature:
    Code:
    Public Function DoRPC(ProcName As String, ParamArray P()) As ADODB.Recordset
    Of course, removing the (not needed) DAO-reference from your Test-Project would help as well in that regard.
    (as would the "Up-shifting" of the ADO-Reference-entry, until it sits above the DAO-reference, by using those little Arrow-Buttons in the appropriate References-Dialogue,
    in case you need both references in your Project for some reason).

    Olaf

  14. #14

    Thread Starter
    Hyperactive Member Peekay's Avatar
    Join Date
    Sep 2006
    Location
    Witbank, South Africa
    Posts
    362

    Re: Implementation of RPC server side code

    It works beautifully, thanks so much.
    PK

  15. #15

    Thread Starter
    Hyperactive Member Peekay's Avatar
    Join Date
    Sep 2006
    Location
    Witbank, South Africa
    Posts
    362

    Re: Implementation of RPC server side code

    Incredible technology this!

    Two questions if I may:
    1. I find it difficult to search. You said that I do not need any apostrophes or % before or after the search text inserted as ?. The way I search is 'WHERE upper(fieldname) LIKE ? .... I pass it the search text as the parameter UCASE(txtSearch.text). Although it does not give me an error, it also does not give me the record in which the search text appears. If you can elucidate on that please.
    2. When I compile the Server dll, where in my inetpub wwwroot folder do I place it? You said I do not need to register it on the remote server. Presently I have an asp folder under the root folder wherein serverlib.asp is stored.

    Thanks
    PK

  16. #16
    PowerPoster
    Join Date
    Jun 2013
    Posts
    4,375

    Re: Implementation of RPC server side code

    Quote Originally Posted by Peekay View Post
    Two questions if I may:
    1. I find it difficult to search. You said that I do not need any apostrophes or % before or after the search text inserted as ?. The way I search is 'WHERE upper(fieldname) LIKE ? .... I pass it the search text as the parameter UCASE(txtSearch.text).
    Right, no apostrophes are needed... also for Like expressions, which should be written:
    - in the constant SQL-String as: "Select * From Table Where FieldName Like ?"
    - and the SearchParam you pass separately should be prefixed or suffixed by you, e.g. passing -> "%" & txtSearch.Text & "%"
    Note, that I left out the Upper-function in the SQL and also the UCase in the SearchString, because Like usually works case-insensitive in most DB-Engines by default.

    Quote Originally Posted by Peekay View Post
    2. When I compile the Server dll, where in my inetpub wwwroot folder do I place it? You said I do not need to register it on the remote server. Presently I have an asp folder under the root folder wherein serverlib.asp is stored.
    Are you already at this stage (with regards to "proper testing"), to think about the RollOut of the Dll - to the real WebServer-instance which is "publically reachable on the Internet"?

    Well, things to consider before you make that final (mainly administrative) step...
    - Is https-mode (for secure transfers) already configured and available on that IIS-instance?
    - Is the default-Application-Pool of that remote IIS-instance already switched to 32Bit-Mode?
    - Is the SQLServer-instance on that remote-host available and correctly set up and configured (DB-Schema-wise with "all the right Tables and Indexes", and will your Connection-String in the Server-Dll work)?
    - Do you not need any other functions in your Server-Dll (e.g. File-Upload or -Download - or Authentication-stuff), which should better be inserted and tested in cHandler.cls, as long as you run the Debug-ProjectGroup?

    As for the Paths...
    IIRC - the little ServerLib.asp VBScript (which does the re-directing into the serverside COM-Dll) was placed in an \asp\ SubFolder directly under wwwroot.
    For the COM-Dll I'd ensure a Folder beside \asp\ - also under wwwroot - e.g. named \bin\ or \lib\ or \api\ ... make sure, that new Folder got Read/Write and Execute-rights.

    The Debug-Asp-Script on your local Dev-machine (where you run your Debug-Group) had the following content:
    Code:
    <% @EnableSessionState=False %>
    <%
       On Error Resume Next
          Server.CreateObject("ServerLib.cASP").HandleRequest Application, Server, Request, Response
       If Err Then Response.Write "Error: " & Err.Description
    %>
    For regfree mode on IIS (assuming you've compiled your Dll as ServerLib.dll) - this Scripts content should become:
    Code:
    <% @EnableSessionState=False %>
    <%
      On Error Resume Next
      'try to create the SxS-Object - and provide it with the Libs-manifest-file
      Dim SxS: Set SxS = Server.CreateObject("Microsoft.Windows.ActCtx") 
          SxS.Manifest = Server.MapPath("/bin/ServerLib.manifest")
    
          'now instantiate a cASP-instance regfree via SxS - and call its HandleRequest-routine
          SxS.CreateObject("ServerLib.cASP").HandleRequest Application, Server, Request, Response
     
      If Err Then Response.Write "Error: " & Err.Description
    %>
    And as said - your IIS-AppPool should be configured for 32Bit-mode, oherwise the 32Bit-Dlls VB6 produces cannot be loaded...

    What remains, is the content of the ServerLib.manifest File (which is in XML-Format - and contains only a few lines).
    In that Files XML-content, you will have to specify the ClsID of your ServerLib.dll - ...
    ... and this Dll-ClsID might change, when you re-compile the Server-Dll (in case you're not using Project- or Binary-Compatibility in your Dll-Project-Settings).
    So I'd suggest, that we talk about that part, when your ServerLib.dll really has "all the functionality you ever dreamed of" - and is well-tested.

    HTH

    Olaf

  17. #17

    Thread Starter
    Hyperactive Member Peekay's Avatar
    Join Date
    Sep 2006
    Location
    Witbank, South Africa
    Posts
    362

    Re: Implementation of RPC server side code

    That's good thanks.
    PK

  18. #18

    Thread Starter
    Hyperactive Member Peekay's Avatar
    Join Date
    Sep 2006
    Location
    Witbank, South Africa
    Posts
    362

    Re: Implementation of RPC server side code

    I wish to get the ID of the last record created and use this:

    Code:
    Set rsADOtemp = modRemoteDB.GetRs("Select @@Identity as ID")
    But, it returns a null value?
    Is there any other way I can get it?

    PK

  19. #19

    Thread Starter
    Hyperactive Member Peekay's Avatar
    Join Date
    Sep 2006
    Location
    Witbank, South Africa
    Posts
    362

    Re: Implementation of RPC server side code

    I have found that this one works.

    Code:
    "SELECT IDENT_CURRENT('Tablename')"

  20. #20

    Thread Starter
    Hyperactive Member Peekay's Avatar
    Join Date
    Sep 2006
    Location
    Witbank, South Africa
    Posts
    362

    Re: Implementation of RPC server side code

    I get an error which seems unlikely:

    I call this from my program:

    Code:
                    Fields_Values.Add "ParticipantName", UserName
                    Fields_Values.Add "AppointNo", CLng(fgAll.TextMatrix(fgRow, 0))
                    If Not AddNewRecordToTable("Participants", Fields_Values) Then WarnUser Failure, " Could not update participant.": Exit Sub
    When I add a new row, it sends back the BatchUpdate in this routine on the serverside as false:

    Code:
    Public Function UpdateBatch(Cnn As ADODB.Connection, Rs As ADODB.Recordset) As Boolean
      Set Rs.ActiveConnection = Cnn
          Rs.UpdateBatch
      If TrueDisconnectedMode Then Set Rs.ActiveConnection = Nothing
      UpdateBatch = True
    End Function
    The Rs recordset passed to this routine is:

    Code:
    for n=1 to 2:Print rs.Fields(n).value:next n
     2 
    Peka
    In the following routine on the client side:

    Code:
    Public Function UpdateBatch(Rs As adodb.Recordset) As Boolean
      Dim RsBoolResult As adodb.Recordset
      Set RsBoolResult = RPC.DoRPC("UpdateBatch", GetByteContentFromRs(Rs)) 'pass the serialized Rs-Bytes to UpdateBatch
      UpdateBatch = NoErrorIn(RsBoolResult)
    End Function
    It receives these values:

    Code:
    for n=0 to 8:Print rs.Fields(n).name:next n
    ID
    AppointNo
    ParticipantName
    Accepted
    Closed
    SMSsent
    SMSinviteSent
    Cancelled
    SMScancelSent
    
    for n=1 to 2:Print rs.Fields(n).value:next n
     2 
    Peka
    The error it sends is 'Subscript out of range.

    Here are the values in the client modRemoteDB.AddNewRecordToTable routine:

    Code:
    print f_v.count
     2 
    print f_v.Item("ParticipantName")
    Peka
    print f_v.Item("AppointNo")
     2 
    print tablename
    Participants
    for n=0 to rs.Fields.Count-1:print rs.Fields(n).Name,rs.Fields(n).Value:next n
    ID        
    AppointNo    2 
    ParticipantName         Peka
    Accepted   
    Closed     
    SMSsent    
    SMSinviteSent
    AppointCancelled        
    SMScancelSent
    Here is my table:

    Name:  Participants table.jpg
Views: 176
Size:  38.6 KB
    Last edited by Peekay; Jun 12th, 2019 at 11:32 PM.

  21. #21

    Thread Starter
    Hyperactive Member Peekay's Avatar
    Join Date
    Sep 2006
    Location
    Witbank, South Africa
    Posts
    362

    Re: Implementation of RPC server side code

    Ok I have solved this error thanks.
    The error was that I tried to update a record without updating the non null Boolean fields, not realizing that SQL Server Boolean fields could be set to be not null as I was advised, which is not the case with Access.

    I started this thread with this question:

    When I open an ADO recordset on the server side, it gives me an Automation Error regardless of the actual error.
    Is there a way to get the real server side error on the client side?
    Thanks
    PK
    The problem I have is that the server side does not transmit the correct error, so it is quite difficult to debug. I understand partly why.

    PK

  22. #22
    PowerPoster
    Join Date
    Jun 2013
    Posts
    4,375

    Re: Implementation of RPC server side code

    The DBEngine does not give many infos in case of a failing UpdateBatch (meaning, that the Info you got, was the correct serverside error).
    For UpdateBatch to work properly, you will have to take care of proper Table-Defs.

    E.g. all Fields you have defined with "Don't allow Nulls" - should be:
    - either be remembered by you - when you define FieldValues for inserting a new Record at the clientside (those NonNull-Values will have to be filled "by hand" then)
    - Or - properly define a Default-Value for these NonNull-Fields in the Table-Schema - because then the Rs.AddNew-call at the clientside should normally "prefill" these Fields correctly with their Default-Value (in case you forgot to fill-in an appropriate Value "by hand" at the clientside) - or if that Prefilling of the Rs is not happening at the clientside already, at least the serverside UpdateBatch-call should not choke in these cases, and use the default-value in case you left fields at Null when filling the clientside-Rs)
    - also in case of ID-As Integer Fields - please make sure, that these have "Autoincrement-behaviour", because otherwise you will have to "prefill" them at the clientside as well

    HTH

    Olaf
    Last edited by Schmidt; Jun 13th, 2019 at 08:49 AM.

  23. #23

    Thread Starter
    Hyperactive Member Peekay's Avatar
    Join Date
    Sep 2006
    Location
    Witbank, South Africa
    Posts
    362

    Re: Implementation of RPC server side code

    Thanks so much. I understand now.
    PK

  24. #24
    PowerPoster
    Join Date
    Jun 2013
    Posts
    4,375

    Re: Implementation of RPC server side code

    JFYI ...
    Since the two clientside Helper-Functions (in modRemoteDB.bas):
    - AddNewRecordToTable ... and
    - UpdateRecordInTable
    ... work "single-record-based" with new Fields_Values passed via a Scripting-Dictionary ...

    ... you could always write two Form-specific Helper-Functions, named e.g.:
    - Public Function GetFieldValuesForInsert() As Scripting.Dictionary
    - Public Function GetFieldValuesForUpdate() As Scripting.Dictionary
    ... defined Public at Form-Level.

    That way you could then use the two modRemoteDB-Functions in always the same way
    (from different places within the given Form, but also from outside a given Form - e.g. when multiple Forms are open in a Master-Detail-data-entry-scenario):
    - modRemoteDB.AddNewRecordToTable "Participants", frmParticipants.GetFieldValuesForInsert
    - or basically the same thing with modRemoteDB.UpdateRecordInTable "Participants", ..., frmParticipants.GetFieldValuesForUpdate, ...

    Or sometimes you have multiple instances of a Private UserControl on a Form (e.g. ucAddressEntry to cover multiple Addresses for a given Customer).
    Here you could follow the same logic... (using and defining the two Public Dictionary-returning helper-Functions within the UserControl).
    And a Form with e.g. 3 of these Address-Entry-Controls on it, could then just call (in case all 3 were filled with new Data):
    modRemoteDB.UpdateRecordInTable "Addresses", ..., ucAddrBusiness.GetFieldValuesForUpdate, ...
    modRemoteDB.UpdateRecordInTable "Addresses", ..., ucAddrPrivate.GetFieldValuesForUpdate, ...
    modRemoteDB.UpdateRecordInTable "Addresses", ..., ucAddrInvoice.GetFieldValuesForUpdate, ...

    I think, you get the idea...

    Ok, here an example for GetFieldValuesForInsert, as you could e.g. define it in your frmParticipants.frm:
    Code:
    Public Function GetFieldValuesForInsert() As Scripting.Dictionary
      Set GetFieldValuesForInsert = New Scripting.Dictionary
      With GetFieldValuesForInsert
        .Add "ParticipantName", UserName
        .Add "AppointNo", CLng(fgAll.TextMatrix(fgRow, 0))
        'a.s.o.
      End With
    End Function
    Just a recommendation, how to make your Forms and Private UserControl-based Entry-areas more generically usable regarding their "Control-to-DBFieldName-mappings",
    each Form or Control always defining these two Dictionary-returning-Functions with always the same naming-convention (only having a different "internal Field-mapping").

    Olaf

  25. #25

    Thread Starter
    Hyperactive Member Peekay's Avatar
    Join Date
    Sep 2006
    Location
    Witbank, South Africa
    Posts
    362

    Re: Implementation of RPC server side code

    These are very helpful routines. I will try them out.
    Thank you!
    PK

  26. #26

    Thread Starter
    Hyperactive Member Peekay's Avatar
    Join Date
    Sep 2006
    Location
    Witbank, South Africa
    Posts
    362

    Re: Implementation of RPC server side code

    I wish to implement this code line which lets me view a file from within my program, by using the associated program to view it, but I wish to do it from the remote server:

    Code:
    ShellExecute Me.hWnd, vbNullString, App.Path & "\HelpFiles\Doc06 IBSMS Help overview.pdf", vbNullString, vbNullString, vbNormalFocus
    Must I use a downloader program or can it be done via this RPC technique?

    PK

  27. #27
    Frenzied Member wqweto's Avatar
    Join Date
    May 2011
    Posts
    1,535

    Re: Implementation of RPC server side code

    Quote Originally Posted by Peekay View Post
    Must I use a downloader program or can it be done via this RPC technique?PK
    In your case the RPC technique can be used only if the default viewer implements the very RPC technique. Does it? Can it read PDF data from anything but a file? Does it support anonymous pipes for instance? Or URLs perhaps?

    cheers,
    </wqw>

  28. #28

    Thread Starter
    Hyperactive Member Peekay's Avatar
    Join Date
    Sep 2006
    Location
    Witbank, South Africa
    Posts
    362

    Re: Implementation of RPC server side code

    wqweto,

    I cannot reply to any of your questions as I am trying to figure out which processes and architecture are available for such an implementation.
    What I wish to know is if I can write RPC client side and/or server side routines to implement this and how it looks like.

    PK

  29. #29
    PowerPoster
    Join Date
    Jun 2013
    Posts
    4,375

    Re: Implementation of RPC server side code

    Quote Originally Posted by Peekay View Post
    I cannot reply to any of your questions as I am trying to figure out which processes and architecture are available for such an implementation.
    What wqweto meant is, that ShellExecute will start an associated App-Process, which is able to handle the passed DocumentFilename(-Resource).
    Some PDF-Viewers might support the http-protocol (so that your priorily uploaded PDF-File could be downloaded directly) -
    but that's not something you can rely on...
    Better to download the File yourself (via the http51 <-> IIS based RCP-mechanism which is the base for your current project,
    and which was described here: http://www.vbforums.com/showthread.p...g-of-http-RPCs

    Quote Originally Posted by Peekay View Post
    What I wish to know is if I can write RPC client side and/or server side routines to implement this and how it looks like.
    And so we come back to "ByteArrays-Transfers" (one of your favourite topics, I'm sure)

    If the filesizes will always be smaller than - say - 4MB (please check that, because some PDFs can be quite large, e.g. when they contain a lot of ScreenShots and stuff) -
    then you could use the client- and serverside routines I've already posted in #80 here:
    http://www.vbforums.com/showthread.p...=1#post5392203

    If the File is larger, then you will have to write a quite similar pair of routines,
    where the FileName-Param is accompanied by e.g. two more Arguments: a FileOffset and a BufferSize

    With these 3 Params you could then (at the serverside) e.g. simply use "Standard VB6 Binary FileMode" via:
    FreeFile FNr
    Open FileName For Binary As FNr
    Redim ByteArray(0 To Buffersize -1)
    Get FNr, OneBasedOffset, ByteArray
    Close FNr
    -> now return the ByteArray-Chunk via the usual Transport-Rs-Mechanism.

    At the clientside you would then download e.g. 2MB-chunks in a loop,
    appending the chunks to a temp-file (which when finished gets shell-executed).

    Olaf

  30. #30

    Thread Starter
    Hyperactive Member Peekay's Avatar
    Join Date
    Sep 2006
    Location
    Witbank, South Africa
    Posts
    362

    Re: Implementation of RPC server side code

    Olaf, thanks so much - very elucidating and complete!
    PK

  31. #31

    Thread Starter
    Hyperactive Member Peekay's Avatar
    Join Date
    Sep 2006
    Location
    Witbank, South Africa
    Posts
    362

    Re: Implementation of RPC server side code

    I have been able to get the byte array of the downloaded file into my client module modRemoteDB, but have been unable to save it properly.

    This is my routine:

    Code:
    Public Function ReadServerFile(FileName As String) As String
    Dim Rs As adodb.Recordset, FileOffset As Long, BufferSize As Long, B() As Byte
    Dim ByteArray() As Byte, FNr As Integer, ReturnFilename As String, FileExt As String, DecimalPos As Integer, FileSizeBytes As Long
        
        DecimalPos = InStr(FileName, ".")
        If DecimalPos = 0 Then frmDiary.WarnUser Failure, " File has no extension.": ReadServerFile = "": Exit Function
        
        FileExt = Right(FileName, Len(FileName) - DecimalPos + 1)
        ReturnFilename = App.Path & "\" & "File" & Format(Date & " " & Time, "yyyy-mm-dd HH-mm-ss") & FileExt
        FNr = FreeFile(0)
        Open ReturnFilename For Binary As FNr
        FileOffset = 0
        BufferSize = 3900
        
        FileSizeBytes = GetFileSizeOnServer(FileName)
    
        Do While FileOffset + BufferSize < FileSizeBytes
            Set Rs = RPC.DoRPC("DownloadFileContent", FileName, FileOffset, BufferSize)
        
            If NoErrorIn(Rs) Then
                ByteArray = Rs(0).Value
                Put FNr, , ByteArray
            End If
        
            FileOffset = FileOffset + BufferSize + 1
            If FileOffset >= FileSizeBytes Then Exit Do
        
        Loop
        
        Close FNr
        
        ReadServerFile = ReturnFilename
        Exit Function
        
    End Function
    The ByteArray received from the server contains this data: (the first 500 bytes)

    80 68 70 45 49 46 52 10 37 255 255 255 255 10 49 54 32 48 32 111 98 106 10 60 60 47 76 101 110 103 116 104 32 50 52 55 54 10 47 83 117 98 116 121 112 101 32 47 88 77 76 10 47 84 121 112 101 32 47 77 101 116 97 100 97 116 97 10 62 62 10 115 116 114 101 97 109 10 60 63 120 112 97 99 107 101 116 32 98 101 103 105 110 61 39 239 187 191 39 32 105 100 61 39 87 53 77 48 77 112 67 101 104 105 72 122 114 101 83 122 78 84 99 122 107 99 57 100 39 63 62 10 60 120 58 120 109 112 109 101 116 97 32 120 58 120 109 112 116 107 61 34 51 46 49 45 55 48 49 34 32 120 109 108 110 115 58 120 61 34 97 100 111 98 101 58 110 115 58 109 101 116 97 47 34 62 10 32 32 60 114 100 102 58 82 68 70 32 120 109 108 110 115 58 114 100 102 61 34 104 116 116 112 58 47 47 119 119 119 46 119 51 46 111 114 103 47 49 57 57
    57 47 48 50 47 50 50 45 114 100 102 45 115 121 110 116 97 120 45 110 115 35 34 62 10 32 32 32 32 60 114 100 102 58 68 101 115 99 114 105 112 116 105 111 110 32 114 100 102 58 97 98 111 117 116 61 34 34 32 120 109 108 110 115 58 120 109 112 61 34 104 116 116 112 58 47 47 110 115 46 97 100 111 98 101 46 99 111 109 47 120 97 112 47 49 46 48 47 34 62 10 32 32 32 32 32 32 60 120 109 112 58 67 114 101 97 116 101 68 97 116 101 62 50 48 49 56 45 49 50 45 49 49 84 49 52 58 51 51 58 53 50 90 60 47 120 109 112 58 67 114 101 97 116 101 68 97 116 101 62 10 32 32 32 32 32 32 60 120 109 112 58 67 114 101 97 116 111 114 84 111 111 108 62 78 105 116 114 111 32 80 114 111 32 55 32 32 40 55 46 32 53 46 32 48 46 32 50 57 41 60 47 120 109 112 58 67 114 101 97 116 111 114 84 111 111 108 62 10 32 32 32
    32 32 32 60 120 109 112 58 77 111 100 105 102 121 68 97 116 101 62 50 48 49 56 45 49 50 45 49 49 84 49 52 58 51 51 58 53 50
    The original document is 2689 kB and my downloaded saved file is 8 KB

    Help will be appreciated.
    Thanks.

    PK
    Last edited by Peekay; Jul 9th, 2019 at 07:58 AM.

  32. #32

    Thread Starter
    Hyperactive Member Peekay's Avatar
    Join Date
    Sep 2006
    Location
    Witbank, South Africa
    Posts
    362

    Re: Implementation of RPC server side code

    Deleted
    Last edited by Peekay; Jul 7th, 2019 at 01:12 AM.

  33. #33

    Thread Starter
    Hyperactive Member Peekay's Avatar
    Join Date
    Sep 2006
    Location
    Witbank, South Africa
    Posts
    362

    Re: Implementation of RPC server side code

    I have published above under an old post number - sorry.
    Thanks
    PK

  34. #34

    Thread Starter
    Hyperactive Member Peekay's Avatar
    Join Date
    Sep 2006
    Location
    Witbank, South Africa
    Posts
    362

    Re: Implementation of RPC server side code

    If I write bytes to a file which is larger than 4MB and the previous file offset was 4000000, what do I put in the PUT statement in the record number position Put FNr,???, ByteArray, if it was opened as binary?

    This routine is not working for me. It only downloads files with a size smaller than 4MB.:

    Code:
    Public Function ReadServerFile(FileName As String) As String
    Dim Rs As adodb.Recordset, FileOffset As Long, BufferSize As Long, B() As Byte
    Dim ByteArray() As Byte, FNr As Integer, ReturnFilename As String, FileExt As String, DecimalPos As Integer, FileSizeBytes As Long, BytesLeft As Long
        
        DecimalPos = InStr(FileName, ".")
        If DecimalPos = 0 Then frmDiary.WarnUser Failure, " File has no extension.": ReadServerFile = "": Exit Function
        
        FileExt = Right(FileName, Len(FileName) - DecimalPos + 1)
        ReturnFilename = App.Path & "\" & "File" & Format(Date & " " & Time, "yyyy-mm-dd HH-mm-ss") & FileExt
        FNr = FreeFile(0)
        Open ReturnFilename For Binary As FNr
        FileOffset = 1
        
        FileSizeBytes = GetFileSizeOnServer(FileName)
        If FileSizeBytes < 4000000 Then BufferSize = FileSizeBytes Else BufferSize = 4000000
        BytesLeft = FileSizeBytes
    
        Do While BytesLeft > 0
            ReDim ByteArray(0 To BufferSize - 1)
            Set Rs = RPC.DoRPC("DownloadFileContent", FileName, FileOffset, BufferSize)
        
            If NoErrorIn(Rs) Then
                ByteArray = Rs(0).Value
                Put FNr, FileOffset, ByteArray
            End If
        
            BytesLeft = FileSizeBytes - FileOffset - BufferSize
            If BytesLeft <= 0 Then Exit Do
            If BytesLeft < 4000000 Then BufferSize = BytesLeft Else BufferSize = 4000000
            FileOffset = FileOffset + BufferSize
            If FileOffset >= FileSizeBytes Then Exit Do
        
        Loop
        
        Close FNr
        
        ReadServerFile = ReturnFilename
        Exit Function
        
    End Function
    It seems to me that I can write the contents of a pdf file directly into the SQL Server database as a byte blob for instance. When I do that can I then get the data from the database in chunks of more than 4 MB in the format of a field in a recordset? Is it better practise to have the document on the server side as a pdf file or is it better to save it in a record in the database?

    Thanks
    PK

    PS. I am moving the last question to a new thread. This has become too long and diverse.
    PK
    Last edited by Peekay; Jul 10th, 2019 at 07:20 AM.

  35. #35

    Thread Starter
    Hyperactive Member Peekay's Avatar
    Join Date
    Sep 2006
    Location
    Witbank, South Africa
    Posts
    362

    Re: Implementation of RPC server side code

    Olaf,

    Thanks to your persistence and patience with me, I have completed the server side code complete with large file uploads and downloads, contrary to what we both thought would be the outcome.
    I am now ready to publish the DLL and will do so according to the way you set it out above.

    Thank you!!!!

    PK

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width