I'm using the SQL Genie "Query a SQL Database to get JSON data" in a UX component.
The result is this
What would I add to the SQL generated to extract the results? (ignore the arguments. They're irrelevant here.) The SQL is returning 4 records. I want to extract the "FooterText" and insert it into a text field on the UX component.
I know how to set it if I use the arguments to filter the results, but I don't want to keep returning to the database for each record. I want to get all 4 at the same time and then set the fields in the UX.
Code:
'DIM a SQL arguments object
dim args as sql::arguments
args.add("footer1",convert_type("1","Numeric"))
args.add("footer2",convert_type("2","Numeric"))
args.add("footer3",convert_type("3","Numeric"))
args.add("footer4",convert_type("4","Numeric"))
'DIM an Xbasic AlphaDAO connection object
dim cn as sql::connection
dim flag as l
''open the connection object to connect to the Database
flag = cn.open("::Name::SQL")
dim sql as c
sql = "SELECT Footer, FooterText, FooterHeader FROM Footer_Data"
'turn portable SQL on. (for more information on portable sql search in help (https://www.alphasoftware.com/documentation)
'execute the SQL - pass in the sql::arguments object
flag = cn.execute(sql,args)
if flag = .f. then
'an error occurred - get the error message from cn.callresult.text
'report the error, close the database and then end the script
dim errortext as c
errortext = cn.callresult.text
cn.close()
end if
'DIM a SQL::Resultset object
dim rs as sql::resultset
rs = cn.resultset
'get the JSON from the query
dim json as c
json = rs.toJSON()
'Clean up by freeing the resultset and then closing the database
cn.freeresult()
cn.close()
The result is this
Code:
[
{
"Footer": "1",
"FooterText": "<img src=\"recycleonly.png\"> <font size=\"4\"><b>ABOUT US</b></font><br><div><br></div><div>Fosters is a huge thrift store where all profits benefit foster children. 24,000 sq/ft of clothing, toys, household decor, electronics, new mattresses, furniture, shoes, purses, holiday decor, and so much more! Shop where it matters.</div><div><br></div><div><a href=\"/\"><font color=\"#ffa200\" size=\"3\">Home</font></a></div><div><a href=\"productlist.a5w\"><font color=\"#ffa200\" size=\"3\">Products</font></a></div><div><a href=\"cart.a5w\"><font color=\"#ffa200\" size=\"3\">Cart</font></a></div><div><a href=\"contactus.a5w\"><font color=\"#ffa200\" size=\"3\">Contact Us</font></a></div><div><a href=\"aboutus.a5w\"><font color=\"#ffa200\" size=\"3\">About Us</font></a></div><div><a href=\"news.a5w\"><font color=\"#ffa200\" size=\"3\">News</font></a></div><div><a href=\"donate.a5w\"><font color=\"#ffa200\" size=\"3\">Donate</font></a></div><div><a href=\"fosterfaq.a5w\"><font color=\"#ffa200\" size=\"3\">Foster Parent FAQ's</font></a></div><div><a href=\"rewards.a5w\"><font color=\"#ffa200\" size=\"3\">Fosters Rewards</font></a></div>",
"FooterHeader": "<img src=\"recycleonly.png\"> ABOUT US"
},
{
"Footer": "2",
"FooterText": "<img src=\"recycleonly.png\"> <font size=\"4\"><b>FIND US</b></font><br><div><br></div><div>Fosters reStore<br>2000 Lathrop Ave<br>Racine, WI 53405<br>(262) 333-8414<br><a href=\"mailto:[email protected]\"><font color=\"#ffa200\">[email protected]</font></a></div><div><br></div><div><a href=\"privacypolicy.a5w\"><font color=\"#ffa200\">Privacy Policy</font></a><br><a href=\"termsandconditions.a5w\"><font color=\"#ffa200\">Terms And Conditions</font></a></div>",
"FooterHeader": "Find Us"
},
{
"Footer": "3",
"FooterText": "<img src=\"recycleonly.png\"> <font size=\"4\"><b>STORE HOURS</b></font><br><div><br></div><div>Sunday - 11am to 5pm<br> Monday - 10am to 6pm<br>Tuesday - 10am to 6pm<br> Wednesday - 10am to 6pm<br>Thursday - 10am to 6pm<br>Friday - 10am to 6pm<br>Saturday - 10am to 6pm</div>\r\n<br><br>\r\n<img src=\"recycleonly.png\"> <font size=\"4\"><b>DONATION DROP-OFF HOURS</b></font><br><div><br></div><div>Sunday - 11am to 3pm<br>Monday - 10am to 4pm<br>Tuesday - 10am to 4pm<br>Wednesday - 10am to 4pm<br>Thursday - 10am to 4pm<br>Friday - 10am to 4pm<br>Saturday - 10am to 4pm<br><em><strong><font color=\"#ffa000\">All donation days are subject to weather. Rain cancels dropoff days.</font></strong></em></div>",
"FooterHeader": "Store Hours"
},
{
"Footer": "4",
"FooterText": "<img src=\"recycleonly.png\"> <font size=\"4\"><b>COVID-19 INFO</b></font><br><div><br></div><div>Our store is currently open 7 days a week. Please adhere to all social distancing and masking guidelines posted at the entrance.</div><div><br></div><div><br></div>\r\n\r\n<a style=\"background: #41a2d8 url(https://d1iczxrky3cnb2.cloudfront.net/red_logo.png) no-repeat 56px;color: #fff;text-decoration: none;font-family: Verdana,sans-serif;display: inline-block;font-size: 16px;padding: 17px 60px;padding-left: 94px;-webkit-border-radius: 2px;-moz-border-radius: 2px;border-radius: 2px;box-shadow: 0 2px 0 0 #1f5a89;text-shadow: 0 1px rgba(0, 0, 0, 0.3);\" href=\"https://donorbox.org/new-year-operating-budget?default_interval=o\">Donate</a><div><br><div><br></div><div>Foster Parents, please visit the <a href=\"fosterparentfaq.a5w\"><font color=\"#ffa200\">Foster Parent FAQ</font></a> page for instructions on how to shop during the COVID era.</div></div>",
"FooterHeader": "COVID-19 INFO"
}
]
I know how to set it if I use the arguments to filter the results, but I don't want to keep returning to the database for each record. I want to get all 4 at the same time and then set the fields in the UX.
Comment