Ok, I'll admit it right up from, I'm new to this Xbasic stuff but as a 20 year veteran of Foxpro programming, I'm trying really hard to wrap my head around how things are done in Alpha Anywhere. There is something I do a LOT of that I need to verify I can do in Alpha Anywhere. Basically, I am trying to write an Xbasic script that will import a CSV file to a temporary cursor or DBF file, then open the DBF file and loop through the records in the DBF file, use a Select Statement to determine whether a record exists in a SQL database and then depending upon whether a record exists, update the existing SQL data or create an insert statement to add any new records.
I'm sure that the folks who use Alpha everyday think this is probably a dumb question, but after four days of watching videos and trying out code from tutorials I'm about as frustrated as it gets trying to figure out how to do something that I can do in five minutes in VFP. Here's the psuedo code in VFP:
--------------------------------------------------------
lnSQLConnection = (SQL CONNECTION MADE)
Create cursor "tmpData" (rec_no I NOT NULL, name C(40) NOT NULL, address C(40) NOT NULL, city C(20) NOT NULL, state c(2) NOT NULL, zip c(10) NOT NULL)
select tmpData
append from "memberdata.csv" type csv
Goto top in tmpData
scan
lcSQLCmd = "Select * from Members where members.rec_no = ' + transform(tmpData.rec_no)
SQLEXEC(lnSQLConnection, lcSQLCmd, "tmpResultSet")
if reccount('tmpResultSet') > 0
lcUpdateCmd = "update members set name = '" + tmpData.Name "' where members.rec_no = " + transform(tmpData.rec_no)
SQLEXEC(lnSQLConnection, lcUpdateCmd)
else
lcInsertCmd = "insert into members (rec_no, name) values (" + transform(tmpData.rec_no) + ",'" + tmpData.Name + "')"
SQLEXEC(lnSQLConnection, lcInsertCmd)
endif
endscan
release lnSQLConnection
--------------------------------------------------------
I have frankensteined a script that I can post later (not at that particular machine right now) that kinda works up to the point where I'm trying to loop through the temporary dbf and issue the SQL commands against the database where I'm getting an error about AA not being able to give me a result set while another result set is in progress. I've tried to test each line via the interactive window but no luck so far.
I should tell you that the SQL I'm trying to work with is FirebirdSQL using the AlphaDAO ODBC connection. (I've used Firebird in the past with other projects and I've been pleased with the results, but I'm not married to it. I just couldn't decide whether to use MSSQL, MySQL or PostgreSQL and so I picked the one with which I was more familiar.) If the problems I'm experiencing are tied to using the ODBC type connection to Firebird rather than a native AlphaDAO connection string, I'm willing to swap. Unfortunately, I'm so new at the Alpha logic and the Xbasic language that I can't tell if it is my ignorance or a technical issue.
If someone can provide a translation of the code I have included above to the XBasic language I think it would really help me out a lot. This shouldn't be that hard to do, but for some reason, I'm banging my head against the wall to figure this out.
Thanks In Advance!
I'm sure that the folks who use Alpha everyday think this is probably a dumb question, but after four days of watching videos and trying out code from tutorials I'm about as frustrated as it gets trying to figure out how to do something that I can do in five minutes in VFP. Here's the psuedo code in VFP:
--------------------------------------------------------
lnSQLConnection = (SQL CONNECTION MADE)
Create cursor "tmpData" (rec_no I NOT NULL, name C(40) NOT NULL, address C(40) NOT NULL, city C(20) NOT NULL, state c(2) NOT NULL, zip c(10) NOT NULL)
select tmpData
append from "memberdata.csv" type csv
Goto top in tmpData
scan
lcSQLCmd = "Select * from Members where members.rec_no = ' + transform(tmpData.rec_no)
SQLEXEC(lnSQLConnection, lcSQLCmd, "tmpResultSet")
if reccount('tmpResultSet') > 0
lcUpdateCmd = "update members set name = '" + tmpData.Name "' where members.rec_no = " + transform(tmpData.rec_no)
SQLEXEC(lnSQLConnection, lcUpdateCmd)
else
lcInsertCmd = "insert into members (rec_no, name) values (" + transform(tmpData.rec_no) + ",'" + tmpData.Name + "')"
SQLEXEC(lnSQLConnection, lcInsertCmd)
endif
endscan
release lnSQLConnection
--------------------------------------------------------
I have frankensteined a script that I can post later (not at that particular machine right now) that kinda works up to the point where I'm trying to loop through the temporary dbf and issue the SQL commands against the database where I'm getting an error about AA not being able to give me a result set while another result set is in progress. I've tried to test each line via the interactive window but no luck so far.
I should tell you that the SQL I'm trying to work with is FirebirdSQL using the AlphaDAO ODBC connection. (I've used Firebird in the past with other projects and I've been pleased with the results, but I'm not married to it. I just couldn't decide whether to use MSSQL, MySQL or PostgreSQL and so I picked the one with which I was more familiar.) If the problems I'm experiencing are tied to using the ODBC type connection to Firebird rather than a native AlphaDAO connection string, I'm willing to swap. Unfortunately, I'm so new at the Alpha logic and the Xbasic language that I can't tell if it is my ignorance or a technical issue.
If someone can provide a translation of the code I have included above to the XBasic language I think it would really help me out a lot. This shouldn't be that hard to do, but for some reason, I'm banging my head against the wall to figure this out.
Thanks In Advance!
Comment