Before we move ahead, there are a few things I do not understand.
1. I understand the routine "ReflectString" quite well, because you use a string to reflect.
2. In "TransferByteArrays" you also manipulate alpha-numeric characters. Why do you use Bytes for this? I have not worked with byte arrays before, but somehow followed your explanation, but I do not follow it in a methodical or understandable way.
3. Sometimes you use RsIn(1) and RsIn(2) and other times you use Rsin.Fields(1).value. Should I not be consistent in using the same input type of ADO and abstracting data from it in a consistent manner?
4. I understand partly that we will mostly work with multiple strings as parameters, so I understand that we need to work in arrays, but I am not sure what is the purpose of working in arrays in one place and with fields in another.
1. I understand the routine "ReflectString" quite well, because you use a string to reflect.
That's good, because a String is basically the same as a ByteArray (an allocation of consecutive memory - containing a sequence of bytes aka "chars").
Originally Posted by Peekay
2. In "TransferByteArrays" you also manipulate alpha-numeric characters. Why do you use Bytes for this?
I have not worked with byte arrays before, but somehow followed your explanation, but I do not follow it in a methodical or understandable way.
The emphasis is on "Transfer" in this case -
the "manipulation of content" (no matter if the byte-sequence is in a string or a bytearray) is not that important -
it's only there as a kind of "proof", that the passed String or ByteArray was "indeed received and touched" at the serverside...
Originally Posted by Peekay
3. Sometimes you use RsIn(1) and RsIn(2) and other times you use Rsin.Fields(1).value.
Should I not be consistent in using the same input type of ADO and abstracting data from it in a consistent manner?
When you read out a Value like that:
SomeValue = RsIn(1)
then this is absolutely the same as:
SomeValue = RsIn.Fields(1).Value
because .Fields is a default-property of the ADO-Recordset-Class - and .Value is a Default-Property of the ADO-Field-Class (which you've addressed via the parentheses).
(the VB6-compiler kind of "auto-expands" the short-form under the covers).
If you are into "writing explicit, nice code", then use the longer (expanded form).
Originally Posted by Peekay
4. I understand partly that we will mostly work with multiple strings as parameters,
so I understand that we need to work in arrays,
but I am not sure what is the purpose of working in arrays in one place and with fields in another.
The necessity to work with "Rs-Fields" comes from choosing ADO-Rs as the transport-container-format for Input- and Output of the RPC-http-Requests
(due to the reason, that an ADO-Rs can take up multiple Field-Types in its Variant-Fields - but especially because an ADO-Rs can be serialized to a "sequence of Bytes",
which is a necessity, when you transfer "stuff over sockets/network-connections".
As for the similarities between ByteArrays and Strings - you might take a good long look at the following:
Code:
Private Sub Form_Load()
Dim S As String, B() As Byte
'Unicode-WChar-Mode (16Bit=2Bytes per Char)
S = "ABC"
B = S 'implicit conversion from String to ByteArray-Type (making a content-copy)
Debug.Print B, S, UBound(B) + 1, LenB(S)
'explicit filling of B in unicode-mode
ReDim B(0 To 5) 'Bytes = 6, WChars = 3
B(0) = 65
B(2) = 66
B(4) = 67
Debug.Print B, S, UBound(B) + 1, LenB(S)
'ANSI-Char-Mode (8Bit=1Byte per Char)
S = StrConv(S, vbFromUnicode) 'make an ANSI-String from the Unicode-String
B = S 'implicit conversion from String to ByteArray-Type (making a content-copy)
Debug.Print StrConv(B, vbUnicode), StrConv(S, vbUnicode), UBound(B) + 1, LenB(S)
'explicit filling of B in ANSI-mode
ReDim B(0 To 2) 'Bytes = 3, Chars = 3
B(0) = 65
B(1) = 66
B(2) = 67
Debug.Print StrConv(B, vbUnicode), StrConv(S, vbUnicode), UBound(B) + 1, LenB(S)
End Sub
Here is my code for returning the same string and I can see the Resultset is correct, but it does not print the result in the grid, but prints and error number in stead:
Public Sub TransferString()
Dim S As String
S = RsIn.Fields(1).Value
Set RsOut = CreateResultRs("TransferString", S)
End Sub
Public Sub TransferString()
Dim S As String
S = RsIn.Fields(1).Value
Set RsOut = CreateResultRs("TransferString", S)
End Sub
Ok, the above looks right now (for transferring or "roundtripping" a simple String).
Now show me your implementation for an appropriately named serverside HandlerRoutine, which does the same for the ByteArray-Type.
As for the Errors you might see at the clientside...
These might occur, when you spend too many seconds in the Serverside routines in "Break-Point-Mode" -
because the clientside (async) httprequest might have run into a timeout in the meantime.
If you remove all breakpoints, and start the whole thing from a correctly written clientside DoRPC-call,
you should see correct results coming in at the clientside (in your Form-Code).
Public Sub TransferByteString()
Dim S As String, B() As Byte
B = StrConv(RsIn.Fields(1).Value, vbUnicode)
S = B
Set RsOut = CreateResultRs("TransferByteString", S)
End Sub
However, for some reason my http Send code line gives a 404 error, and I cannot fathom why all of a sudden.
Public Sub TransferByteString()
Dim S As String, B() As Byte
B = StrConv(RsIn.Fields(1).Value, vbUnicode)
S = B
Set RsOut = CreateResultRs("TransferByteString", S)
End Sub
However, for some reason my http Send code line gives a 404 error, and I cannot fathom why all of a sudden.
In that case, you will have to ensure proper http-request-serving again...
http-error 404 means "resource not found"...
...and that in turn means, that the RPC-Request-URL you've speicified in your Client (fTest.frm) Form_Load Event:
Code:
Private Sub Form_Load()
RPC.RPCUrl = "http://localhost/asp/ServerLib.asp"
...
...cannot be resolved (was not found) by your local IIS-instance on your Test-machine.
So you will have to make sure, that the ServerLib.asp File exists in your: "C:\InetPub\wwwroot\asp" folder.
Thanks Olaf, that is the problem.
IIS suddenly disappeared from my Folders and Admin tools. I wonder if it has to do with installing the Chrome virus.
You had an excellent tutorial about installing IIS for Asp, before you published this one:
I cannot find the thread. Can you please help with a link so that I can do the installation properly? One of the problems I run into is that I do not have authority to make folders under wwwroot.
Private Sub Command1_Click()
Dim B() As Byte, RsResult As ADODB.Recordset, S As String
ReDim B(0 To 5)
S = "QWERTY"
B() = S
Set RsResult = RPC.DoRPC("TransferByteString", B())
B = RsResult(0).Value 'get the resulting ByteArray-Blob from the ResultRs-Field 0
Debug.Print B
End Sub
Here is my Handler:
Public Sub TransferByteString()
Dim S As String, B() As Byte
B = StrConv(RsIn.Fields(1).Value, vbUnicode)
S = B
Set RsOut = CreateResultRs("TransferByteString", S)
End Sub
And here is my debug.print from the click event:
Last edited by Shaggy Hiker; May 12th, 2019 at 08:36 AM.
Reason: Added CODE tags.
Yep - now remember, that all the String-to-ByteArray stuff is not really what we need
(it was just for the bettering of understanding "how to work with bytes", and "why a ByteArray is not that much different from a String").
What we now need, is (without using any String-Variables):
- Upload of a ByteArray (from the clientside)
- Download of a ByteArray (from the serverside)
So we are back at the task where we did start (in post #23).
Show me, that you can transfer binary data (in ByteArrays) in both directions
(from client to server - and from the server to the client) ...
because that's a prerequisite, to finally transfer ADO-Recordsets in both directions.
Private Sub Command1_Click()
Dim B() As Byte, RsResult As ADODB.Recordset
ReDim B(0 To 5)
B(0) = Asc("Q"): B(1) = Asc("W"): B(2) = Asc("E"): B(3) = Asc("R"): B(4) = Asc("T"): B(5) = Asc("Y")
Debug.Print "Sending from Client in Bytes: " & B(0), B(1), B(2), B(3), B(4), B(5)
Set RsResult = RPC.DoRPC("TransferByteSentence", B())
Debug.Print "Received at client in ASCII: " & StrConv(RsResult(0), vbUnicode)
B = RsResult(0).Value 'get the resulting ByteArray-Blob from the ResultRs-Field 0
Debug.Print B
End Sub
Here is my Handler routine:
Code:
Public Sub TransferByteSentence()
Dim B() As Byte, i As Integer
B = StrConv(RsIn.Fields(1).Value, vbUnicode)
Debug.Print "Received in handler: ";: For i = 0 To 11: Debug.Print B(i);: Next i: Debug.Print
Dim C() As Byte
ReDim C(0 To 5)
C(0) = Asc("Y"): C(1) = Asc("T"): C(2) = Asc("R"): C(3) = Asc("E"): C(4) = Asc("W"): C(5) = Asc("Q")
Dim FldNames: FldNames = Array("1", "2", "3", "4", "5")
Dim FldValues: FldValues = C()
Debug.Print "Sent from Handler: " & C(0), C(1), C(2), C(3), C(4), C(5)
Set RsOut = CreateResultRs("TransferByteSentence", C)
End Sub
See my results above.
Last edited by Shaggy Hiker; May 12th, 2019 at 08:37 AM.
Reason: Added CODE tags.
Public Sub AddNewRow()
Dim Params As Integer, i As Integer, Query As String, Cmd As ADODB.Command
OpenMainConnection
Query = "INSERT INTO " & RsIn.Fields(1).Value & " (" & RsIn.Fields(2).Value & "," & RsIn.Fields(4).Value & "," & RsIn.Fields(6).Value & ") VALUES (" & RsIn.Fields(3).Value & "," & RsIn.Fields(5).Value & "," & RsIn.Fields(6).Value & ")"
Set Cmd = New ADODB.Command
Cmd.CommandText = Query
Cmd.ActiveConnection = Conn
Conn.Command = Cmd.CommandText
Conn.Execute (Cmd.CommandText)
Conn.Close
Set RsOut = CreateResultRs("AddNewRow", "Successful")
End Sub
Private Sub OpenMainConnection()
On Error GoTo ConnectError
Set Conn = New ADODB.Connection
Conn.CursorLocation = adUseServer
Conn.ConnectionString = strConn
Conn.Open
On Error GoTo 0
Exit Sub
ConnectError:
MsgBox "Database connection failed."
On Error GoTo 0
Exit Sub
End Sub
For example:
- the usage of the Cmd-Object is not correct (will not prevent SQL-injection)
- the incoming Params are not mapped to procedure-local Variables (from RsIn)
- the OpenConn-Routine should not contain a MsgBox-call
..(it's code, which is run "headless" at the serverside - so unless you know about the "unattended execution"-setting in AX-Dlls, you should avoid showing any "blocking GUI-PopUps")
What I'd prefer is, when you could concentrate on the ByteArray-stuff again
(last shown in your post #55).
The Handler-Routine does still contain unnecessary code
(e.g. FldNames and FldValues Variables are defined and filled, but then not used - they are not needed at all).
Remember, that our goal is, to transfer ByteArrays efficiently
(to be able to later use these ByteArrays, to transport serialized ADO-Rs).
Whats needed in the end, to completely cover any CRUD-scenario is only two Handler-Functions:
- GetRs(SQL, BoundParams,...) ... returning a ByteArray with the serialized Rs, which was selected according to the SQL-const-string and the following Params
- UpdateBatch(ByteArray_Of_A_SerializedRs) ... returning a Boolean
That's it.
Below is a module (e.g. name it: modADODB.bas), which you might want to study, ... it includes these functions.
Code:
Option Explicit 'convenience-functions for Cnn-Opening + Selecting ADO-Rs + Updating ADO-Rs
Public TrueDisconnectedMode As Boolean 'should be set to True, when used at the serverside
Public Function OpenJetCnn(DBName As String, Optional UserName$, Optional PassWord$) As ADODB.Connection
Const JetOleDB$ = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
Set OpenJetCnn = OpenCnn(JetOleDB & DBName, UserName, PassWord)
End Function
Public Function OpenCnn(ConnString As String, Optional UserName$, Optional PassWord$) As ADODB.Connection
Set OpenCnn = New ADODB.Connection
OpenCnn.Open ConnString, UserName, PassWord
OpenCnn.CursorLocation = adUseClient
End Function
Public Function GetRs(Cnn As ADODB.Connection, ByVal SQL As String, ParamArray P()) As ADODB.Recordset
Set GetRs = New ADODB.Recordset
Dim Cmd As New ADODB.Command, i As Long
Set Cmd.ActiveConnection = Cnn
Cmd.CommandText = SQL
For i = 0 To UBound(P)
Select Case VarType(P(i))
Case vbString: Cmd.Parameters.Append Cmd.CreateParameter(, adVarChar, , 4096, P(i))
Case Else: Cmd.Parameters.Append Cmd.CreateParameter(, VarType(P(i)), , , P(i))
End Select
Next
GetRs.CursorLocation = adUseClient
GetRs.Open Cmd, , adOpenStatic, adLockBatchOptimistic
GetRs.Properties("Update Criteria") = adCriteriaKey 'allow only PKs, to locate Rows for Updates in the Where-Clause
If TrueDisconnectedMode Then Set GetRs.ActiveConnection = Nothing
Set Cmd.ActiveConnection = Nothing
End Function
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
First step should be, that you try to concentrate on the GetRs-Handler-Function first.
(selecting a given Ado-Rs - by delegating the incoming RPC-Paramters from RsIn to the modADODB.GetRs-function)
Please show me your first version of the clientside DoRPC-triggering - and the appropriate Handler-Method which wraps the GetRs-function.
I have tried and I think succeeded in sending a byte array in post 55 to DoRPC, but I do not know how to transform the following into a byte array if you wish me to send the parameters to DoRPC only in byte arrays?
If I have to do that, I presume that I will have to have seven byte arrays, each with the code for each letter of the words I am sending or one byte array with a row and a column like in B(1,1).
I presume that I should do this first as these are the parameters I will send from my program, or do I send it as a string array to be serialized, as the DoRPC function also accepts parameters of other variable types?
1) I edited a few posts to add [CODE][/CODE] tags. You can do this by pressing the # button and pasting code between the resulting tags. The formatting is much nicer that way.
2) This really should have been a second thread. It has wandered far from the original question, and now has nothing at all to do with connection strings. Since it was the thread starter that did the wandering, they can go where they will, but as a general rule, you'll get more eyes on the question if you start a new thread with a new question, and getting eyes on the question is the best way to get the best answers.
Thanks for that. I only learned this week how to use the #.
I understand your concern in your second comment, and it bothers me somewhat to keep coming back here for some un related subjects.
This should have been a Code Project and really belongs in the Code Bank, but that started out as such on another thread and ended unresolved, but ... I am asking a lot of newbie questions here which I do not want to form part of the Code Bank.
If Olaf is OK with this, I think we can pick out some useful parts from both threads to form a unified Coding thread for the Code bank, which can be helpful to a lot of members.
Public Sub CrudOperations()
Dim B() As Byte, i As Integer, strConn As String, Cnn As ADODB.Connection, rsADO As ADODB.Recordset, SQL As String
B = StrConv(RsIn.Fields(1).Value, vbUnicode)
strConn = "Provider=SQLOLEDB;Data Source=.\MSSQLEXP2014;Initial Catalog=*******;Integrated Security=SSPI" 'for localhost
SQL = "SELECT * FROM dailyjournal order by date"
Set Cnn = OpenCnn(strConn)
If Cnn.State = adStateOpen Then
Set rsADO = GetRs(Cnn, SQL, B())
If rsADO.State = adStateOpen Then
Set RsOut = CreateResultRs("CrudOperations", rsADO)
Else
Set RsOut = CreateResultRs("CrudOperations", "Recordset not opened")
End If
Else
Set RsOut = CreateResultRs("CrudOperations", "Connection not opened")
End If
End Function
Private Sub cmdAddRow_Click()
Dim S(0 To 6) As String, B() As Byte, RsResult As ADODB.Recordset
S(0) = "DailyJournal": S(1) = "Date": S(2) = Date: S(3) = "UserNo": S(4) = 1: S(5) = "Entry": S(6) = "May 11 entry"
Set RsResult = RPC.DoRPC("AddNewRow", S(1), S(2), S(3), S(4), S(5), S(6))
Debug.Print "Received at client in ASCII: " & StrConv(RsResult(0), vbUnicode)
B = RsResult(0).Value 'get the resulting ByteArray-Blob from the ResultRs-Field 0
'Debug.Print B
VisualizeRs RsResult
End Sub
In the Handler:
Code:
Public Sub AddNewRow()
Dim Cnn As ADODB.Connection, rsADO As ADODB.Recordset, SQL As String, i As Integer, RsSend As New ADODB.Recordset, Updated As Boolean
SQL = Query(1)
Debug.Print RsIn(1), RsIn(2), RsIn(3), RsIn(4), RsIn(5), RsIn(6)
Set Cnn = OpenCnn(strConn)
If Cnn.State = adStateOpen Then
Updated = UpdateBatch(Cnn, RsIn)
If Updated = True Then
Set RsOut = CreateResultRs("AddNewRow", "Row added")
Else
Set RsOut = CreateResultRs("AddNewRow", "Row not added")
End If
Else
Set RsOut = CreateResultRs("AddNewRow", "Connection not opened")
End If
End Sub
In a new server module:
Code:
Option Explicit
Public Const strConn = "Provider=SQLOLEDB;Data Source=.\MSSQLEXP2014;Initial Catalog=*******;Integrated Security=SSPI" 'for localhost
Public Function Query(QueryNo As Integer) As String
Select Case QueryNo
Case 1
Query = "SELECT id, date, users.username, entry, private FROM dailyjournal LEFT JOIN users on users.id=dailyjournal.userno"
End Select
End Function
My understanding of the project we are busy working with, it is to prevent SQL injection.
Where this injection can originate and how it is done, I do not know, because by serializing the data sent to the server, no hacker can understand or fiddle with that while it is on its way to the server, where it creates an object.
How will a hacker know which objects to create once some injection gets there?
Once arriving at the server, why can I not just use the values in RsIn as it comes in, or just convert it to local strings?
That way I can write server side routines like the ones you have done in post #58.
What I can do presently is send strings (or with some superhuman effort even bytes maybe) to DoRPC where it is serialized and sent to the server.
On arriving at the server it is de-serialized and I can read the strings again and do what is needed to do the CRUD operations, for sure not as efficiently as you have done it in post #58.
I need some direction please if you do not mind. I realize you are very busy, and to work with newbies, like me, is sometimes a waste of time.
Private Sub cmdAddRow_Click()
Dim S(0 To 9) As String, B() As Byte, C() As Byte, D() As Byte, E() As Byte, F() As Byte, G() As Byte, H() As Byte, K() As Byte, RsResult As ADODB.Recordset, i As Integer
S(0) = "TableName": S(1) = "DailyJournal": S(2) = "Date": S(3) = Date: S(4) = "UserNo": S(5) = 1: S(6) = "Entry": S(7) = "May 11 entry"
B = S(0): C = S(1): D = S(2): E = S(3): F = S(4): G = S(5): H = S(6): K = S(7)
Set RsResult = RPC.DoRPC("AddNewRow", B, C, D, E, F, G, H, K)
Debug.Print "Received at client in ASCII: " & StrConv(RsResult(0), vbUnicode)
B = RsResult(0).Value 'get the resulting ByteArray-Blob from the ResultRs-Field 0
'Debug.Print B
VisualizeRs RsResult
End Sub
In the Handler I have this:
Code:
Public Sub AddNewRow()
Dim Cnn As ADODB.Connection, rsADO As ADODB.Recordset, SQL As String, k As Integer, RsQuery As New ADODB.Recordset, Updated As Boolean
SQL = Query(1)
k = 1
Do While k <= RsIn.Fields.Count - 1
If VarType(RsIn(k)) = 8209 Then 'ByteArray
RsQuery.Fields.Append RsIn.Fields(k).Value, adBinary, -1
Else
RsQuery.Fields.Append RsIn.Fields(k).Value, VarType(RsIn(k))
End If
k = k + 2
Loop
RsQuery.Open
RsQuery.AddNew
k = 2
Do While k <= RsIn.Fields.Count - 1
RsQuery.Fields(k / 2 - 1).Value = RsIn(k)
k = k + 2
Loop
Debug.Print "RsIn field values: ", RsIn(1), RsIn(2), RsIn(3), RsIn(4), RsIn(5), RsIn(6), RsIn(7), RsIn(8)
Debug.Print "RsQuery field names: ", RsQuery.Fields(0).Name, RsQuery.Fields(1).Name, RsQuery.Fields(2).Name, RsQuery.Fields(3).Name
Debug.Print "RsQuery field values: ", RsQuery.Fields(0).Value, RsQuery.Fields(1).Value, RsQuery.Fields(2).Value, RsQuery.Fields(3).Value
Set Cnn = OpenCnn(strConn)
If Cnn.State = adStateOpen Then
Updated = UpdateBatch(Cnn, RsQuery)
If Updated = True Then
Set RsOut = CreateResultRs("AddNewRow", "Row added")
Else
Set RsOut = CreateResultRs("AddNewRow", "Row not added")
End If
Else
Set RsOut = CreateResultRs("AddNewRow", "Connection not opened")
End If
End Sub
In the Hanlder I get these values:
Code:
RsIn field values: TableName DailyJournal Date 21-May-19 UserNo 1 Entry May 11 entry
RsQuery field names: TableName Date UserNo Entry
RsQuery field values: DailyJournal 21-May-19 1 May 11 entry
Believe me, I'm already trying my best - but it seems like you don't even read what I write...
E.g. in thread-entry #58:
- I've posted a code-module (modADODB.bas, to be included in the Serverside-Dll-Project-part of the VBGroup-Project we use for Debugging).
- This module is capable to handle all the needed serverside DB-interaction
- The only thing missing, is to "use this module properly" within the serverside cHandler.cls
- by implementing exactly two (very small) Handler-Routines (in the usual manner within cHandler.cls - using a Sub - and in this Sub, RsIn and RsOut)
Here an exact citation, what I wrote in post #58:
------------------------------ Whats needed in the end, to completely cover any CRUD-scenario is only two Handler-Functions:
- GetRs(SQL, BoundParams,...) ... returning a ByteArray with the serialized Rs, which was selected according to the SQL-const-string and the following Params
- UpdateBatch(ByteArray_Of_A_SerializedRs) ... returning a Boolean
I also wrote (at the end of post #58): First step should be, that you try to concentrate on the GetRs-Handler-Function first.
(selecting a given Ado-Rs - by delegating the incoming RPC-Paramters from RsIn to the modADODB.GetRs-function)
Please show me your first version of the clientside DoRPC-triggering - and the appropriate Handler-Method which wraps the GetRs-function.
-------------------------------
If there is any problem with the text-citations above (words or terms you don't understand), you'll have to let me know about it.
I seriously don't know anymore, how to respond or reply - because you seem to misunderstand a whole lot (due to knowledge-gaps, I guess).
But other than you telling me - where those gaps are (where I lost you), we will not make any further progress with this...
So, what I'd like you to do is, to concentrate on the part I've marked blue above:
- a functionality, which is able to select an Rs at the serverside - and transfer it back to the clientside
- since this singular function(ality) is a Remote-Call, we will have to write two parts (to properly transport In-Params - and the Out-Result)
1) the clientside Triggering (In-Parameter-Passing into DoRPC), where our RemoteCall starts
2) the serverside receiving of those In-Parameters (in a Handler-Function, which will receive these In-Params the clientside sent to us, as usual in RsIn)
3) the serverside execution within the Handler-Routine (acting on these In-Params -preparing to get an Out-Result)
4) the serverside OutResult-Handling, so that we "get it savely and easily" back to the clientside
What are the In-Params for GetRs?
I wrote that above: 1. SQL, 2. an optional "BoundParameters"-List (bound to the SQL, to avoid SQL-injection)
If you want, you can leave out Part 2. in your first try - and send only a single Param to the GetRs-Handler (1. SQL)
Believe me, I'm already trying my best - but it seems like you don't even read what I write...
Thank you so much - I appreciate. I do read every sentence many times over, but some times I do not understand it well.
- I've posted a code-module (modADODB.bas, to be included in the Serverside-Dll-Project-part of the VBGroup-Project we use for Debugging).
I have included this new routine in my server side code previously already . I did not show it here, because I copied that correctly from your code. In entry#65 I am calling that module, but I see that I called the incorrect routine, as I tried adding a new row in stead of getting a new recordset.
- This module is capable to handle all the needed serverside DB-interaction
I can see that.
- The only thing missing, is to "use this module properly" within the serverside cHandler.cls
Here is how I think it should be called from the Handler:
I need three parameters:
1. I must tell the function which connection to use. I would make a connection string in the declaration section of your module modADODB.bas
2. I must tell the function which SQL to use. I have (in my version of your modADODB.bas) added a function where the SQL queries can be read from, thereby obviating the necessity of sending them in the http.send code. I would call my SQL function in your module modADODB with a parameter indicating which SQL I want to use, numbering them 1 to 100 or more - I have 150 different functions, and therefore SQL queries, in my program. I would send the number of this SQL query via a parameter from the Form (as part of the other parameters) and the Handler will submit this parameters then to this function which will return the applicable SQL query without parameters.
3. I must send the function the SQL parameters. In my Handler in entry #65, I have the parameters for field names and field values in the recordset RsQuery. I do not know which of these I must send to the function GetRs and in which order.
If I presume that my recordset (RsQuery) contains these parameters correctly, which I think it is not, then I would call it this way:
I would open the connection with your function OpenCnn with the correct connection string, username, password etc.
I would then test whether its status is adstateopen.
Then I would pass the parameters in my points 1 to 3 above to your function GetRs
That function would return a recordset (RsOut) from the function GetRs which will be sent to the handler like in:
Code:
RsOut=GetRs(Cnn,SQL,RsQuery)
This is the best I can do at this stage. Sorry.
PK
I have gone so far with the code as CreatingParameter in:
Code:
For i = 0 To UBound(P)
Select Case VarType(P(i))
Case vbString
Cmd.Parameters.Append Cmd.CreateParameter(, adVarChar, , 4096, P(i))
Case Else
Cmd.Parameters.Append Cmd.CreateParameter(, VarType(P(i)), , , P(i))
End Select
Next
I have never worked with CreateParameters and do not know what to send to the routine parameter P(). I would think it needs a field name and a field value.
I have gone so far with the code as CreatingParameter in:
Code:
For i = 0 To UBound(P)
Select Case VarType(P(i))
Case vbString
Cmd.Parameters.Append Cmd.CreateParameter(, adVarChar, , 4096, P(i))
Case Else
Cmd.Parameters.Append Cmd.CreateParameter(, VarType(P(i)), , , P(i))
End Select
Next
I have never worked with CreateParameters and do not know what to send to the routine parameter P(). I would think it needs a field name and a field value.
If you want to get more familiar with the routines in modADODB.bas,
you should use and test them in a separate (non RPC) simple Std-Exe-Project
(which has only a Form1, the modADODB.bas + a Project-Reference to ADO).
As for ParamArray P():
This is kinda like an "entire List of Optional Variant-Params"
(allowing "an unlimited amount" of them - but also passing "none of them").
Within the Function it behaves like a Variant-Array (with zerobased indexing).
The amount of passed Params can be determined with:
Dim ParamCount: ParamCount = Ubound(P) +1
And the above works also in case no optional Param was passed into P (by the caller),
because the Ubound(P) will be -1 ... signalizing an "initialized, but yet unfilled VB-Array with no Elements".
If you have such a little StdExe-TestProject in place:
Then all you need in Form_Load is the following (please adapt the connection-string for your own MS-SQL-instance):
Code:
Private Sub Form_Load()
Dim Cnn As ADODB.Connection
Set Cnn = OpenCnn("Provider=SQLOleDB.1;Data Source=(local);Initial Catalog=NorthWnd;Integrated Security=SSPI;")
Const SQL0 = "Select 3 - 1" 'zero Parameters (no ?-Parameter-Slot was given in the SQL)
Const SQL1 = "Select ? - 1" 'SQL contains one Parameter-Slot
Const SQL2 = "Select ? - ?" 'SQL contains two Parameter-Slots
Const SQL3 = "Select ? + IIf(GetDate() Between ? And ?, 'Yes', 'No')" 'and here 3 Slots are defined
Debug.Print GetRs(Cnn, SQL0).Fields(0).Value '<- no bound Param is used here (ParamArray P() remains empty)
Debug.Print GetRs(Cnn, SQL1, 3).Fields(0).Value
Debug.Print GetRs(Cnn, SQL2, 3, 1).Fields(0).Value
Debug.Print GetRs(Cnn, SQL3, "In range? -> ", Now - 1, Now + 1).Fields(0).Value
End Sub
That shows already, how the SQL-Parameter-Binding works via GetRs(...):
- by passing the Bound-Params in the ParamArray, matching the amount of ? in the SQL-constant
- with the exception of SQL0, which is a "naked" (normal) SQL-string without ? Parameter-Slots (and an empty ParamArray P)
(please count - or read - the ?-Slots from left to right - and then pass the matching "Bound-Paramarray-Value" in the same order in the GetRs-call)
Note, that the SQL-Defs are done in Consts deliberately, to demonstrate that no change on them was taking place
(no concatenation or replacment happened on the executed SQL, which is what prevents SQL-injection).
Another side-effect of using bound Parameters via ADO-commands is, that you can input normal Values!
That's quite a relief when you consider "classic" SQL-concat-fiddling with String-Values or Date-Values
(the last query in SQL3 shows that nicely, since it gets passed 3 Params:
- a normal VB-String-Value (no requirement to enclose this in single-qutoes)
- followed by two normal VB-DateValues (which could be provided and passed from a DatePickerCtl.Value directly)
Now, when you've played around enough with the above Demo-Code (in a separate project),
we should try to get back to the topic at hand:
Your implementation of only the GetRs-Handler-Routines for the client- and the serverside
(and as suggested, please leave out the passing of Bound-Parameters for the moment, just pass a plain SQL-String to the Server -
and call modADODB.GetRs(SQL) <- without any extra-params for the moment... GetRs allows that (as the SQL0-call above shows).
Thanks, this is very lucid now, as I did not understand the '?' previously.
In this exercise I have used the SQL without parameters.
Here is my Form event:
Code:
Private Sub cmdGetRecordset_Click()
Dim S(0 To 9) As String, B() As Byte, C() As Byte, RsResult As ADODB.Recordset, i As Integer
S(0) = 1
B = S(0)
Set RsResult = RPC.DoRPC("GetRecordset", B)
Debug.Print "Received at client in ASCII: " & StrConv(RsResult(0), vbUnicode)
B = RsResult(0).Value 'get the resulting ByteArray-Blob from the ResultRs-Field 0
'Debug.Print B
VisualizeRs RsResult
End Sub
Here is my Handler:
Code:
Public Sub GetRecordset()
Dim Cnn As ADODB.Connection, rsADO As ADODB.Recordset, SQL As String, i As Integer, RsGetRecordset As New ADODB.Recordset, S(0 To 2) As String, B() As Byte, C() As Byte
Debug.Print "GetRecordset() Handler RsIn field values: ", RsIn(0), RsIn(1)
S(0) = RsIn(1)
SQL = Query(S(0))
Set Cnn = OpenCnn(strConn)
If Cnn.State = adStateOpen Then
Set RsOut = GetRs(Cnn, SQL)
If RsOut.State = adStateOpen Then
Debug.Print "GetRecordset() Handler RsOut field values: ", RsOut.Fields(0).Value, RsOut.Fields(1).Value, RsOut.Fields(2).Value, RsOut.Fields(3).Value
Else
Set RsOut = CreateResultRs("GetRecordset", "Recordset not retrieved")
End If
Else
Set RsOut = CreateResultRs("GetRecordset", "Connection not opened")
End If
Exit Sub
End Sub
Here is a function in my server side module named modQueries:
Code:
Public Function Query(QueryNo As String) As String
Dim n As Integer
n = CInt(QueryNo)
Select Case QueryNo
Case 1
Query = "SELECT dailyjournal.id, date, userno, entry, private FROM dailyjournal"
End Select
End Function
Here is my Debug.Print results:
Code:
GetRecordset() Handler RsIn field values: GetRecordset 1
GetRecordset() Handler RsOut field values: 2 2019-05-11 1 May 11 entry
Received at client in ASCII: 2
Private Sub cmdGetRecordsetWithParameters_Click()
Dim S(0 To 9) As String, B() As Byte, C() As Byte, RsResult As ADODB.Recordset, i As Integer
S(0) = 2
S(1) = "May 11"
B = S(0)
C = S(1)
Set RsResult = RPC.DoRPC("GetRecordsetWithParams", B, C)
B = StrConv(RsResult(0).Value, vbUnicode) 'get the resulting ByteArray-Blob from the ResultRs-Field 0
VisualizeRs RsResult
End Sub
In my modeQuery for index 2:
Code:
Public Function Query(QueryNo As String) As String
Dim n As Integer
n = CInt(QueryNo)
Select Case QueryNo
Case 1
Query = "SELECT dailyjournal.id, date, userno, entry, private FROM dailyjournal"
Case 2
Query = "SELECT dailyjournal.id, date, userno, entry, private FROM dailyjournal WHERE entry LIKE '%'+?+'%'"
End Select
End Function
1. I have used your GetRs routine in modADODB with and without parameters with some success in posts #70 to #72. However, your silence is deafening, so I presume I made a lot of mistakes there. If you can point me to the errors I will appreciate.
2. When doing the exercise in 1. above, your code in the form event: B = RsResult(0).Value gives me an variant type error. I presume it is because I sent it data in the wrong format or type, because when I change that to B = StrConv(RsResult(0).Value, vbUnicode) it works perfectly. I do not know how to solve this error.
3. In this whole process I never know when I must use bytes and when I may use strings. If you can please revisit my post #64 to point these out to me, I will appreciate. I do not know quite where SQL injection can and can not take place in this process.
4. In the same modADODB you have a batchupdate function which takes a recordset Rs as a parameter. Which parameters or fields should I pass to this function in the recordset?
I am publishing this variation on the song High Noon with the greatest respect:
Do not forsake me, oh, my mentor
On this, my ignoramus day
Do not forsake me, oh, my mentor
help, help along
I do not know what fate awaits me
I only know I must be brave
And I must face a man who knows all
Or lie a coward, a craven coward
Or lie a coward in my grave
Oh, to be torn 'tweenst ignorance and duty
Supposin' I lose my programs's beauty
Look at that data not moving along
Nearin' high noon
He made a promise whilst in AdStateOpen
Vowed he would help me overcome my problem
I'm not afraid of failure, but, oh
What shall I do if you forsake me?
PK
PS - Moderator, please do not ban me!!! I love this forum.
As far as I know Olaf didn't make a promise, including in a previous thread where he tried to help you. This forum is made of a community volunteering their time, to help others. One doesn't have the right to demand they put everything away, and work on their issue. Others could step in and out depending what time they have or willing to spend. There should be no expectations that the same person comes back and volunteer their time over and over. Would you stop everything that you do and help me with my problem?
I sometimes just post API functions or keywords to search and run. If a thread subject doesn't reflect the contents, then someone searching for a solution is unlikely to view such a thread, so any answers given to such a thread just benefit one specific person. The same goes for threads that say "I need help" in the subject, etc., I don't respond to these. Only one benefits rather than the community. I know derailing a thread happens from time to time, but usually briefly.
qvb6,
I hold you and Olaf and many others here in veneration.
However, I think that whilst we are mostly nerds we can enjoy some diversion.
Rest assured that I am here to support the forum.
PK
Private Sub cmdAddRow_Click()
Dim B() As Byte, B0() As Byte, B1() As Byte, B2() As Byte, B3() As Byte, B4() As Byte, B5() As Byte, B6() As Byte, B7() As Byte
Dim S(0 To 6) As String, RsResult As ADODB.Recordset, i As Integer, ByteLength As Integer
S(0) = "DailyJournal": S(1) = "MyDate": S(2) = Date: S(3) = "UserNo": S(4) = 1: S(5) = "Entry": S(6) = "May 11 entry"
For i = 0 To 6
If i = 0 Then B0 = S(i)
If i = 1 Then B1 = S(i)
If i = 2 Then B2 = S(i)
If i = 3 Then B3 = S(i)
If i = 4 Then B4 = S(i)
If i = 5 Then B5 = S(i)
If i = 6 Then B6 = S(i)
Next i
Set RsResult = RPC.DoRPC("AddNewRow", B0, B1, B2, B3, B4, B5, B6)
B = RsResult(0).Value 'get the resulting ByteArray-Blob from the ResultRs-Field 0
End Sub
Thjs works fine.
I have this in the Handler:
Code:
Public Sub AddNewRow()
Dim Cnn As ADODB.Connection, rsADO As ADODB.Recordset, SQL As String, i As Integer, Updated As Boolean, TableName As String, FieldNames(0 To 2) As String, Values(0 To 2) As Variant
Debug.Print "RsIn field values: ", RsIn(1), RsIn(2), RsIn(3), RsIn(4), RsIn(5), RsIn(6), RsIn(7)
TableName = RsIn(1)
FieldNames(0) = RsIn(2)
FieldNames(1) = RsIn(4)
FieldNames(2) = RsIn(6)
Values(0) = RsIn(3)
Values(1) = RsIn(5)
Values(2) = RsIn(7)
Set Cnn = OpenCnn(strConn)
If Cnn.State = adStateOpen Then
SQL = "SELECT * FROM " & TableName & " WHERE id=0"
Set rsADO = GetRs(Cnn, SQL)
If rsADO.State = adStateOpen Then
rsADO.AddNew
For i = 0 To rsADO.Fields.Count - 1
If rsADO.Fields(i).Name = CStr(FieldNames(0)) Then rsADO.Fields(i).Value = Values(0)
If rsADO.Fields(i).Name = CStr(FieldNames(1)) Then rsADO.Fields(i).Value = Values(1)
If rsADO.Fields(i).Name = CStr(FieldNames(2)) Then rsADO.Fields(i).Value = Values(2)
Next i
Updated = UpdateBatch(Cnn, rsADO)
Set RsOut = CreateResultRs("AddNewRow", "New row added")
Else
Set RsOut = CreateResultRs("AddNewRow", "New row not added")
End If
Else
Set RsOut = CreateResultRs("AddNewRow", "Connection not opened")
End If
Exit Sub
This works fine if I enter real values in the code lines like this"
Code:
If rsADO.Fields(i).Name = CStr(FieldNames(0)) Then rsADO.Fields(i).Value = "01-06-2019"
But it gives me an error when I do the code line like this:
Code:
If rsADO.Fields(i).Name = CStr(FieldNames(0)) Then rsADO.Fields(i).Value = Values(0)
And I know I have the correct values in FieldNames() and in Values().
Private Sub cmdDeleteRow_Click()
Dim B() As Byte, C() As Byte, S(0 To 1) As String, RsResult As ADODB.Recordset
S(0) = "DailyJournal": S(1) = "5"
B = S(0): C = S(1)
Set RsResult = RPC.DoRPC("DeleteRow", B, C)
B = StrConv(RsResult(0).Value, vbUnicode) 'get the resulting ByteArray-Blob from the ResultRs-Field 0
End Sub
Here is my Handler:
Code:
Public Sub DeleteRow()
Dim Cnn As ADODB.Connection, rsADO As ADODB.Recordset, SQL As String, i As Integer, Updated As Boolean, TableName As String, IDvalue As Variant
Debug.Print "RsIn field values: ", RsIn(1), RsIn(2)
TableName = RsIn(1)
IDvalue = RsIn(2)
Set Cnn = OpenCnn(strConn)
If Cnn.State = adStateOpen Then
SQL = "SELECT * FROM " & TableName & " WHERE id=" & IDvalue
Set rsADO = GetRs(Cnn, SQL)
If rsADO.State = adStateOpen Then
rsADO.Delete
Updated = UpdateBatch(Cnn, rsADO)
rsADO.Close
Set RsOut = CreateResultRs("DeleteRow", "Row deleted")
Else
Set RsOut = CreateResultRs("DeleteRow", "Row not deleted")
End If
Else
Set RsOut = CreateResultRs("DeleteRow", "Connection not opened")
End If
Exit Sub
Private Sub cmdUpdateRow_Click()
Dim B() As Byte, B0() As Byte, B1() As Byte, B2() As Byte, B3() As Byte, B4() As Byte, B5() As Byte, B6() As Byte, B7() As Byte
Dim S(0 To 7) As String, RsResult As ADODB.Recordset, i As Integer, ByteLength As Integer
S(0) = "DailyJournal": S(1) = 6: S(2) = "MyDate": S(3) = Date: S(4) = "UserNo": S(5) = 2: S(6) = "Entry": S(7) = "June 1 entry"
For i = 0 To 7
If i = 0 Then B0 = S(i)
If i = 1 Then B1 = S(i)
If i = 2 Then B2 = S(i)
If i = 3 Then B3 = S(i)
If i = 4 Then B4 = S(i)
If i = 5 Then B5 = S(i)
If i = 6 Then B6 = S(i)
If i = 7 Then B7 = S(i)
Next i
Set RsResult = RPC.DoRPC("UpdateRow", B0, B1, B2, B3, B4, B5, B6, B7)
B = RsResult(0).Value 'get the resulting ByteArray-Blob from the ResultRs-Field 0
End Sub
Here is my Handler:
Code:
Public Sub UpdateRow()
Dim Cnn As ADODB.Connection, rsADO As ADODB.Recordset, SQL As String, i As Integer, Updated As Boolean, TableName As String, MyID As Variant, FieldNames(0 To 2) As String, Values(0 To 2) As Variant
Debug.Print "RsIn field values: ", RsIn(1), RsIn(2), RsIn(3), RsIn(4), RsIn(5), RsIn(6), RsIn(7)
TableName = RsIn(1)
MyID = RsIn(2)
FieldNames(0) = RsIn(3)
FieldNames(1) = RsIn(5)
FieldNames(2) = RsIn(7)
Values(0) = RsIn(4)
Values(1) = RsIn(6)
Values(2) = RsIn(8)
Set Cnn = OpenCnn(strConn)
If Cnn.State = adStateOpen Then
SQL = "SELECT * FROM " & TableName & " WHERE id=" & MyID
Set rsADO = GetRs(Cnn, SQL)
If rsADO.State = adStateOpen Then
For i = 0 To rsADO.Fields.Count - 1
If rsADO.Fields(i).Name = CStr(FieldNames(0)) Then rsADO.Fields(i).Value = Values(0)
If rsADO.Fields(i).Name = CStr(FieldNames(1)) Then rsADO.Fields(i).Value = Values(1)
If rsADO.Fields(i).Name = CStr(FieldNames(2)) Then rsADO.Fields(i).Value = Values(2)
Next i
Updated = UpdateBatch(Cnn, rsADO)
Set RsOut = CreateResultRs("UpdateRow", "Row updated")
Else
Set RsOut = CreateResultRs("UpdateRow", "Row not added")
End If
Else
Set RsOut = CreateResultRs("UpdateRow", "Connection not opened")
End If
Exit Sub
End Sub
As for InsertRow, the fields update gives me an error.
Peekay, the "problem I have" in this discussion, is that you don't read properly - you do not listen -
or if you do - then you apparently don't understand even the simplest things with regards to programming.
Seriously - you've not even mastered the "transfer of a Byte-Array-Param" via RPC yet...
(which was a necessity, that should have been mastered about 40 postings earlier).
I've now decided, to give up on my attempt "to make you really understand" (what's going on) -
and instead stick to simple "code-handouts"...
(To me, it feels like handing an airplane over to somebody who so far has not demonstrated
any decent piloting-skills - and what's even worse is, that "he's convinced he does").
So, here is - what I've expected you to come up with, regarding the "transfer bytearray task":
In the clientside Form-Code:
Code:
Private Sub Command1_Click()
ReDim B(0 To 2) As Byte: B(0) = 0: B(1) = 1: B(2) = 2
Dim RsResult As ADODB.Recordset
Set RsResult = RPC.DoRPC("TransferByteArrays", B)
If NoErrorIn(RsResult) Then B = RsResult(0).Value: Debug.Print B(0), B(1), B(2)
End Sub
'just add this additional helper into the Form, to be able to test for returned RsResult-Errors in a single line in clientside Caller-Code
Private Function NoErrorIn(RsResult As ADODB.Recordset) As Boolean
If RsResult(0).Name <> "ErrNumber" Then NoErrorIn = True: Exit Function
MsgBox "Error " & RsResult(0).Value & ": " & RsResult(1).Value
End Function
In the serverside cHandler.cls:
Code:
Public Sub TransferByteArrays()
'read the ByteArray (as a Blob-Value) from the incoming Rs' first Field-Slot
Dim B() As Byte: B = RsIn(1).Value
Dim i As Long 'now increment all the values in B() - just to show that we've touched it at the serverside
For i = 0 To UBound(B)
B(i) = B(i) + 1
Next
'return the (changed) B-Array in RsOut
Set RsOut = CreateResultRs("OutBytesBlob", B)
End Sub
So the above is, what I tried to explain to you regarding Byte-Array passing (in about 30 postings).
Perhaps I should write it up one last time, regarding the "flow and the passing" of a single ByteArray-Param (according to the code above):
- It starts at the clientside in Command1_Click, where a single Param B (in Blue) was passed (behind the serverside ProcedureNameString)
- next stop is the serverside Handler-Code, where the clientside InParam B is read from the RsIn(1)-transport-slot
- next is the processing on the incoming B-bytes in the little For-Loop
- last ilne in the serverside Handler-Routine is "passing B back to the remote-caller" (as a single param again, behind the "ParamDescriptionString")
- finally the handling of the result in Command1_Click again, where after return from DoRPC the single ByteArray-Value sits in RsResult(0).Value
That's it again, how a complete roundtrip works with the given Helper-Modules and -Functions (here for the case of ByteArray-Param passing).
And of course you can mix String-Param-passing and ByteArray-Param passing - e.g. when you want to write functionality,
which is able to transfer e.g. an ImageFiles contents to the clientside (as a ByteArray - but using a String-Param - the FilePath as an Input):
Clientside Form-Code for ServerFile-Content-Downloads:
Code:
Private Sub Command3_Click()
Dim FileName As String: FileName = "MyServersideImage.jpg"
Dim RsResult As ADODB.Recordset
Set RsResult = RPC.DoRPC("DownloadFileContent", FileName)
If NoErrorIn(RsResult) Then
Dim B() As Byte: B() = RsResult(0).Value
WriteByteArrayContentToFile B, App.Path & "\MyClientsideImage.jpg"
End If
End Sub
Serverside Handler-Code for the above:
Code:
Public Sub DownloadFileContent()
'load the single InParam (which was passed as a String) into a local Variable
Dim FileName As String: FileName = RsIn(1).Value
'do internal processing with the passed param (here, loading a Files content as a ByteArray)
Dim B() As Byte: B = ReadByteArrayContentFromFile(App.Path & "\" & FileName)
'pass the read serverside ByteContent of the File back to the caller
Set RsOut = CreateResultRs("OutBytesBlob", B)
End Sub
Please at least try to really understand what I've just posted (perhaps implementing the FileTransfer-Function as well, because it's something useful to have),
before I'm posting my code-handouts which can cover CRUD-scenarios (which as said, can be covered completely with only two serverside routines: GetRs and UpdateBatch).