0

I have a stored array of objects within my reactjs application which when console logged looks like this.

0: {answer: "yes↵", question: "Is the keyboard separate from the screen?", state: "Declined with solution defined"}
1: {answer: "yes", question: "Does the keyboard tilt?", state: "Accepted"}
2: {answer: "Question declined But not a problem", question: "Is it possible to find a comfortable typing postion?", state: "Accepted"}
3: {answer: "yes", question: "Do you have a good keyboard technique?", state: "Accepted"}
4: {answer: "Question declined But not a problem", question: "Are the characters on the keyboard clear and readable?", state: "Accepted"}
5: {answer: "sgdfgdfgdf", question: "Is your mouse or other pointing device suitable to the task you're using it for?", state: "problem specified"}
6: {answer: "yes", question: "Is the mouse (or other pointing device) located sufficently close to you?  ", state: "Accepted"}
7: {answer: "sdfsdfsdfsd", question: "Is there support for your wrist and forearm when using the mouse(or other pointing device)", state: "Declined with solution defined"}
8: {answer: "yes", question: "Does the mouse (or other pointing device) work smoothly at a speed that suits you?", state: "Accepted"}
9: {answer: "asdasdas", question: "Can you easily adjust the software settings for speed and accuracy of the pointer?", state: "Declined with solution defined"}
10: {answer: "asdasdads", question: "Are the characters on your screen clear and readable?", state: "problem specified"}
11: {answer: "yes", question: "Is the text size on your screen confortable to read?", state: "Accepted"}
12: {answer: "asdasdasd", question: "Is the image on your screen free from flicker and jitter?", state: "Declined with solution defined"}
13: {answer: "asdasdasd", question: "Is your screen's specification suitable for its intended use?", state: "problem specified"}
14: {answer: "yes", question: "Is the brightness and/or contrast on your screen adjustable?", state: "Accepted"}

this consists of answer, question and state (state of the answered question not state as in react components)

What I would like to do is pass these through to express so that I can upload them into my SQL database using the npm package mssql. However as these are all stored into the array I'm not sure how to split these up.

Ideally I would love to pass the whole object through to SQL and just store the whole thing in the database e.g (pseudo code)

insert into table where answer = answer and question = question and state = state

Essentially to use this with SQL how would I break these down to be used with my back end or can I pass the whole object with a specific SQL stored procedure.

create procedure StoreAnswers

@{answer_values}
as

INSERT INTO QuestionResponses                                         
(RUId, QuestionId, Date, QuestionWhenAnswered, QuestionResponse, Accepted, AssignedWorkStation )
VALUES
${answer_values}

EDIT

Stored procedure

create procedure StoreAnswers(@Results varchar(max))
as begin
  insert into QuestionResponses(QuestionResponse, QuestionWhenAnswered, State)
         select substring(value, charindex('{answer: "', value) + 10,  charindex('", ', value, charindex('{answer: "', value) + 10) -  charindex('{answer: "', value) - 10) as answer, 
                substring(value, charindex('question: "', value) + 11, charindex('", ', value, charindex('question: "', value) + 11) - charindex('question: "', value) - 11) as question,
                substring(value, charindex('state: "', value) + 8,      charindex('"}', value,  charindex('state: "', value) + 8) -     charindex('state: "', value) - 8) as state
         from string_split(@Results, char(10))    
end;

How it is passed through to the sql query from express npm

app.post("/post-question-answers", async (req, res) => {
  console.log("!called");

  let results = req.body.results;

  console.info(results);

  await sql.connect(config, function(err) {
    if (err) console.log(err);

    // create Request object
    var request = new sql.Request();
    request.input("Results", sql.VarChar, results);
    // query to the database and get the records
    request.execute("dbo.StoreAnswers", function(err, recordset) {
      if (err) console.log(err);
      // send records as a response
      res.json(recordset);
    });
  });

  res.statusCode = 400;
  res.statusMessage = "bad request";
  // res.json({ message: "Email Missing" });
});

This is the console.info(results)

