Kitz ADSL Broadband Information
adsl spacer  
Support this site
Home Broadband ISPs Tech Routers Wiki Forum
 
     
   Compare ISP   Rate your ISP
   Glossary   Glossary
 
Please login or register.

Login with username, password and session length
Advanced search  

News:

Pages: 1 [2]

Author Topic: how to capture a signature and insert into SQL database as IMAGE field type  (Read 12558 times)

chenks

  • Kitizen
  • ****
  • Posts: 1106

unless i've done something wrong that still doesn't work
it's still inserted the following data into the field

Code: [Select]
0x405369676E617475726544617461
this is what i've amended the code to

https://pastebin.com/J2g0Z2ui
Logged

johnson

  • Reg Member
  • ***
  • Posts: 838

Was a bit of a long shot, you could try messing with the length of the insert:
Code: [Select]
Set param = cmd.CreateParameter("@SignatureBlob", 205, 1, Len(signatureData), signatureData)
or
Set param = cmd.CreateParameter("@SignatureBlob", 205, 1, 30000, signatureData)

But really I guess you need the data as a Byte[], stream? 205 type is adLongVarBinary, googling got me:
https://stackoverflow.com/questions/63994536/how-to-convert-string-to-byte-array-in-classic-asp and https://stackoverflow.com/questions/14629104/save-base64-to-an-image-using-classic-asp

I cant try anything as I dont have the environment, so I probably cant be any help.
Logged

chenks

  • Kitizen
  • ****
  • Posts: 1106

hmm, doesn't seem to be a length issue, as what it's entering into the DB is already quite short.
seems it is the data type that is the issue, the script as it is just now is happily inserting what it's creating but it's not the format that we are expecting it to insert.

according to this
https://www.w3schools.com/asp/ado_datatypes.asp

adBinary is 128, but again i'm not sure if that's what it needs to be, as the db field type is just "image".

but then it says adLongVarBinary is the correct datatype for image in SQL.
Logged

johnson

  • Reg Member
  • ***
  • Posts: 838

Give this a go:
Code: [Select]
<%
' Check if the form is submitted
If Request.ServerVariables("REQUEST_METHOD") = "POST" Then

    ' Retrieve the signature data from the request
    Dim signatureData
    signatureData = Request.Form("signatureData")
    ' Split the base64 data from the "data:image/png;base64," part
    Dim base64String
    base64String = Mid(signatureData, InStr(signatureData, ",") + 1)

    ' From https://stackoverflow.com/questions/4920416/base64-image-decoder-for-asp-classic
    Set objXML = Server.CreateObject("MSXML2.DOMDocument")
    Set objDocElem = objXML.createElement("Base64Data")
    objDocElem.DataType = "bin.base64"
    objDocElem.text = base64String

    Dim conn
    Set conn = Server.CreateObject("ADODB.Connection")
    conn.Open "Driver={SQL Server}; Server=servername; Database=databasename; UID=username; PWD=password; Option=4"

    ' '@SignatureBlob' will only work when refering to a stored procedure I think, use ? and give the values in order (only 1 in this case).
    Dim sql
    'sql = "UPDATE call_events SET ce_technicians_signature_image = '@SignatureBlob' WHERE ce_Id = '56729908'"
    sql = "UPDATE call_events SET ce_technicians_signature_image = ? WHERE ce_Id = '56729908'"

    ' Create a parameterized command and set the blob value
    Dim cmd
    Set cmd = Server.CreateObject("ADODB.Command")
    cmd.ActiveConnection = conn
    cmd.CommandText = sql
 
    ' Create a parameter and assign the blob value
    Dim param
    ' No name required here as we used ? in the UPDATE statement
    Set param = cmd.CreateParameter( , 205, 1, LenB(objDocElem.NodeTypedValue), objDocElem.NodeTypedValue)
    'Set param = cmd.CreateParameter("@SignatureBlob", 205, 1, LenB(signatureBlob), signatureBlob)
    cmd.Parameters.Append param
    cmd.Execute
 
    ' Cleanup
    Set param = Nothing
    Set cmd = Nothing
    conn.Close
    Set conn = Nothing

    ' For testing can output the length of the image, or return it as a file to download
    'Response.Write LenB(objDocElem.NodeTypedValue)
    'Response.ContentType = "image/jpeg"
    'Response.AddHeader "Content-Disposition", "attachment; filename=abc.jpg"
    'Response.BinaryWrite objDocElem.NodeTypedValue

    Set objXML = Nothing
    Set objDocElem = Nothing

    Response.Write "Signature successfully saved."

End If
%>
 
<!DOCTYPE html>
 
<html>
 
<head>
  <title>Signature Capture Form</title>
  <script src="https://unpkg.com/signature_pad"></script>
  <script>
    // Initialize the signature pad
    var signaturePad;
 
    window.onload = function() {
      var canvas = document.getElementById('signatureCanvas');
      signaturePad = new SignaturePad(canvas, {backgroundColor: 'rgb(255, 255, 255)'});
    };
 
    // Function to handle form submission
    function handleSubmit(event) {
      event.preventDefault(); // Prevent form submission
 
      // Get the signature as a data URL
      var signatureDataUrl = signaturePad.toDataURL("image/jpeg");
 
      // Set the signature data as a hidden form field
      document.getElementById('signatureData').value = signatureDataUrl;
 
      // Submit the form
      document.getElementById('signatureForm').submit();
    }
  </script>
  <style>
    #signatureCanvas {
      border: 1px solid #000;
    }
  </style>
</head>
 
<body>
 
  <h1>Signature Capture Form</h1>
  <form id="signatureForm" method="post" action="">
    <label for="signatureCanvas">Please sign below:</label><br>
    <canvas id="signatureCanvas" width="400" height="200"></canvas><br>
    <input type="hidden" id="signatureData" name="signatureData" value="">
    <button type="submit" onclick="handleSubmit(event)">Submit</button>
  </form>
 
</body>
 
</html>

It works as far as I can test (Server 2016, SQL 2017).

Some info in the comments, but apart from finding this stack answer that gives a nice short way of converting base64 to binary, your UPDATE statement was the reason it was putting the same value in the database everytime, if we convert "@SignatureBlob" to hex some familiar characters:
Code: [Select]
root@5022ada6fc2a:~# echo '@SignatureBlob' | xxd -p
405369676e6174757265426c6f620a

@Parameters names seem to require a stored procedure as mentioned here, so we need to use "?" in this case. If you have a stored procedure with the signature blob as a named parameter it should work as before.
« Last Edit: June 04, 2023, 11:58:39 PM by johnson »
Logged
Pages: 1 [2]
 

anything