4.3 SQL Interface

gtisdale · Post by **gtisdale** » Sun Jun 03, 2012 3:28 pm

I'm trying to update my old VB interface to an ACCESS GL to the new BR4.30 SQL interface becasue I need to be able to process the link on the Server side, the current VB interface is on the Client side.

The issue that I do not understand is the function of the various commands

There is a CONFIG command that estblishess the linkage tot he SQL/ACCESS database. This I understand

There is then a OPEN statement that names the TABLE and the columns within the database that are to be accessed and processed in some manner

There is a WRITE statement

There is a READ statement

What I am trying to understand now is the relationship among these last three. Is the OPEN statement required for each TABLE within the database to be accessed? This then lists the TABLE name and the COLUMNS within the database?

Does the WRITE command then execute the search established by the OPEN statement and load the selected variables into a memory space for further processing by READ statements? Or, does the READ statement do this?

If a record is to be added to the selected table how is this specified?

Thanks for any help on this. More questions will follow once I get through this. One question will be "How and when is the linkage established by the CONFIG statement broken? Also, if the critereqa for a table selection is changed do we need to CLOSE the file and then OPEN it again with a new SQL statement?

FNGeorge

bluesfannoz · Post by **bluesfannoz** » Sun Jun 03, 2012 4:04 pm

George
I just happen to be working on a program today that is moving data from our BR files into a SQL database.

What I am trying to understand now is the relationship among these last three. Is the OPEN statement required for each TABLE within the database to be accessed? This then lists the TABLE name and the COLUMNS within the database?

I open the database the same way and reference different tables in my SQL code.

Does the WRITE command then execute the search established by the OPEN statement and load the selected variables into a memory space for further processing by READ statements? Or, does the READ statement do this?

That is correct. The Write Statement submits the SQL to the database. The Read statement then parses through what is returned.

If a record is to be added to the selected table how is this specified?

My Config Statement(Not an Access database-Yours will need to match your database)
EXECUTE 'CONFIG database '&DATABASE$&' CONNECTSTRING="DSN=SQLite3 Datasource;Database=.\MAPP.db;StepAPI=0;SyncPragma=NORMAL;NoTXN=0;Timeout=100000;ShortNames=0;LongNames=0;NoCreat=0;NoWCHAR=0;FKSupport=0;JournalMode=;OEMCP=0;LoadExt=;BigInt=0;PWD=;"'

APRMF is my Table in my MAPP.db

SQL to add a record
LET SQL$="INSERT INTO APRMF (APRMFID,AJENO) VALUES (1,"0001")"

SQL to update a record
LET SQL$="UPDATE APRMF SET AJENO="0002" WHERE APRMFID=1"

Thanks for any help on this. More questions will follow once I get through this. One question will be "How and when is the linkage established by the CONFIG statement broken? Also, if the critereqa for a table selection is changed do we need to CLOSE the file and then OPEN it again with a new SQL statement?

Yes you need to close the file channel and reopen it with any change to the SQL your submitting to the database based upon my experience. I am not working with access. I am working with a SQLite database.

Hope this helps!

GomezL · Post by **GomezL** » Sun Jun 03, 2012 6:48 pm

If you look at the conference thumb drive I have demonstration programs in CLSINC\RPT\NWP.WB

00030 GOSUB CONFIG_ACCESS

CONFIG_ACCESS defines database "MyDB", this refers to the "MDB" or "Database" that you want to use.

CONFIG Database allows you to define a database connection, in my case "mydb".

IO to the database will happen through "SQL Statements" like:
SELECT Key,Name,Addr,CSZ FROM ATTYFILE Where CSZ>'A' ORDER BY NAME

To perform IO, you simply open a file handle:
01010 OPEN #(Db_Handle:=20): "DATABASE=mydb",SQL Sql$,OUTIN

Notice that the Opens statement contains the SQL you wish to execute.

In order to actually "call the SQL statement, you need a "Write Statement"
01020 WRITE #Db_Handle:

To read the result of the query, using a "Read Statement"
02020 READ #Db_Handle: Key,Name$,Addr$,Csz$ EOF FINIS ERROR ERROR_READ

I also have an example that is much more flexible:
CLSINC\CSV\CSV_Export.wb

This example is very similar, but use "Arrays" to read the data
00030 GOSUB CONFIG_ACCESS
00040 LET Sql$="SELECT * FROM ATTYFILE" ! For Access

I then perform a query just to get the information about the table
01010 OPEN #(Db_Handle:=20): "DATABASE=mydb",SQL Sql$,OUTIN
01020 WRITE #Db_Handle:
01030 GOSUB GET_CSV_FIELDS
01040 CLOSE #Db_Handle:

** Note: I am sure this can be done in a better way, but it's what I did in my example.

GET_CSV_FIELDS pull all the the "Database" information
05010 LET Env$("STATUS.DATABASE.[MYDB].TABLES.[ATTYFILE].COLUMNS",Mat Columns$)

Eventually, it populates 4 arrays:
05250 MAT Attyfile_Fieldsc$(Attyfile_Fieldsc)
05260 MAT Attyfile_Fieldsn$(Attyfile_Fieldsn)
05270 MAT Attyfile_Data$(Attyfile_Fieldsc)=("")
05280 MAT Attyfile_Data(Attyfile_Fieldsn)=(0)

I the build a "Specific SQL statement"
01050 LET Sql_Fieldsc=Mat2str(Mat Attyfile_Fieldsc$,Sql_Fieldsc$,",")
01060 LET Sql_Fieldsn=Mat2str(Mat Attyfile_Fieldsn$,Sql_Fieldsn$,",")
01070 LET Sql$="SELECT "&Sql_Fieldsc$&","&Sql_Fieldsn$&" FROM AttyFile Where CSZ>'A' " ! For Access
01080 OPEN #(Db_Handle:=20): "DATABASE=mydb",SQL Sql$,OUTIN
01090 WRITE #Db_Handle:

Finally, I read the data into the arrays:

02020 READ #Db_Handle: Mat Attyfile_Data$,Mat Attyfile_Data EOF FINIS ERROR ERROR_READ

This technique should be very familiar to anyone that has used FILEIO.

gtisdale · Post by **gtisdale** » Wed Jun 06, 2012 5:32 am

I am getting closer. Thank you for the help Steve and Luis.

Next question related to adding information to the database.

If an INSERT statement is processed in the SQL$ phrase does this happen on the WRITE statement? If so then it seems that the variables being written to the database should be listed on the WRITE line following the ":", is this correct?

In this case then if more than one row is to be added to the database table would this be done with subsequent (or looped) write statements?

Again thanks for the help.

FNGeorge

bluesfannoz · Post by **bluesfannoz** » Wed Jun 06, 2012 7:42 am

Since I have not seen any syntax for the actual WRITE statement, I was unable to make that work. So I simply loop through opening the file channel each time like this: If you figure out the syntax for the WRITE statement, Please let us know!

Code: Select all

01090     OPEN #5: "NAME=ALMF.IDA,KFNAME=ALMF.IDX,SHR",INTERNAL,INPUT,KEYED
01110 RD_ALMF: READ #5,USING FRM_ALMF: ALMS$ EOF DONE_ALMF
01130   GOSUB GET_ALMF
01150   LET SQL$="INSERT INTO ALMF (ALMFID,ALSAC,ALFND,ALTYP,ALBYR,ALLOC,ALNME,ALFIN1,ALFIN2,ALFIN3,ALFIN4,ALFIN5"
01170   LET SQL$=SQL$&",ALFIN6,ALFIN7,ALCPO,ALVNO,ALEBF) VALUES ("&STR$(REC(5))&","&Q$&TRIM$(ALSAC$)&Q$
01190   LET SQL$=SQL$&","&Q$&TRIM$(ALFND$)&Q$&","&Q$&TRIM$(ALTYP$)&Q$&","&Q$&TRIM$(ALBYR$)&Q$&","&Q$&TRIM$(ALLOC$)&Q$&","&Q$&TRIM$(ALNME$)
01210   LET SQL$=SQL$&Q$&","&Q$&TRIM$(ALFIN$(1))&Q$&","&Q$&TRIM$(ALFIN$(2))&Q$&","&Q$&TRIM$(ALFIN$(3))&Q$&","&Q$&TRIM$(ALFIN$(4))&Q$&","&Q$&TRIM$(ALFIN$(5))
01230   LET SQL$=SQL$&Q$&","&Q$&TRIM$(ALFIN$(6))&Q$&","&Q$&TRIM$(ALFIN$(7))&Q$&","&Q$&TRIM$(ALCPO$)&Q$&","&Q$&TRIM$(ALVNO$)
01250   LET SQL$=SQL$&Q$&","&Q$&TRIM$(ALEBF$)&Q$&")"
01270   OPEN #(DB_HANDLE:=21): "DATABASE=MAPP",SQL SQL$,OUTIN
01290   WRITE #DB_HANDLE:
01310   CLOSE #DB_HANDLE:
01330   PRINT FIELDS "10,1,C": ALSAC$&" "&ALNME$
01350   GOTO RD_ALMF
01370 DONE_ALMF: CLOSE #5: : RETURN

