[Gambas-user] MySQL frontbegin (tahara)
ron
ronstk at ...239...
Tue Oct 7 14:00:13 CEST 2003
Hi,
I am building a graphical interface to make the query's. I did it already in
PHP but the IE interface is to slow and to much info to transfer between the
webserver and the browser and also the widgets are difficult to make in
DHTML.
In VB I did start it also and that was much better. Now I am building it with
Gambas and the main goal is to make the query, not the database management
self. Maybe a run for the result of the query wil be included.
It can build the query now for 1...n tables and with JOIN for the tables. The
group by methods are not implemented yet as in my web based version are.
This is as graphical pane. The fields to use and conditions on the field are
in a grid with realtime updated select boxes and a TextArea showing realtime
the selections as SQL query. Adding the backticks if you want or not and
checking if tablenames must be used. Works with or without alias on field and
table names. Takes a lot of type work to do it by hand and this updates
simple the whole SQL line if you change a field to use.
I wil give it to Benoit to add as example or to include in his database
manager as tool when it is in a publishing state arrived.
For your question. Do you want to decode the SQL line or split a long SQL line
in seperate part each on his own a query? In that case a split(sqlline,";")
can do the job if there are no ';' in the WHERE conditions.
For the first answer try to see it as a string seperated by key words.
SELECT <fieldlist>
FROM <tablelist>
WHERE <conditionlist>
GROUP BY <groupdata>
ORDER BY <orderlist>
You can Split this by SQL keywords taking the last part each time i.e.
pos=instr(sqlstring," ORDER BY")
sOrder=mid(sqlstring,pos+1)
arrOrder=split(sOrder,",")
sqlstring=left(sqlstring,pos-1)
Repeat this for the other uppercase key's above. Create arrays with a split
function on the lastpart and apropiate delimiters, mostly commas to get the
basic construction.
You have now arrays with elements from the ORDER BY parts. Here you can detect
the sort order by split on space, if more than 1 in the array you have ASC or
DESC or somting wrong in it. You must check this of course.
Syntax check on parts are easy to do this way instead of scanning the whole
line char by char in a basic loop.
The section between FROM and WHERE is the most difficult one if there are JOIN
in it but this can be done the same way.
My web based is working this way. It is working with JScript, DHTML (and a M$
ActiveX Data Control from IE) , PHP and MySQL on a windows machine but needs
IE to use and not the latest security fixes.
> hi,
> I'm in a project to build a mysql front end with gambas
> just like the gambas database manager, but it will look more come to
> mysql-front in windows version. may be if successfull, i post to this
> mailing list too...
>
> now, it's time to make the multiple query.. but i don't know how the
> algortihm of it... i look for code(s) that doing some thing like that.. and
> i have it from phpmyadmin
>
> here are the code:
>
> ''''''START CODING''''''''
>
>
> FUNCTION PMA_splitSqlFile(&$ret, $sql, $release)
> {
> $sql = Trim($sql);
> $sql_len = strlen($sql);
> $char = '';
> $string_start = '';
> $in_string = FALSE;
> $time0 = Time();
>
> FOR ($i = 0; $i < $sql_len; ++$i) {
> $char = $sql[$i];
>
> // We are IN a String, check FOR NOT escaped END of strings
> except FOR
> // backquotes that can't be escaped
> IF ($in_string) {
> FOR (;;) {
> $i = strpos($sql, $string_start, $i);
> // No END of String found -> add the current substring
> TO the
> // returned Array
> IF (!$i) {
> $ret[] = $sql;
> RETURN TRUE;
> }
> // Backquotes OR no backslashes before quotes: it's
> indeed the
> // END of the String -> EXIT the LOOP
> ELSE IF ($string_start == '`' || $sql[$i-1] != '\\') {
> $string_start = '';
> $in_string = FALSE;
> BREAK;
> }
> // one OR more Backslashes before the presumed END of
> String...
> ELSE {
> // ... first checks FOR escaped backslashes
> $j = 2;
> $escaped_backslash = FALSE;
> WHILE ($i-$j > 0 && $sql[$i-$j] == '\\') {
> $escaped_backslash = !$escaped_backslash;
> $j++;
> }
> // ... if escaped backslashes: it's really the end
> of the
> // String -> EXIT the LOOP
> IF ($escaped_backslash) {
> $string_start = '';
> $in_string = FALSE;
> BREAK;
> }
> // ... else LOOP
> ELSE {
> $i++;
> }
> } // END IF...elseif...else
> } // END FOR
> } // END IF (IN String)
>
> // We are NOT IN a String, first check FOR delimiter...
> ELSE IF ($char == ';') {
> // IF delimiter found, add the parsed part TO the returned
> Array
> $ret[] = substr($sql, 0, $i);
> $sql = LTrim(substr($sql, Min($i + 1, $sql_len)));
> $sql_len = strlen($sql);
> IF ($sql_len) {
> $i = -1;
> } ELSE {
> // The submited statement(s) end(s) here
> RETURN TRUE;
> }
> } // END ELSE IF (IS delimiter)
>
> // ... then check FOR start of a String,...
> ELSE IF (($char == '"') || ($char == '\'') || ($char == '`')) {
> $in_string = TRUE;
> $string_start = $char;
> } // END ELSE IF (IS start of String)
>
> // ... for start of a comment (AND remove this comment IF
> found)...
> ELSE IF ($char == '#'
>
> || ($char == ' ' && $i > 1 && $sql[$i-2] . $sql[$i-1]
> || ==
>
> '--')) {
> // starting position of the comment depends on the comment
> type
> $start_of_comment = (($sql[$i] == '#') ? $i : $i-2);
> // IF no "\n" exits IN the remaining String, checks FOR
> "\r" // (Mac eol style)
> $end_of_comment = (strpos(' ' . $sql, "\012", $i+2))
> ? strpos(' ' . $sql, "\012", $i+2)
>
> : strpos(' ' . $sql, "\015", $i+2);
>
> IF (!$end_of_comment) {
> // no eol found after '#', add the parsed part to the
> returned
> // Array IF required AND EXIT
> IF ($start_of_comment > 0) {
> $ret[] = Trim(substr($sql, 0,
> $start_of_comment)); }
> RETURN TRUE;
> } ELSE {
> $sql = substr($sql, 0, $start_of_comment)
> . ltrim(substr($sql, $end_of_comment));
> $sql_len = strlen($sql);
> $i--;
> } // END IF...else
> } // END ELSE IF (IS comment)
>
> // ... and FINALLY disactivate the "/*!...*/" syntax IF MySQL <
> 3.22.07
> ELSE IF ($release < 32270
> && ($char == '!' && $i > 1 && $sql[$i-2] . $sql[$i-1]
> == '/*')) {
> $sql[$i] = ' ';
> } // END ELSE IF
>
> // loic1: send a fake header EACH 30 sec. to bypass browser
> timeout
> $time1 = Time();
> IF ($time1 >= $time0 + 30) {
> $time0 = $time1;
> header('X-pmaPing: Pong');
> } // END IF
> } // END FOR
>
> // add any rest TO the returned Array
> IF (!empty($sql) && ereg('[^[:space:]]+', $sql)) {
> $ret[] = $sql;
> }
>
> RETURN TRUE;
> } // END of the 'PMA_splitSqlFile()' function
>
>
>
> '''''''''' END OF CODING ''''''''''''''''''
>
> please someone help me to translate that code to gambas...
> your help is a big help that i need...
> thank you
>
> regards,
>
>
> tahara
>
>
>
>
> --__--__--
>
> _______________________________________________
> Gambas-user mailing list
> Gambas-user at lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/gambas-user
>
>
> End of Gambas-user Digest
More information about the User
mailing list