Using MySQL to save data

Help and Feedback Scripting Support
#1

So past 24 hours, I am trying somehow to save data in MySQL Database from Roblox I’ve managed to save a string, boolean, number etc but when I tried saving JSON it gave me an error on my website saying

SyntaxError: Unexpected token S in JSON at position 0
    at JSON.parse (<anonymous>)
    at IncomingMessage.<anonymous> (C:\Users\Luka\Desktop\Lua\index.js:27:21)
    at IncomingMessage.emit (events.js:203:15)
    at endReadableNT (_stream_readable.js:1129:12)
    at process._tickCallback (internal/process/next_tick.js:63:19)
PS C:\Users\Luka\Desktop\Lua>

Ohh and also I forgot to say I am sending an HTTP request with some details from Roblox to my local website and then that local website puts the information in the database

This is the current code I have

local MySQL = require(script.MySQL).new("http://localhost:8000/")
local Players = game:GetService("Players")

local DataLoaded = {}


Players.PlayerAdded:Connect(function(Player)
	
	local ls =Instance.new("Folder",Player)
	ls.Name = "leaderstats"
	
	Instance.new("NumberValue",ls).Name = "Money"
	
	local Success,Res = MySQL:SELECT([[id]],Player.UserId)
	
	if (Success and Res.Body) then
		local Success,Res = MySQL:JSONDecode(Res.Body)
		
		if (Success and Res[1]) then
			
			DataLoaded[Player.UserId] = true
			
			for i,v in next,Res[1] do
				if (type(v) == "table") then
					for _,e in next,v do
						ls[e.Name].Value = e.Value
					end
				end
			end
		end
	end
end)

function Data(folder)
	local DataTable = {}
	
	for _,v in next,folder:GetChildren() do
		local TempData = {
			Name = v.Name,
			Value = v.Value	
		}
		
		table.insert(DataTable,TempData)
	end
	
	return DataTable
end


Players.PlayerRemoving:Connect(function(Player)
	if (DataLoaded[Player.UserId]) then
		local Set = [[name="%s",id=%s,data=%s]]
		
		local Success,Res = MySQL:JSONEncode(Data(Player.leaderstats))
		
		if not (Success) then
			print(Res)
			return
		end
				
		Set = string.format(Set,Player.Name,Player.UserId,Res)
		
		local WHERE = [[name="%s"]]
		
		WHERE = string.format(WHERE,Player.Name)
		
		MySQL:UPDATE(Set,WHERE)
	else
		local Success,Res = MySQL:JSONEncode(Data(Player.leaderstats))
		
		if not (Success) then
			print(Res)
			return
		end
		
		local String = [["%s","%s","$s"]]
		
		String = string.format(String,Player.Name,Player.UserId,Res)
		
		local Success,Res = MySQL:INSERT([[`name`, `id`, `data`]],String)
		
		if not (Success) then
			print(Res)
		end
	end
end)

Website

var mysql = require('mysql');
var express = require("express")
var app = express()

var con = mysql.createConnection({
    host: "",
    user: "",
    password: "",
    database: ""
});

con.connect(function(err) {
  if (err) throw err;
  console.log("Connected!");
});

app.post("/",function(req,res) {

    var body = '';

    req.on('data', function (data) {
        body += data;
    });

    req.on('end', function () {

        body = JSON.parse(body)

        console.log(body.Q)

        con.query(body.Q, function (err, result) {
            if (err) throw err;
            res.end(JSON.stringify(result))
        });
    });
})

app.listen(8000)
3 Likes
#2

Did you set the value type to the column to be json or is it text?

Since you’re encoding the json, you should be able to have it set to text. Just make sure to decode it once you grab the data again.

#3

The issue is that I cannot encode it.

#4

So what exactly is the issue? You’ve vaguely described your problem, dropped an error message and left two entire chunks of code, each of which may not even be the problem code.

At the very least, after some time having to dissect this myself, I see that the error message is linked to parsing the incoming JSON on your server. This equates to you not sending a proper JSON formatted string to your server.

The thing is, no code from the MySQL library has been provided nor are there any debug attempts listed, so it’s hard to pinpoint a problem. Please be explicit in detailing your problem so you can be better helped.

#5

I fixed it but basically, the error was because that script couldn’t convert my table to JSON it was telling me.

Can't convert to JSON

#6

So your issue was sending an invalid set to be encoded, then. Exactly what were you sending that wasn’t formatted properly and what did you correct it with?

Make sure to mark your post as the solution as well so people don’t waste time trying to solve something that was already resolved.

#7

I was sending String with JSON.

		local Success,Res = MySQL:JSONEncode(Data(Player.leaderstats)) --converted it to JSON
		
		if not (Success) then
			print(Res)
			return
		end
				
		Set = string.format(Set,Player.Name,Player.UserId,Res)
		
		local WHERE = [[name="%s"]]
		
		WHERE = string.format(WHERE,Player.Name)
		
		MySQL:UPDATE(Set,WHERE)

image
image694×275 7.64 KB

#8

So the error occurs at this line. It seems like the value of that body variable starts with an “S”, which isn’t a valid json to parse.
Perhaps try adding console.log(body) before that line and see what is the value of the variable?

#9

If you’re unable to encode it, that means that you’re not passing a table. You could be passing a nil value. do print(Data(Player.leaderstats)) to see if it’s nil or not.

If it’s working correctly, it should print something like “table:0000000”

#10

This may not be the only cause of it

You can’t encode mixed arrays, example of one that shouldn’t work in json encoding, though Roblox apparently changed it so it strips data, this is definitely something new

local str = {
	[1] = "some data";
	[2] = "more data";
	["3"] = "oof";
	whereditgo = 123123;
	what = {
		1, 2, 3, oof = "broke"
	}
}

local output = game:GetService("HttpService"):JSONEncode(str)
-- You should notice how it strips parts of the data, though this should be erroring?

another thing to keep in mind, is keeping all indexes either string or number.
so keep it like one of the examples:

{ [1] = "data"; [2] = "more data"; [3] = "ya" }

{ ["1"] = "data"; ["2"] = "more data"; ["3"] = "ya" }

don’t mix like the first example, as it isn’t JSON encodable.

2 Likes