[
[0]   {
[0]     answer: 'yes',
[0]     question: 'Is the keyboard separate from the screen?',
[0]     state: 'Accepted'
[0]   },
[0]   {
[0]     answer: 'yes',
[0]     question: 'Does the keyboard tilt?',
[0]     state: 'Accepted'
[0]   },
[0]   {
[0]     answer: 'yes',
[0]     question: 'Is it possible to find a comfortable typing postion?',
[0]     state: 'Accepted'
[0]   },
[0]   {
[0]     answer: 'yes',
[0]     question: 'Do you have a good keyboard technique?',
[0]     state: 'Accepted'
[0]   },
[0]   {
[0]     answer: 'yes',
[0]     question: 'Are the characters on the keyboard clear and readable?',
[0]     state: 'Accepted'
[0]   },
[0]   {
[0]     answer: 'yes',
[0]     question: "Is your mouse or other pointing device suitable to the task you're using it for?",
[0]     state: 'Accepted'
[0]   },
[0]   {
[0]     answer: 'yes',
[0]     question: 'Is the mouse (or other pointing device) located sufficently close to you?  ',
[0]     state: 'Accepted'
[0]   },
[0]   {
[0]     answer: 'yes',
[0]     question: 'Is there support for your wrist and forearm when using the mouse(or other pointing device)',
[0]     state: 'Accepted'
[0]   },
[0]   {
[0]     answer: 'yes',
[0]     question: 'Does the mouse (or other pointing device) work smoothly at a speed that suits you?',
[0]     state: 'Accepted'
[0]   },
[0]   {
[0]     answer: 'yes',
[0]     question: 'Can you easily adjust the software settings for speed and accuracy of the pointer?',
[0]     state: 'Accepted'
[0]   },
[0]   {
[0]     answer: 'yes',
[0]     question: 'Are the characters on your screen clear and readable?',
[0]     state: 'Accepted'
[0]   },
[0]   {
[0]     answer: 'yes',
[0]     question: 'Is the text size on your screen confortable to read?',
[0]     state: 'Accepted'
[0]   },
[0]   {
[0]     answer: 'yes',
[0]     question: 'Is the image on your screen free from flicker and jitter?',
[0]     state: 'Accepted'
[0]   },
[0]   {
[0]     answer: 'yes',
[0]     question: "Is your screen's specification suitable for its intended use?",
[0]     state: 'Accepted'
[0]   },
[0]   {
[0]     answer: 'yes',
[0]     question: 'Is the brightness and/or contrast on your screen adjustable?',
[0]     state: 'Accepted'
[0]   }
[0] ]

error

RequestError: Invalid length parameter passed to the LEFT or SUBSTRING function.
Improve this question
2
  • Version of SQL Server? Commented Feb 18, 2020 at 9:12
  • newest version I am fairly sure Commented Feb 18, 2020 at 9:14

5 Answers 5

Reset to default
1

This solution converts the source lines into json strings (your log lines already are very similar to a json, so only minor changes are needed). This way you can use standard SQL Server functions to read their values, making the code cleaner, easier to maintain, and hopefully more robust than my other proposal.

The lines are still separated using the string_split function with the line feed character.

create procedure StoreAnswers(@text varchar(max))
as begin
  insert into QuestionResponses(question, answer, state)
  select json_value(json, '$.question') as question,
         json_value(json, '$.answer') as answer,
         json_value(json, '$.state') as state
  from (select replace(replace(replace(substring(value, charindex('{', value), len(value)), 'answer: ', '"answer": '), 'question: ', '"question": '), 'state: ', '"state": ') as json
        from string_split(@source, char(10))
        where value like '%{%}%') as jsons
end;

json_value is the SQL function that extracts a value from a json string.

Example of the conversion from a source line to a json. From :

0: {answer: "yes", question: "Is the keyboard separate from the screen?", state: "Declined with solution defined"}

... we convert it to :

{"answer": "yes", "question": "Is the keyboard separate from the screen?", "state": "Declined with solution defined"}

We just remove the initial line number, and we add quotes to the identifiers. This expression does those changes :

replace(replace(replace(substring(value, charindex('{', value), len(value)), 'answer: ', '"answer": '), 'question: ', '"question": '), 'state: ', '"state": ')

Here you can see live the extraction of each value from the source text : http://sqlfiddle.com/#!18/9eecb/74419

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

I can't tell much about how to write it in express, but what you need is an SQL query like

INSERT INTO `table_name`                                           
(answer, question, created_at, updated_at)
VALUES
${sql_insert_values}

sql_insert_values will be an array of strings with comma-separated value. Each string will represent one field and will have comma-separated values for each column.

Do add ON DUPLICATE KEY UPDATE value = VALUES(value) if you have some unique key or index on some field.

Feel free to ask if you have doubts.

PS: Values sent to SQL will be in format:

["(question value in string, answer value in string, date-time, date-time)", "()", "()"]. Here's a simpler example of the query: 

INSERT INTO tbl_name (a,b,c) 
VALUES(1,2,3), (4,5,6), (7,8,9);

Refer this for more: https://dev.mysql.com/doc/refman/8.0/en/insert.html

Improve this answer

5 Comments

