xbasic - insert into sql table

[carlbsmith]

I have a server-side event 'beforesearch' that basically every time a search is made in a grid, I have data that is captured and collected into a table called 'user_tracking'. This has been working fine as a .dbf. But I am converting everything over to sql. I went through all of the xbasic help videos, and created what I thought would be the correct statement to write to this table, but it is not working. Wondering if someone can take a look at this and tell me where my error(s) are:


function BeforeSearch as v (SearchDataSubmitted as P, Args as p, PageVariables as p)
with PageVariables
end with

'Declare session variables
DIM session.UserID as c
DIM session.Account_Number as c
DIM session.Sales_Rep as c
DIM session.Vendor as c
DIM session.Part as c
DIM session.Int_Ext as c

'Get currrent user id
session.UserID = a5ws_getcurrentuser()

'Check for specific vendor search
session.Vendor = if(SearchDataSubmitted.Manufacturer = null_value(),"None",SearchDataSubmitted.Manufacturer)

'Check for specific part number search
session.Part = if(SearchDataSubmitted.Part_number = null_value(),"None",SearchDataSubmitted.Part_number)

'Open usersregistered table to retrieve account number and sales rep based on user id
tbl = table.open("[PathAlias.ADB_Path]\usersregistered_sql")
query.filter = "username="+quote(var->session.UserID)
query.order = ""
qry = tbl.query_create()
session.Account_Number = alltrim(tbl.ACCOUNT_NUMBER)
session.Sales_Rep = alltrim(tbl.SALES_REP)

tbl.close()



'check if user is internal or external
session.Int_Ext = if(session.Sales_Rep = "Internal","Internal","External" )


'write info to user tracking table

dim cn as sql::Connection
flag = cn.open("::Name::snap-sql")

dim sqlCommand as c

sqlInsert = "Insert into user_tracking (userid, account_number, date, page_title, event_type, sales_rep, vendor_specified, part_specified, int_ext, client_type, ip_address) Values (:newuserid, :newaccount_number, :newdate, :newpage_title, :newevent_type, :newsales_rep, :newvendor_specified, :newpart_specified, :newint_ext, :newclient_type, :newip_address)"

dim args as sql::arguments
args.add("newuserid", "session.UserID")
args.add("newaccount_number", "session.Account_Number")
args.add("newdate", "date()")
args.add("newpage_title", "Cameras")
args.add("newevent_type", "Product Search")
args.add("newsales_rep", "session.Sales_Rep")
args.add("newvendor_specified", "session.Vendor")
args.add("newpart_specified", "session_Part")
args.add("newint_ext", "session_Int_Ext")
args.add("newclient_type", "Desktop")
args.add("newip_address", "request.remote_addr")


flag = cn.execute(sqlCommand, sqlInsert, args)



end function

[Davidk]

Re: xbasic - insert into sql table

Here's what I see...

Do not dim session vars... it is unnecessary.
Use args.set... not args.add. .add just adds. .set adds or re-sets if already established.
You are putting quotes around the session vars you're using in the args... therefore the session var values are not being evaluated. Same with that one request var... and the date().

args.add("newuserid", session.UserID)
args.set("newaccount_number", session.Account_Number)
args.set("newdate", date())
args.set("newpage_title", "Cameras")
args.set("newevent_type", "Product Search")
args.set("newsales_rep", session.Sales_Rep)
args.set("newvendor_specified", session.Vendor)
args.set("newpart_specified", session_Part)
args.set("newint_ext", session_Int_Ext)
args.set("newclient_type", "Desktop")
args.set("newip_address", request.remote_addr)

Using date() will also mess you up since SQL wants to see yyyy-MM-dd format.

What you can do for date() is use the SQL_Date() function... which is horribly documented.

Do something like this...

args.set("newdate",sql_date(date(),2))

sql_date results in your date being properly formatted, but enclosed in {}... which will blow up a sql statement. Why this could ever be considered a sql date is an Alpha trademark.

