Apr
15
Written by:
Robert Houben, CTO
4/15/2010 10:38 AM
Accessing SQL Data with the Legacy2SQL Bridge
MultiValue Approach to File Processing
When processing file data in a MultiValue system, you typically handle them by doing the following steps:
· OPEN a file handle
· Perform reads, writes, and selects against the file handle.
· Optionally close the file handle (not always meaningful in some MV systems).
The operations against the file are typically done using dynamic arrays, with attribute delimiters to separate fields. Because SQL Server does not handle multivalues or subvalues, we assume that any access to SQL data will be done in a way that only processes single-valued fields (if you choose to hide delimiters in that data, that’s your business!)
What would be really nice is if you had a set of APIs that let you do an OPEN, READ, WRITE, DELETE, SELECT and CLOSE against data from a table in a relational database. This is exactly what we have done! Additionally, we’ve provided COPY-TO-SQL and COPY-FROM-SQL operations that you can do using API calls.
So let’s look at some simple examples:
Writing Data to SQL Server From MV BASIC
Here is a SQL table called CUST:
What would be desirable is to be able to access this by creating a dynamic array:
CUSTID = 1
CUSTDATA = "JOHN DOE":@AM:51:@AM:"4/4/1959":@AM:123456.78:@AM:1076
Then call an API that would write this data out.
Here is how we’d do that with our API
0001: $INCLUDE MV2SQL.PROGRAMS MV2SQL.INCLUDE
0002: *
0003: CALL MV2SQL.OPEN.SUB("REMOTE.CUST", FILEHANDLE, ERROR)
0004: *
0005: * This error handling block is copied lower down.
0006: * You should probably put this in a subroutine.
0007: *
0008: IF ERROR NE '' THEN
0009: FOR A=1 TO DCOUNT(ERROR,AM)
0010: PRINT A 'R%2 ':ERROR
0011: NEXT A
0012: STOP
0013: END
0014: *
0015: ITEM ="JOHN DOE"
0016: ITEM<2>=51 ;* Age
0017: DT = DATE() - (365 * 51) ;* Birthdate today minus 51 years
0018: OUT.DATE=OCONV(DT,"DY"):"-":OCONV(DT,"DM"):"-":OCONV(DT,"DD")
0019: ITEM<3>=OUT.DATE
0020: ITEM<4>=123456.78 ; * NetWorth - we don't MD2 convert it
0021: ITEM<5>=INT(TIME()) ; * Birthtime - use the current time
0022: * Write out item-id '1' with this data
0023: CALL MV2SQL.WRITE.SUB(FILEHANDLE, '1', ITEM, ERROR)
0024: IF ERROR NE '' THEN
0025: FOR A=1 TO DCOUNT(ERROR,AM)
0026: PRINT A 'R%2 ':ERROR
0027: NEXT A
0028: STOP
0029: END
0030: STOP
0031: *
0032: END
After running this, here is the output of a SQL command:
You can see that this is quite easy to work with. Now let’s look at some of the code a bit closer.
Error Handling
In any statement like OPEN, READ, WRITE, etc. there is a THEN/ELSE syntax that gets exploited. To do the same thing you would use this syntax immediately after:
IF ERROR EQ "" THEN
…
END ELSE
…
END
Note that by using NE instead of EQ in our program, we are doing the equivalent of an ELSE clause on both the OPEN and the WRITE. Note also that WRITE can fail, since we are writing to a remote database that might refuse our call or fail for another reason.
Let’s say that the write failed, for instance, for a foreign key constraint that our data violated. Here is what the output of our error handling block would look like:
01 FWIS:JDBC:Error processing data operations for Custom action 'MV2SQLB.MV2SQLB'
02 Item Data:
03 934^^50^1974-03-11 00:00:00.0^199521.44^54822
04 SQLState=23000
05 SQLMessage=The INSERT statement conflicted with the FOREIGN KEY constraint "FK_CUST_CUSTNAMES". The conflict occurred in database "RHStaging", table "dbo.CUSTNAMES", column 'CUSTNAME'.
To save you parsing this for relevant information, you can pass this to our API routine as follows:
0032: CALL MV2SQL.PARSE.ERROR.SUB(ERROR, PRIMARY.CAUSE,
SECONDARY.CAUSE, SQLSTATE, MESSAGE)
0033: PRINT 'PRIMARY.CAUSE =':PRIMARY.CAUSE
0034: PRINT 'SECONDARY.CAUSE=':SECONDARY.CAUSE
0035: PRINT 'SQLSTATE =':SQLSTATE
0036: PRINT 'MESSAGE =':MESSAGE
Which will give you this output:
PRIMARY.CAUSE =FWIS
SECONDARY.CAUSE=JDBC
SQLSTATE =23000
MESSAGE =The INSERT statement conflicted with the FOREIGN KEY constraint "FK_CUST_CUSTNAMES". The conflict occurred in database "RHStaging", table "dbo.CUSTNAMES", column 'CUSTNAME'.
Reading Data From SQL Server
Now let’s look at what it would take to read that same record back. First, the program:
0001: $INCLUDE MV2SQL.PROGRAMS MV2SQL.INCLUDE
0002: *
0003: CALL MV2SQL.OPEN.SUB("REMOTE.CUST", FILEHANDLE, ERROR)
0004: IF ERROR NE '' THEN
0005: FOR A=1 TO DCOUNT(ERROR,AM)
0006: PRINT A 'R%2 ':ERROR
0007: NEXT A
0008: STOP
0009: END
0010: *
0011: CALL MV2SQL.READ.SUB(FILEHANDLE, '1', ITEM, ITEMEXISTS, ERROR)
0012: IF ERROR NE '' THEN
0013: FOR A=1 TO DCOUNT(ERROR,AM)
0014: PRINT A 'R%2 ':ERROR
0015: NEXT A
0016: STOP
0017: END
0018: IF ITEMEXISTS THEN
0019: PRINT 'THEN CLAUSE:'
0020: PRINT ITEM
0021: END ELSE
0022: PRINT 'ELSE CLAUSE'
0023: ITEM=''
0024: END
0025: STOP
0026: *
0027: END
The output when we run it is:
THEN CLAUSE:
JOHN DOEþ51þ1959-04-28 00:00:00.0þ123456.78þ46309
Note that you might want to add the following lines:
ITEM<3>=ICONV(FIELD(ITEM<3>," ",1),"D")
ITEM<4>=ICONV(ITEM<4>,"MD2")
You would then have a dynamic array with dates and dollar amounts in internal format.
Note also that you now have two possibilities. The ELSE clause in a READ statement is not really an indication of an “ERROR” condition, but used to signal that the item does not exist. You really need to look for a true ERROR condition and handle it separately. The ITEMEXISTS Boolean return value is really used to duplicate the THEN/ELSE portion of the READ statement syntax.
Special Read Case – Passthru
We have a special case of the read, where instead of mapping the columns you want, you can call any SQL statement. Let’s look at an example:
0001: $INCLUDE MV2SQL.PROGRAMS MV2SQL.INCLUDE
0002: *
0003: CALL MV2SQL.OPEN.SUB("REMOTE.CUST", FILEHANDLE, ERROR)
0004: IF ERROR NE '' THEN
0005: FOR A=1 TO DCOUNT(ERROR,AM)
0006: PRINT A 'R%2 ':ERROR
0007: NEXT A
0008: STOP
0009: END
0010: CMD="select * from CUST where CUSTID <= 80"
0011: CALL MV2SQL.PASSTHRU.SUB(FILEHANDLE, CMD, TRUE, 20000, RESULTS, ERROR)
0012: IF ERROR NE '' THEN
0013: FOR A=1 TO DCOUNT(ERROR,AM)
0014: PRINT A 'R%2 ':ERROR
0015: NEXT A
0016: STOP
0017: END
0018: ROWCOUNT=DCOUNT(RESULTS,LF)
0019: FOR R=1 TO ROWCOUNT
0020: ROW=FIELD(RESULTS,LF,R)
0021: ID=ROW<1>
0022: ITEM=DELETE(ROW,1,0,0)
0023: PRINT 'ID=':ID:' ITEM=':ITEM
0024: NEXT R
0025: STOP
0026: *
0027: END
Here is its output:
ID=1 ITEM=JOHN DOEþ51þ1959-04-28 00:00:00.0þ123456.78þ46309
ID=78 ITEM=MMMMMMMMMMMþ12þ1995-03-30 00:00:00.0þ138526.73þ54759
ID=79 ITEM=HHHHHHHHHþ48þ1959-04-04 00:00:00.0þ105387.96þ54759
ID=80 ITEM=BBBBBBBþ47þ1960-02-07 00:00:00.0þ144493.16þ54759
So, what did we do here?
First, we returned a special dynamic array, based on the results of a dynamic SQL statement. In this case the statement was:
select * from CUST where CUSTID <= 80
The dynamic array consists of a series of SQL rows, separated by a linefeed character. Each row has its fields formatted, and stored in an attribute delimited dynamic array. Please note that you can create massive dynamic arrays with some SQL statements. It’s up to you to manage the size of your results. We’ve given you rope, please use it responsibly!
Note that the API call has some interesting parameters included:
We pass TRUE (equated to 1) to the 3rd parameter of the subroutine, to indicate that the command is expected to return results. You can set this to FALSE and run any SQL command you have permissions to that does not return a result set.
We pass 20000 to parameter 4, to indicate a timeout value (in milliseconds). We expect this command to always succeed within 20 seconds. Set this to zero to indicate no timeout.
Note also that we had to pass it a valid handle, so it would know which SQL Server database to get a connection for. We don’t use the file handle for anything else.
How Do I Configure This?
So, we’ve shown how easy it is to use all this, but how easy is it to configure?
First, let’s look at two records:
Referencing the Legacy2SQL Bridge Server
Here is the record that defines where the Legacy2SQL Bridge Server is and how to access it from MV/BASIC:
>ED MV2SQL.CONFIG MV2SQL.INI
19 lines long.
----: P
0001: 1
0002: http://localhost
0003: /mv/FWMV2SQLB/logs/http.log
0004: 20000
0005: 0
0006: /mv/FWMV2SQLB/certs/myserver.cer
0007: 2
0008: 1
0009: /mv/FWMV2SQLB/
0010: * Start comments
0011: amc 1 = LoggingOn
0012: amc 2 = URL
0013: amc 3 = LogPath
0014: amc 4 = Timeout (milliseconds)
0015: amc 5 = Secure
0016: amc 6 = CertPath
0017: amc 7 = CertFormat (2 = DER format)
0018: amc 8 = CertAlgorithm (1 = RSA key)
0019: amc 9 = AbstractAccountPath
Bottom at line 19.
In our case, we host it on our local system, so we access it via localhost http socket 80. We have default timeout of 20 seconds (20,000 milliseconds). Most of these values should be obvious.
The server is configured to have a JDBC connection to SQL Server using the latest JDBC driver from Microsoft (yes, they provide one, and it’s very full-featured, robust and fast.) A connection to our database is defined with the name JDBCTest.
Referencing the CUST Table
Here is the record that references the CUST table for us. Note that in our examples we referenced it with the name “REMOTE.CUST”:
>ED MV2SQL.POINTERS REMOTE.CUST
8 lines long.
----: P
0001: Q
0002:
0003: JDBCTEST:ODBC:CUST
0004: CUSTýýCUSTIDýCustNameýAgeýBirthdateýNetWorthýBirthtime
0005: ýýNUMBERýVARCHAR2ýNUMBERýDATEýNUMBERýNUMBER
0006: ýý20ý50ý20ý20ý20ý20
0007: ýýNýYýYýYýYýY
0008: ýýýýýý2ý
Bottom at line 8.
Note that attributes 5-8 are ignored for SQL Server, but are required for Oracle. This format was used to support a product that one of our customers was previously using. Here is how it works:
Attirubte 3 has 3 segments. The first is the name of the connection as defined in the Legacy2SQL Bridge server. The second is always “ODBC”. The last is the name of the table.
Attribute 4 has a series of multivalues. The first is the table name (again) the second is reserved, the third is the first column, assuming it’s a primary key, and the remainder are all the remaining columns that are not primary key columns.
In the case of an Oracle target, the subsequent attributes have the data type, precision, nullable, and scale for the columns.
Summary
With just a little bit of configuration, you can have an environment that will quickly enable your multivalue programmers to get up to speed.
We have not delved into the API’s for the MV equivalent of the BASIC SELECT and DELETE statements, or the ones that do the COPY-TO-SQL or COPY-FROM-SQL, but they are equally easy to use and powerful.
Copyright ©2010 Robert Houben, CTO