Enter SQL statements directly into the FF3 code editor

[Paul Squires]

Here is one that I noticed over on Edwin's forum. Be able to enter free form text for SQL statements directly into the code editor. At compile time, FF3 would convert those statements to the appropriate PB formated code so that the compiler will read it.

For example: (right from Edwin's example)

Code Select Expand


   Local sSQL As String

sSQL = @{create table customer 
( 
    customer_id serial, 
    title  char(4), 
    fname  varchar(32), 
    lname  varchar(32) not null, 
    addressline varchar(64), 
    town   varchar(32), 
    zipcode     char(10) not null, 
    phone  varchar(16), 
    CONSTRAINT  customer_pk PRIMARY KEY(customer_id) 
);
}@
    VD_ADO_Execute( "postgresql's connectionstring here...", sSQL, Empty )


Would become....

Code Select Expand


    sSQL = "create table customer " & $CrLf _
    & "( " & $CrLf _
    & "customer_id serial, " & $CrLf _
    & "title  char(4), " & $CrLf _ 
    & "fname  varchar(32), " & $CrLf _ 
    & "lname  varchar(32) Not Null, " & $CrLf _ 
    & "addressline varchar(64), " & $CrLf _ 
    & "town   varchar(32), " & $CrLf _ 
    & "zipcode     char(10) Not Null, " & $CrLf _ 
    & "phone  varchar(16),  " & $CrLf _
    & "CONSTRAINT  customer_pk PRIMARY KEY(customer_id)  " & $CrLf _
    & "); " & $CrLf
     
    VD_ADO_Execute( "postgresql's connectionstring here...", sSQL, Empty )


We would also need some way of allowing variables to be interspersed within the free form text so that they are properly handled during the conversion. Maybe wrap them in a special character or something.

Anyway, I thought that it was a neat idea. Not sure if it will make it to FF3 but I thought that I should at least throw it out there for consideration.

[Martin Francom]

I am a novice, but I don't quite see the advantage.  It doesn't seem to make the code any more readable, and you would be mixing two syntax styles, which might made the code less readable to some only versed in PB.  And it introduces more more interpretation step which might make finding coding erros more difficult.

What was the advantage?

[Paul Squires]

The advantage is the ease of being able to enter your SQL statements into the code editor. If you have dealt with entering SQL into a PB program then you quickly see that it becomes a pain having to ensure that things are wrapped in quotes correctly and that each line concatenated to the next. Entering the text in a free form fashion makes it cleaner to visualize the SQL statement. Actually, in many cases you may be able to copy/paste the SQL statement directly out of a program like SQL Expert and into the FF3 code editor.

I see a use for it... granted, it is not a major priority.

There was a topic over int he PB forums recently about this type of stuff. For example, say that you want to enter 20 lines of free form text into a TextBox. You have to have each line in your code embedded in quotes, and concatenated to each other with $CRLF & _ statements at the end of each line.

[Haakon Birkeland]

I see where you're going. Doing quite large queries in ASP. problems with a missing quote/space/ next line statements are not always that easy to spot when things act up because of it.

[Nathan Durland]

I do a lot of work with SQL.  This looks cool, but I don't think I'd ever use it.