Nice so how would this be done within a stored procedure. What would the object variable datatype be ?
You will need to reformat your data a little. ${sql_insert_values} will be an array. here's an example of it ["('This is my question', 'This is my answer', Time.now, Time.now") , "('This is my second question', 'This is my second answer', Time.now, Time.now)" ]
Hey I have update the question wpuld that stored procedure work ?
Sorry henry I don't have much knowledge about stored procedures. I can only help you with the query.
@henrypf You can upvote my answer if you get help from the query.
1

You can use string_split with the line feed character to separate your text into lines, and then use substring to separate your values within those lines, using charindex to identify their positions.

declare @text varchar(max) = 
'0: {answer: "yes↵", question: "Is the keyboard separate from the screen?", state: "Declined with solution defined"}
1: {answer: "yes", question: "Does the keyboard tilt?", state: "Accepted"}
2: {answer: "Question declined But not a problem", question: "Is it possible to find a comfortable typing postion?", state: "Accepted"}
3: {answer: "yes", question: "Do you have a good keyboard technique?", state: "Accepted"}
4: {answer: "Question declined But not a problem", question: "Are the characters on the keyboard clear and readable?", state: "Accepted"}
5: {answer: "sgdfgdfgdf", question: "Is your mouse or other pointing device suitable to the task you''re using it for?", state: "problem specified"}
6: {answer: "yes", question: "Is the mouse (or other pointing device) located sufficently close to you?  ", state: "Accepted"}
7: {answer: "sdfsdfsdfsd", question: "Is there support for your wrist and forearm when using the mouse(or other pointing device)", state: "Declined with solution defined"}
8: {answer: "yes", question: "Does the mouse (or other pointing device) work smoothly at a speed that suits you?", state: "Accepted"}
9: {answer: "asdasdas", question: "Can you easily adjust the software settings for speed and accuracy of the pointer?", state: "Declined with solution defined"}
10: {answer: "asdasdads", question: "Are the characters on your screen clear and readable?", state: "problem specified"}
11: {answer: "yes", question: "Is the text size on your screen confortable to read?", state: "Accepted"}
12: {answer: "asdasdasd", question: "Is the image on your screen free from flicker and jitter?", state: "Declined with solution defined"}
13: {answer: "asdasdasd", question: "Is your screen''s specification suitable for its intended use?", state: "problem specified"}
14: {answer: "yes", question: "Is the brightness and/or contrast on your screen adjustable?", state: "Accepted"}'

select substring(value, charindex('{answer: "', value) + 10, charindex('", ', value, charindex('{answer: "', value) + 10) - charindex('{answer: "', value) - 10) as answer, 
       substring(value, charindex('question: "', value) + 11, charindex('", ', value, charindex('question: "', value) + 11) - charindex('question: "', value) - 11) as question,
       substring(value, charindex('state: "', value) + 8, charindex('"}', value, charindex('state: "', value) + 8) - charindex('state: "', value) - 8) as state
from string_split(@text, char(10))

You can see here that extraction working : http://sqlfiddle.com/#!18/9eecb/74353

So the stored procedure that accepts your text and inserts it into your table will be :

create procedure StoreAnswers(@text varchar(max))
as begin
  insert into QuestionResponses(answer, question, state)
         select substring(value, charindex('{answer: "', value) + 10,  charindex('", ', value, charindex('{answer: "', value) + 10) -  charindex('{answer: "', value) - 10) as answer, 
                substring(value, charindex('question: "', value) + 11, charindex('", ', value, charindex('question: "', value) + 11) - charindex('question: "', value) - 11) as question,
                substring(value, charindex('state: "', value) + 8,      charindex('"}', value,  charindex('state: "', value) + 8) -     charindex('state: "', value) - 8) as state
         from string_split(@text, char(10))    
end;
Improve this answer

3 Comments

The questions, answers and state will change lenght dramitcally on occasion will this affect this ?
Ahh okay I think if modified this may be the answer. At the moment it is throwing a error message of ` RequestError: Invalid length parameter passed to the LEFT or SUBSTRING function.`
Fully update the question please let me know if you have any idea why this is happening
0

I think you have to create an additional table t_servey(id, name, ...) and use servey_id in t_question table for the future.

t_question(
id,
servey_id, -- link on t_servey table
answer,
question,
created_at,
updated_at
)

And add bining variables (?) for insert into table:

insert into t_question (servey_id, answer, question, created_at, updated_at) values (?, ?, ?, ?, ?)
Improve this answer

Comments

0

why you dont use an orm with your express app

like sequelize it will make your life easier and remove all the headache of building sql statement in your app

check the documentation for it https://sequelize.org/

with sequlize in your case

1- create your table in the db

2- make a model in your express app for that table

3- call model.bulkCreate(the whole object)

give it a try

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.