llHTTPRequest limitations.

[pangore]

I am currently scripting something that uses google sheets as a database for ease of editing the information that needs to change for the object I'm scripting.  The object is just a role-play HUD that allows for full characters to be made. The majority of the data is already being stored using a Experience with key pairs. Unfortunately the admins of this system cannot maintain the database through scripts and needed something more user friendly. Google Sheets seemed perfect for the solution however I am running into a problem with the body length limit in the system.   The information that they need to keep is much longer then the set limit.  I was shown another object by someone else that uses google sheets and pulls all the data from it which is clearly more data then the body limits. 

 

So my question is how to do I pull more information then the body limit for llHTTPRequests ?  I already have the HTTP_BODY_MAXLENGTH set to max while using mono to compile. I'm sure there has to be a way since this over object I was shown is doing it. Just cant seem to figure it out on my own. 

 

Thank you.  

[Quistess Alpha]

If you need this for anything more than a personal project, you really shouldn't be using Google sheets, and should probably learn how to operate an actual database, for example mySQL.

Read at your own peril:

 

Edited June 29, 2021 by Quistessa

[pangore]

I tried for years to learn how to make a php, sql database but failed time and time again so I'm not capable of that. I am already reading the data from google sheets fine and it works but the problem is in the body length limit. It seems that Linden has a hard cap setup within the llHTTPRequests  so that it will only pull the first 16Kb of data from the request. Im trying to find away around that.  

[Coffee Pancake]

16kb is a lot of data when your script process only has 64kb total for script and variables.

[Lucia Nightfire]

1 hour ago, pangore said:

I tried for years to learn how to make a php, sql database but failed time and time again so I'm not capable of that. I am already reading the data from google sheets fine and it works but the problem is in the body length limit. It seems that Linden has a hard cap setup within the llHTTPRequests  so that it will only pull the first 16Kb of data from the request. Im trying to find away around that.  

Good luck. I asked Oz Linden last year if we could have HTTP's Range header functionality added to llHTTPRequest() and he was worried it would increase HTTP traffic to third party sites.

Edited June 29, 2021 by Lucia Nightfire

[Coffee Pancake]

30 minutes ago, Lucia Nightfire said:

Good luck. I asked Oz Linden last year if we could have HTTP's Range header functionality added to llHTTPRequest() and he was worried it would increase HTTP traffic to third party sites.

Maybe time to make a fresh JIRA for this one.

SL getting "pages" of data 16kb at a time from 3rd party sites is pretty inconsequential in world with node.js

Edited June 29, 2021 by Coffee Pancake

[pangore]

I have seen a product in world that uses google sheets as its database and pulls in more than the max limit for the body.  Does anyone have any clues or guesses how they might have accomplished that? 

[Mollymews]

3 hours ago, pangore said:

I have seen a product in world that uses google sheets as its database and pulls in more than the max limit for the body.  Does anyone have any clues or guesses how they might have accomplished that? 

probably multiple http requests that buffer the data in the script: string buffer; buffer += data;

[Innula Zenovka]

On 6/28/2021 at 9:06 PM, pangore said:

I am currently scripting something that uses google sheets as a database for ease of editing the information that needs to change for the object I'm scripting.  The object is just a role-play HUD that allows for full characters to be made. The majority of the data is already being stored using a Experience with key pairs. Unfortunately the admins of this system cannot maintain the database through scripts and needed something more user friendly. Google Sheets seemed perfect for the solution however I am running into a problem with the body length limit in the system.   The information that they need to keep is much longer then the set limit.  I was shown another object by someone else that uses google sheets and pulls all the data from it which is clearly more data then the body limits. 

 

So my question is how to do I pull more information then the body limit for llHTTPRequests ?  I already have the HTTP_BODY_MAXLENGTH set to max while using mono to compile. I'm sure there has to be a way since this over object I was shown is doing it. Just cant seem to figure it out on my own. 

 

Thank you.  

This is in the nature of "are you sure it's plugged in?" but have you checked that you're publishing your Google Sheet as a .csv file and reading that URL?    I've been caught out a couple of times by forgetting to do that and using the url for the HTML sheet, which retrieves a whole lot or formatting information but no data.

[pangore]

I double checked and made sure that it was published under a csv. When I use what I have scripted it shows the first full 18 rows of data and then it cuts off. The sheet has 114 rows and 13 columns so tons of data.  The information it does pull it is just the data I need and no formatting information so that leads me to think I did set it to csv as well. It pulls so little information though that it makes me feel like something isn't going right. 

Its very simple script at the moment since I have been testing anything I can think of to pull more data but maybe someone will see something stupid I did. 

default 
{
    state_entry()
    {   httpkey=llHTTPRequest(URL, [HTTP_BODY_MAXLENGTH,16384] ,"");} 

    http_response(key id, integer status, list meta, string body)
     {
        DataList = llParseString2List(body, [",","\n"], [] );
        RowCount = llGetListLength(llParseStringKeepNulls(body, ["\n"], []));
        CellCount = llGetListLength(DataList);
        ColumnCount = CellCount/RowCount;

        llSay(0,"Total number of cells: "+(string)CellCount);
        llSay(0,"Total number of rows: "+(string)RowCount);
        llSay(0,"Total number of columns: "+(string)ColumnCount);

        string Tempstring = llDumpList2String(DataList, "~~"); 
        llSay(0,"list - "+ Tempstring); 
        DataList = [];
    }
}

 

