Incomplete processing?

Databases
1

Not sure how to describe this… but… I have an investment analysis program that retrieves data from a website using api calls. These data points are then sent into a mysql database.

Some of the data is returned in quotes ("") that I need to remove with a replaceall statement.

This works most of the time, unless I am processing a large number of tickers, then it seems to skip the replaceall statement before executing the MySQL save statement.

For example, one data point is SharesOut, it should save as 56. When I only run one company, it saves as 56, but when I try to update 100 companies it saves at “56.00.”

How do I get xojo to not send the data to mysql until it has been cleaned up?

2

Can you post the code you are using?

3

Do you use:
String.ReplaceAll("""") // 4 double quotes
Or:
String.ReplaceAll(chr(34)) // ascii code for double quote

It can make a difference depending on the encoding used…

Ooh can you post what encoding the db uses?

1 Like
4

It is mariadb, on linux (that particular machine is 32 bit a the moment). Default storage engine, InnoDB, default-character-set = utf8mb4

Code is (my key has been replaced with some xxxxxxxxx’s):

Dim ExDividendDate, outstandingShares0Date, outstandingShares0, outstandingShares1Date, outstandingShares1, outstandingShares2Date, outstandingShares2, outstandingShares3Date, outstandingShares3, outstandingShares4Date, outstandingShares4, outstandingShares5Date, outstandingShares5 AS STRING = “0”

ExDividendDate=socket1.Get(“https://eodhistoricaldata.com/api/fundamentals/” + trim(Globals.DataTick) + Ext + “?api_token=xxxxxxxxxx&filter=SplitsDividends::ExDividendDate”,0)

outstandingShares0Date=socket1.Get(“https://eodhistoricaldata.com/api/fundamentals/” + trim(Globals.DataTick) + Ext + “?api_token=xxxxxxxxxxx&filter=outstandingShares::annual::0::date”,0)
outstandingShares0=socket1.Get(“https://eodhistoricaldata.com/api/fundamentals/” + trim(Globals.DataTick) + Ext + “?api_token=xxxxxxxxxxxxxxx&filter=outstandingShares::annual::0::sharesMln”,0)
outstandingShares1Date=socket1.Get(“https://eodhistoricaldata.com/api/fundamentals/” + trim(Globals.DataTick) + Ext + “?api_token=xxxxxxxxxxxxxxxx&filter=outstandingShares::annual::1::date”,0)
outstandingShares1=socket1.Get(“https://eodhistoricaldata.com/api/fundamentals/” + trim(Globals.DataTick) + Ext + “?api_token=xxxxxxxxxxxxxxxxxxx&filter=outstandingShares::annual::1::sharesMln”,0)
outstandingShares2Date=socket1.Get(“https://eodhistoricaldata.com/api/fundamentals/” + trim(Globals.DataTick) + Ext + “?api_token=xxxxxxxxxxxxxxxxxx&filter=outstandingShares::annual::2::date”,0)
outstandingShares2=socket1.Get(“https://eodhistoricaldata.com/api/fundamentals/” + trim(Globals.DataTick) + Ext + “?api_token=xxxxxxxxxxxxxxxxxxxxxx&filter=outstandingShares::annual::2::sharesMln”,0)
outstandingShares3Date=socket1.Get(“https://eodhistoricaldata.com/api/fundamentals/” + trim(Globals.DataTick) + Ext + “?api_token=xxxxxxxxxxxxxxxxxx&filter=outstandingShares::annual::3::date”,0)
outstandingShares3=socket1.Get(“https://eodhistoricaldata.com/api/fundamentals/” + trim(Globals.DataTick) + Ext + “?api_token=xxxxxxxxxxxxxxxxxxxxx&filter=outstandingShares::annual::3::sharesMln”,0)
outstandingShares4Date=socket1.Get(“https://eodhistoricaldata.com/api/fundamentals/” + trim(Globals.DataTick) + Ext + “?api_token=xxxxxxxxxxxxxxxxxxxxx&filter=outstandingShares::annual::4::date”,0)
outstandingShares4=socket1.Get(“https://eodhistoricaldata.com/api/fundamentals/” + trim(Globals.DataTick) + Ext + “?api_token=xxxxxxxxxxxxxxxxxxxx&filter=outstandingShares::annual::4::sharesMln”,0)
outstandingShares5Date=socket1.Get(“https://eodhistoricaldata.com/api/fundamentals/” + trim(Globals.DataTick) + Ext + “?api_token=xxxxxxxxxxxxxxxxxxxx&filter=outstandingShares::annual::5::date”,0)
outstandingShares5=socket1.Get(“https://eodhistoricaldata.com/api/fundamentals/” + trim(Globals.DataTick) + Ext + “?api_token=xxxxxxxxxxxxxxxxxx&filter=outstandingShares::annual::5::sharesMln”,0)

ExDividendDate=ReplaceAll(ExDividendDate,Chr(34),"")
outstandingShares0Date = ReplaceAll(outstandingShares0Date,chr(34),"")
outstandingShares0=ReplaceAll(outstandingShares0,chr(34),"")
outstandingShares1Date = ReplaceAll(outstandingShares1Date,chr(34),"")
outstandingShares1=ReplaceAll(outstandingShares1,chr(34),"")
outstandingShares2Date = ReplaceAll(outstandingShares2Date,chr(34),"")
outstandingShares2=ReplaceAll(outstandingShares2,chr(34),"")
outstandingShares3Date = ReplaceAll(outstandingShares3Date,chr(34),"")
outstandingShares3=ReplaceAll(outstandingShares3,chr(34),"")
outstandingShares4Date = ReplaceAll(outstandingShares4Date,chr(34),"")
outstandingShares4=ReplaceAll(outstandingShares4,chr(34),"")
outstandingShares5Date = ReplaceAll(outstandingShares5Date,chr(34),"")
outstandingShares5=ReplaceAll(outstandingShares5,chr(34),"")

if ExDividendDate = “null” then ExDividendDate = “01/01/1900”
if outstandingShares0= “null” then outstandingShares0 = “0”
if outstandingShares1= “null” then outstandingShares1 = “0”
if outstandingShares2= “null” then outstandingShares2 = “0”
if outstandingShares3= “null” then outstandingShares3 = “0”
if outstandingShares4= “null” then outstandingShares4 = “0”
if outstandingShares4= “null” then outstandingShares4 = “0”

strSql=(“Update " + tDataTick + “_data SET ExDividendDate = '” + ExDividendDate + “’, outstandingShares0Date = '” + outstandingShares0Date + “’, SharesOut0 = '” + outstandingShares0 + “’, outstandingShares1Date=’” + outstandingShares1Date + “’, SharesOut1 = '” + outstandingShares1 + “’, outstandingShares2Date = '” + outstandingShares2Date + “’, SharesOut2 = '” + outstandingShares2 + “’ , outstandingShares3Date = '” + outstandingShares3Date + “’ , SharesOut3 = '” + outstandingShares3 + “’ , outstandingShares4Date = '” + outstandingShares4Date + “’ , SharesOut4 = '” + outstandingShares4 + “’ , outstandingShares5Date = '” + outstandingShares5Date + “’ , SharesOut5 = '” + outstandingShares5 +”’ ;" )
'MSGBOX strSql
buylist.SQLExecute(strSql)
'stream.WriteLine(strSql)
if buylist.ErrorMessage<> “” then
Globals.errormsg = Globals.errormsg + buylist.errormessage
MsgBox Globals.errormsg
'stream.WriteLine(Globals.errormsg)
'stream.WriteLine(strSql)
End If

5

This code is all sequential, so the problem isn’t what you think it is.

One possibility: since sequential Get calls seem to use DoEvents to do its magic, you might be seeing side-effects from Timers and the like that start processing before this method is finished.

You might consider rewriting this to use the socket asynchronously.

6

Wy don’t you encode/decode the url parameters?

7

Also, consider using a prepared statement. While you can likely trust whatever is returned from the server, you’re opening yourself up to sql injection here.

1 Like
8

The mysql server is here at my office with me… the router blocks the port form the internet.

9

When it fails, is the quote character actually an Chr(34)? Or is it a different character being returned?

10

But you are getting the data from external service that you don’t control.

11

It seems to be the Chr(34). As I said, it works if I only do a few companies at a time… it is when I have to do a large amount that I have issues.

12

Yes, the raw data is from an outside source… using the api calls. I then convert it to what I need and then store it in the mysql database.

13

From what you have posted, there is no way to get from the api call to the database update without going through the ReplaceAll code. There’s got to be something else going on. Is this the only place you’re updating the database?

14

“there is no way to get from the api call to the database update without going through the ReplaceAll code”

That is what I thought as well… hence the problem.

This is part of a subroutine that is called to update the data in the database. There are more sections before and after that update, but they are in the same routine, either before or after this section of code. All of it is sequential.

15

Have you tried storing all the returned raw data and checking over it with a hex editor to ensure that on a rare occasion the skipped quotes in question are actually coming back as ascii 34 and not something that just looks like it but is a different value?

1 Like
16

You should check the encoding that the api response is returning, it could be other than utf-8

17

I tried to save each line to a file, then reviewed that. I see them as " only… so, I don’t think that is the issue.

I am beginning to think I should just save all the data, even with the " marks, then write a routine to clean the database. But that just seems like a lot of extra steps.

18

I’m curious (and I didn’t see it asked above)… do you need to remove the quotes because you don’t want the data stored with them or are you removing them because they cause problems in your MySQL queries?

19

I changed my field type to varchar for all of the fields, so that they will save the data even if they are quoted. I need to remove the quotes so that my program can do further calculations with the data.