The optional 2 of sql_date fixes this issue. Completely undocumented, of course.

[carlbsmith]

Re: xbasic - insert into sql table

Thanks for the feedback. I made the suggested changes, so now it all looks like this:


function BeforeSearch as v (SearchDataSubmitted as P, Args as p, PageVariables as p)
with PageVariables
end with

'Get currrent user id
session.UserID = a5ws_getcurrentuser()

'Check for specific vendor search
session.Vendor = if(SearchDataSubmitted.Manufacturer = null_value(),"None",SearchDataSubmitted.Manufacturer)

'Check for specific part number search
session.Part = if(SearchDataSubmitted.Part_number = null_value(),"None",SearchDataSubmitted.Part_number)

'Open usersregistered table to retrieve account number and sales rep based on user id
tbl = table.open("[PathAlias.ADB_Path]\usersregistered_sql")
query.filter = "username="+quote(var->session.UserID)
query.order = ""
qry = tbl.query_create()
session.Account_Number = alltrim(tbl.ACCOUNT_NUMBER)
session.Sales_Rep = alltrim(tbl.SALES_REP)

tbl.close()



'check if user is internal or external
session.Int_Ext = if(session.Sales_Rep = "Internal","Internal","External" )


'write info to user tracking table

dim cn as sql::Connection
flag = cn.open("::Name::snap-sql")

dim sqlCommand as c

sqlInsert = "Insert into user_tracking (userid, account_number, date, page_title, event_type, sales_rep, vendor_specified, part_specified, int_ext, client_type, ip_address) Values (:newuserid, :newaccount_number, :newdate, :newpage_title, :newevent_type, :newsales_rep, :newvendor_specified, :newpart_specified, :newint_ext, :newclient_type, :newip_address)"

dim args as sql::arguments
args.set("newuserid", session.UserID)
args.set("newaccount_number", session.Account_Number)
args.set("newdate", sql_date(date(),2))
args.set("newpage_title", "Cameras")
args.set("newevent_type", "Product Search")
args.set("newsales_rep", session.Sales_Rep)
args.set("newvendor_specified", session.Vendor)
args.set("newpart_specified", session_Part)
args.set("newint_ext", session_Int_Ext)
args.set("newclient_type", "Desktop")
args.set("newip_address", request.remote_addr)


flag = cn.execute(sqlCommand, sqlInsert, args)



end function





For some reason it is still not writing to my table. I also opened up code editor to verify a few things and this was my result:


dim cn as sql::Connection
flag = cn.open("::Name::snap-sql")
?flag
= .T.


dim sqlCommand as c

sqlInsert = "Insert into user_tracking (userid, account_number, date, page_title, event_type, sales_rep, vendor_specified, part_specified, int_ext, client_type, ip_address) Values (:newuserid, :newaccount_number, :newdate, :newpage_title, :newevent_type, :newsales_rep, :newvendor_specified, :newpart_specified, :newint_ext, :newclient_type, :newip_address)"

dim args as sql::arguments
args.set("newuserid", session.UserID)
args.set("newaccount_number", session.Account_Number)
args.set("newdate", sql_date(date(),2))
args.set("newpage_title", "Cameras")
args.set("newevent_type", "Product Search")
args.set("newsales_rep", session.Sales_Rep)
args.set("newvendor_specified", session.Vendor)
args.set("newpart_specified", session_Part)
args.set("newint_ext", session_Int_Ext)
args.set("newclient_type", "Desktop")
args.set("newip_address", request.remote_addr)


flag = cn.execute(sqlCommand, sqlInsert, args)
?flag
= .T.




end function
ERROR: Mismatched end of loop or if-block


Not sure if the error at the end here has anything to do with my issue or not. Any additional thoughts? Thanks!

[Davidk]

Re: xbasic - insert into sql table

You really need to add a debug(1) statement to your code and debug through it in Live or Working Preview.