gtisdale wrote:I am getting closer. Thank you for the help Steve and Luis.

Next question related to adding information to the database.

If an INSERT statement is processed in the SQL$ phrase does this happen on the WRITE statement? If so then it seems that the variables being written to the database should be listed on the WRITE line following the ":", is this correct?

In this case then if more than one row is to be added to the database table would this be done with subsequent (or looped) write statements?

Again thanks for the help.

FNGeorge

bluesfannoz · Post by **bluesfannoz** » Wed Jun 06, 2012 7:51 am

One important note on a glitch that I found if you often save programs to source and recompile them. On the Open Statement:

01270 OPEN #(DB_HANDLE:=21): "DATABASE=MAPP",SQL SQL$,OUTIN

If you save to source this is what you get for the above line.

01270 OPEN #(DB_HANDLE:=21): "DATABASE=MAPP", SQL$,OUTIN

Notice the SQL in front of SQL$ is missing. That causes an error on reload of that source.

As of 4.30beta+yf

GomezL · Post by **GomezL** » Wed Jun 06, 2012 8:00 am

This is a bit confusing at first, but after a bit, it starts to make more sense.

Step 1

01270 OPEN #(DB_HANDLE:=21): "DATABASE=MAPP",SQL SQL$,OUTIN

This command does not actually perform the SQL statement, it just defines it.

01290 WRITE #DB_HANDLE:

This command actually performs the SQL statement. I don't remember the details, but the open statement support parameters, so you can perform "Multiple Writes" against a single open.

Read #DB_HANDLE:

This is not in Steves "Write Loop", but the READ statement reads the result of the SQL statement that was executed by the WRITE statement.

In Steves example, there is no need for the write.

gtisdale · Post by **gtisdale** » Wed Jun 06, 2012 8:11 am

The dissappearing "SQL" still exists in 4.30beta-yg.

gtisdale · Post by **gtisdale** » Wed Jun 06, 2012 1:52 pm

Put “?” in the OPEN SQL statement then those are replaced by the parameters that follow the WRITE in the order that they appear?

If I want to make multiple WRITES can I then just put in more WRITE statements with the correct parameters following.

SQL$=”INSERT into ACCOUNT ([fdAccount],[FDType]) VALUES (?,?)”
Open #1: ‘database=mydb”, SQL SQL$,outin
WRITE #1:”1007”,5
WRITE #1:”1010”,5
CLOSE #1:

FNGeorge

bluesfannoz · Post by **bluesfannoz** » Wed Jun 06, 2012 2:59 pm

On your Question Marks for the Values, make sure you have the exact number to match the values you intend to Insert. If it doesn't match, you do not get an error currently. I had one too many question marks and the program ran without error. Got no data inserted into the database.

Using George's example this code would not error and nothing would be written so be careful! I told it to insert 2 columns with 3 values..

Code: Select all

SQL$=”INSERT into ACCOUNT ([fdAccount],[fdType]) VALUES (?,?,?)” 
Open #1: ‘database=mydb”, SQL SQL$,outin
WRITE #1:”1007”,5
WRITE #1:”1010”,5
CLOSE #1:

GomezL · Post by **GomezL** » Wed Jun 06, 2012 3:17 pm

I wonder.... (This has been confirmed to NOT WORK by fnGeorge)

Open #1: ‘database=mydb”, SQL "?",outin

SQL$=”INSERT into ACCOUNT ([fdAccount],[fdType]) VALUES (1007,5)”
WRITE #1:sql$

SQL$=”INSERT into ACCOUNT ([fdAccount],[fdType]) VALUES (1010,5)”
WRITE #1:sql$

CLOSE #1:

I am copying stuff out of context, but my wonder is if the entire "SQL" statement could be a "?".

(This has been confirmed to NOT WORK by fnGeorge)

gtisdale · Post by **gtisdale** » Wed Jun 06, 2012 3:24 pm

No, I tried that to start with.

Just the values as ? worked (assuming I used the same number of "?"'s as I did values) Thanks Steve for pointing that out!

FNGeorge

bluesfannoz · Post by **bluesfannoz** » Wed Jun 06, 2012 3:50 pm

I have gone back to the way I was doing it previously because it was catching errors in my insert statement with a 4006 error. With this method I had all the wrong column names for the table I selected and got no error at all.

Another reason I liked the way I was doing it was my SQL$ contained my entire valid SQL statement. With the ?.?.? in there that isn't a valid SQL statement. With my original method I could just copy and paste into my SQL Manager and check for Validity. Just my 2 cents.