[Qie Niangao]

I've never used Google Sheets from LSL so I may not understand; is this pulling in the entire spreadsheet in one great gulp? Would the API's .values.batchget method be an option, to kinda nibble-away at the data?

Edited June 30, 2021 by Qie Niangao
(replace with working link)

[pangore]

I'm going to guess that is something to do with the API for google?  I'm not sure how to even get started using that.

[Wulfie Reanimator]

You can't just request the entire sheet and expect to be able to receive or store it within LSL. It's just not possible, there's not enough memory to work with.

You have to request smaller parts of the sheet, process those, then request more.

5 minutes ago, pangore said:

I'm going to guess that is something to do with the API for google?  I'm not sure how to even get started using that.

Start here: https://developers.google.com/sheets/api/samples/reading#read_a_single_range

[pangore]

Ok, thank you. Had hoped for a lsl solution since I have no luck with other languages. Ill read and see what I can figure out. Hopefully it will be easier then im thinking. 

[Wulfie Reanimator]

24 minutes ago, pangore said:

Ok, thank you. Had hoped for a lsl solution since I have no luck with other languages. Ill read and see what I can figure out. Hopefully it will be easier then im thinking. 

All you need is to change the URL within your LSL script. The instructions for what you need to change are in the page I linked.

[Innula Zenovka]

17 hours ago, pangore said:

Ok, thank you. Had hoped for a lsl solution since I have no luck with other languages. Ill read and see what I can figure out. Hopefully it will be easier then im thinking. 

As Wulfie suggests, you can still do it all in LSL.   You just have to reference the range in the spreadsheet you want it to read, rather than the first however many it is.   If you look at the sample code in the link,  you should see what you need to change.

And if it's a long list of values, then you'll have to read it in multiple calls, stepping down the page so many rows at a time.

[pangore]

Yes thank you. i have been working through that since I saw the link. Currently attempting to solve a "code": 403    "message": "The caller does not have permission"    "status": "PERMISSION_DENIED problem. Set the API up, got my API key as well. just reading through trying to solve it. 

[Drakeo]

LSL

This is within the platform. So the environment is controlled in the platform. what is LSL.

Only runs in the platform. only output you get is what the server gives to the client. that may change per updates of the simulator and client.

 we are dealing with so much much more  than platform. W are dealing with abuse.

 

[Drakeo]

On 6/30/2021 at 2:37 PM, Innula Zenovka said:

As Wulfie suggests, you can still do it all in LSL.   You just have to reference the range in the spreadsheet you want it to read, rather than the first however many it is.   If you look at the sample code in the link,  you should see what you need to change.

And if it's a long list of values, then you'll have to read it in multiple calls, stepping down the page so many rows at a time.

Wulfi  should know what creates the output so your putting words in  wulfi mouth

Edited July 3, 2021 by Drakeo

[Drakeo]

On 6/28/2021 at 9:15 PM, Quistessa said:

If you need this for anything more than a personal project, you really shouldn't be using Google sheets, and should probably learn how to operate an actual database, for example mySQL.

Read at your own peril:

 

who would use that  MySQL.  only  windoze people  amazon  ebay etc etc all use. mariadb it is opensource drop in  for MYSQL

why is that big we can see the diff

how many times have these guys  paid ransom 0

[Quistess Alpha]

https://github.com/mysql/mysql-server

The "community" version is free and open-source. but that's totally tangential. Database software is all the same to the enthusiast layman.

[Profaitchikenz Haiku]

MariaDB is also free and opensource, if it makes a difference.

[bobsknief Orsini]

You are probably stuck in having to implement a database solution and use some PHP, how else are you going to implement the Google Sheets API authentication part. I am assuming you eventually want to store data to instead of just reading it and that is something totally different (and to my knowledge you still need a other script outside of SL to do the authentication part).

You are limited in the amount of data you get, so you want to get a specific set of data based on some parameters. If you have control over this, you can get your data in multiple steps and store this data in multiple scripts working around the script memory limit.

[pangore]

I am currently using LSL Experiences for the data storage part of the design. The google sheets was read only and was being used for ease of use by the admins of the sim I'm designing this for. They don't like notecards and scripting to make changes was to difficult for them. I was able to get past the authentication problems and now I'm just trying to learn how to exclude the # symbol from the incoming body of the request. Everything else is working perfectly fine. I learned how to pull parts of the data from Google although i have to say the amount of data I can pull from sheets is remarkable low needing for multiple pulls. 

[Quistess Alpha]

2 hours ago, pangore said:

how to exclude the # symbol from the incoming body of the request.

strReplace?

// http://wiki.secondlife.com/wiki/Combined_Library
string strReplace(string str, string search, string replace) {
    return llDumpList2String(llParseStringKeepNulls((str = "") + str, [search], []), replace);
}