This statement is wrong...

flag = cn.execute(sqlCommand, sqlInsert, args)

I don't know what sqlCommand is. It should be...

flag = cn.execute(sqlInsert, args)

[carlbsmith]

Re: xbasic - insert into sql table

Thanks for the suggestion. I removed sqlCommand. After running in live preview with debug(1) statement, here are the errors that I saw:

args.set("newpart_specified", session_Part) --------------> Variable "session_Part" not found.
args.set("newint_ext", session_Int_Ext) -----------------------> Variable "session_Int_Ext" not found.
flag = cn.execute(sqlinsert,args) -------------------------> Argument 2 must be an object of type SQL::Arguments or SQL::TableInfo

[Davidk]

Re: xbasic - insert into sql table

session.Part and session.Int_Ext. You've got a "_" after session instead of a "."

[carlbsmith]

Re: xbasic - insert into sql table

Oh wow, you are absolutely right on that. So then really the last error I can't figure out is this one:

flag = cn.execute(sqlinsert,args) -------------------------> Argument 2 must be an object of type SQL::Arguments or SQL::TableInfo

[Davidk]

Re: xbasic - insert into sql table

You'd have to post the full function again... I don't see anything wrong with that statement.

[carlbsmith]

Re: xbasic - insert into sql table

Here is the entire function:

function BeforeSearch as v (SearchDataSubmitted as P, Args as p, PageVariables as p)
with PageVariables
end with


'Get currrent user id
session.UserID = a5ws_getcurrentuser()

'Check for specific vendor search
session.Vendor = if(SearchDataSubmitted.Manufacturer = null_value(),"None",SearchDataSubmitted.Manufacturer)

'Check for specific part number search
session.Part = if(SearchDataSubmitted.Part_number = null_value(),"None",SearchDataSubmitted.Part_number)

'Open usersregistered table to retrieve account number and sales rep based on user id
tbl = table.open("[PathAlias.ADB_Path]\usersregistered_sql")
query.filter = "username="+quote(var->session.UserID)
query.order = ""
qry = tbl.query_create()
session.Account_Number = alltrim(tbl.ACCOUNT_NUMBER)
session.Sales_Rep = alltrim(tbl.SALES_REP)

tbl.close()

'check if user is internal or external
session.Int_Ext = if(session.Sales_Rep = "Internal","Internal","External" )


'write info to user tracking table

dim cn as sql::Connection
flag = cn.open("::Name::snap-sql")


sqlinsert = "insert into user_tracking (userid, account_number, date, page_title, event_type, sales_rep, vendor_specified, part_specified, int_ext, client_type, ip_address))values (:newuserid, :newaccount_number, :newdate, :newpage_title, :newevent_type, :newsales_rep, :newvendor_specified, :newpart_specified, :newint_ext, :newclient_type, :newip_address)"

Dim args as sql::arguments


args.set("newuserid", session.UserID)
args.set("newaccount_number", session.Account_Number)
args.set("newdate", sql_date(date(),2))
args.set("newpage_title", "Cameras")
args.set("newevent_type", "Product Search")
args.set("newsales_rep", session.Sales_Rep)
args.set("newvendor_specified", session.Vendor)
args.set("newpart_specified", session.Part)
args.set("newint_ext", session.Int_Ext)
args.set("newclient_type", "Desktop")
args.set("newip_address", request.remote_addr)



flag = cn.execute(sqlinsert, args)



As a side note, I installed the pre-release for today, which includes an xbasic SQL actions code generator. I ran through the genie, and this is what it created:

dim cn as sql::connection
dim flag as l
flag = cn.open("::Name::snap-sql")
if flag = .f. then
'there was an error
dim errorText as c
errorText = cn.callresult.text
end
end if
'turn on portable SQL
cn.PortableSQLEnabled = .t.
dim SQL as c
sql = "INSERT INTO user_tracking(USERID,ACCOUNT_NUMBER,DATE,PAGE_TITLE,EVENT_TYPE,SALES_REP,VENDOR_SPECIFIED,PART_SPECIFIED,INT_EXT,CLIENT_TYPE,IP_ADDRESS) VALUES (:USERID,:ACCOUNTNUMBER,:DATE,:PAGETITLE,:EVENTTYPE,:SALESREP,:VENDORSPECIFIED,:PARTSPECIFIED,:INTEXT,:CLIENTTYPE,:IPADDRESS)"
dim args as sql::arguments
args.add("USERID","session.UserID")
args.add("ACCOUNTNUMBER","session.Account_Number")
args.add("DATE","sql_date(date(),2)")
args.add("PAGETITLE","Cameras")
args.add("EVENTTYPE","Product Search")
args.add("SALESREP","session.Sales_Rep")
args.add("VENDORSPECIFIED","session.Vendor")
args.add("PARTSPECIFIED","session.Part")
args.add("INTEXT","session.Int_Ext")
args.add("CLIENTTYPE","Desktop")
args.add("IPADDRESS","request.remote_addr")
'execute the SQL
flag = cn.execute(sql,args)
if flag = .f. then
'there was an error
dim errorText as c
errorText = cn.callresult.text
cn.close()
end
end if
dim rowsInserted as n
'get the number of rows that were inserted
rowsInserted = cn.CallResult.rowsaffected
cn.close()


Note: It did not work either. Also, it uses "add" instead of "set", and it ended up putting quotes around my session variables. I changed all of that as a test and still it failed. And the line with 'flag = cn.execute(sql,args)' also kicked out a similar error message during debug: Argument 2 must be an object of type SQL::Arguments or SQL::TableInfo

[Davidk]

Re: xbasic - insert into sql table

That genie output is garbage... that's really bad. You should report that.

You're fixing old errors, but introducing new errors. You sqlinsert statement has an extra ")" near ip_address.

I also wonder if your insert data is creating a problem. If fixing the extra ")" doesn't work, then post the values that are being in the Insert for the arguments. Maybe there's something odd in the data.

I just ran that corrected code and had no issues using sample data. All my table fields are varchar - so that won't match yours, but if you want to dump your table definition
and post it I'll test with that.

[carlbsmith]

Re: xbasic - insert into sql table

I corrected the extra ")". Same issue. Here is some info on my data, let me know if you need anything else. And thanks again for looking at this.

table.png

Here is some example data:
record_id: xxxxx
userid: [email protected]
account_number: 800167
Date: 2018-03-05
Page_title: Wireless
Event_type: product search
Sales_rep: Joe Smith
Vendor_specified: Samsung
Part_specified: None
Int_Ext: Internal
Client_type: desktop
IP_address: x.x.x.x

[Davidk]

Re: xbasic - insert into sql table

Ok... thanks... here's the problem. Using this code where you are using it... you already have an variable named "Args"... it's one of the parameters...

function BeforeSearch as v (SearchDataSubmitted as P, Args as p, PageVariables as p)

Change your dim statement to myArgs... and all the args.set statements to myArgs.set... and change the cn.execute statement... and all will be ok.

dim myArgs as sql::arguments
myArgs.set("newuserid", session.UserID)
flag = cn.execute(sqlinsert, myArgs)

[carlbsmith]

Re: xbasic - insert into sql table

You nailed it. That was my issue. Thank you so much!

[pboomwork]

Re: xbasic - insert into sql table

Glad you got your problem resolved. Didn't see your message till now, I've used SQL from Xbasic a great deal and have ended up with some function for troubleshooting this. I've posted them to the Code Archive maybe they would be of help to someone else that comes across this posting.
https://www.alphasoftware.com/alphaf...ead.php?122003

[carlbsmith]

Re: xbasic - insert into sql table

Thanks. But the thread you reference has a link that doesn't do anything when I click on it.