AltME groups: search

It is structures as a NoSQL-style key/value store with a SQL engine 
on top. We can put our own engine on top.

there is no other option to SQLite, other embedded databases are 
either not free, or don't use SQL syntax (e.g. mySQL)

MySQL doesn't use SQL syntax?

Kaj - last I checked it was long time ago. It did not use SQL syntax, 
and was not even free IIRC. But I don't follow mySQL for many years 
now ...

It was always a GPL SQL database, but people complained it didn't 
support as much as, say, Oracle and PostgreSQL

Kaj - e.g. do-sql errored out on not finding a library, ditto GTK 
demo ...

do-sql runs fine, 0MQ too ...

Kaj, you are assuming that people don't want to create deliberate 
bugs. If any REBOL-like language with an interpreter of JIT becomes 
popular, we are going to have to assume that some developers will 
be malicious. Remember SQL-injection attacks. Don't add a feature 
that is only better than another way of doing that feature in that 
it is so trivially easy to use as an attack vector that anyone could 
use it with the stupidest code imaginable.

Only simple functions work so far. I've added a Red version of do-sql 
to the test programs that just prints the SQLite version. See above. 
They're in */Red/do-sql

I've ported the do-sql example for SQLite from the Red/System binding 
to the Red binding:

http://red.esperconsultancy.nl/Red-SQLite/dir?ci=40dba6332ea529dc&name=examples
To be demoed at above DevCon.

You can give arbitrary SQL instructions to an arbitrary SQLite database 
from the command line

Everything around it is done by Red, such as processing the command 
line arguments, opening and closing the database, injecting the SQL 
and processing status reports

for the windows examples, should there be some indication of failure 
for something like do-sql.exe or GLUT-triangle.com?

You're both right here. Usually, I avoid duplicate names on the SQL 
side already, frequently mapping sql result column names with "as 
<columnname>" to the words I want to use in Rebol.


SELECT Customer.ID AS CustomerId, Article.ID AS ArticleId, Order.Copies 
* Article.Price AS TotalPrice ...   --->  [customer-id article-id 
total-price]

Doc, those column names are meant for binding to the result set, 
but it is not necessary to access the result set that way. You usually 
just do the positional access. If you want to use the column names, 
you have to make sure the column names are unique and that all columns 
have names, same as you have to do with all SQL.

I had the cvs file (one at a time is easiest) read/lines, parsed 
the comma's and then depending on the record with the key name = 
currentrecname being already in the table an insert or an update. 
But the update using db-update/where just gave me trouble beyond 
belief. Trying update within SQL (I am talking about Rebdb here) 
was no problem at least using 1 property at a time (had no more time 
testing). But how to do it using db-update is a mystery to me. Tried 
many things to no avail. My table I created using db-create bt [name 
additive papertype department weight weightline]   How do I update 
the record where name = ABCD and I need only to update columns weight 
and weigthline, both integer (but that's a coincidence, don't mind 
if it could be anything)?

OLEDB was never as fast as ODBC, but for maybe one version of SQL 
Server (2000, I think) it was promoted instead of ODBC. For every 
version before and since it has been better to use ODBC drivers. 
Starting with SQL 2008 they don't even make OLEDB drivers for SQL 
Server anymore.

R2 chokes on every ODBC datatype added to SQL Server since 2000, 
possibly including 2000.

Nowadays I mostly write SQL, sometimes calling that SQL using R3, 
or others call it with ASP.NET, PHP or Java (merger code collection).

OLEDB vs. ODBC; from Microsoft: "You are strongly encouraged to use 
one of the native OLE DB Providers

instead of the Microsoft Open Database Connectivity (ODBC) Provider. 
Native

OLE DB Providers provide better application stability and performance."

at least for it was for SQL Server. But as you said OLEDB is now 
obsolete..

what is exactly defiicient?  I never used sql server

A lot of the OpenDBX references to SQL Server don't seem to take 
SQL Server 2008 into account, so it's at least 3 versions behind 
now. Comparable to R2 I guess.

OpenDBX is not a full SQL interface to databases, but a database 
abstraction of its own. They're probably not interested in supporting 
other features

Knowing C, it would be better to add it to the ODBC API, at least 
for someone who already knows SQL. That way you can get wider database 
support, since most SQL database vendors support ODBC. Heck, even 
Microsoft is making a free unixodbc driver for MSSQL.

Yup. You should see what they have in store for SQL Server 2012. 
It can be run on a fully command line OS, managed remotely entirely 
through PowerShell scripting. And you can do PowerShell scripting 
of MS servers from Linux clients too, reportedly.

except that SQL actualy does return the column headers, its a pity 
R2's ODBC interface doesn't seem to keep/read it.

I used 'columns and 'tables with R2 on SQL Server, it does work well.

I may find some time to tweak the R3 ODBC-API a bit if you could 
supply a prioritized list of things that need work. Regarding problems 
with certain SQL types one easy workaround could be to just allow 
all types without direct support by rebol to read them as strings, 
you then could do anything you like withthem. Regarding "R3's documentation 
for its port model is a bit lacking" - I surely won't have any time 
to find out why the port model I used feels alien to R3 without the 
better R3 beeing documented anywhere.

That is correct. Just Databases, SQL, Status, Export, Import, Settings, 
Synchronize, Variables, Charsets, Engines.

you can connect from local and configure your server using SQL commands.
did you try TELNET to your server's 3306 port?

such as, R2 doesn't support new SQL Server datatypes. You should 
CAST & CONVERT your values in your SQL to be able to get those values 
into R2 app.

I don't know about the datatype support when connecting to Access 
2007 databases; what Endo said was related to SQL Server. However, 
I have Access 2010 here and 2007 at home so I can run some tests 
later today if you like.

R2 is more ambitious about datatype conversion than the R3 extension, 
which only handles SQL datatypes that are directly compatible with 
64bit or smaller R3 datatypes. However, R2 doesn't handle floating-point 
datatypes, and R3 does.

Weirdly enough, the datatypes that R2 doesn't support in newer SQL 
Server versions are the ones that were added to make SQL Server *more* 
standard. It supports the old, non-standard ones just fine.

Or simply I have to use more powerfull SQL solution, but heck, I 
don't like all the heave SQL servers for some simple few tables solution 
....

It's interesting when I try to connect using a port scheme I cannot 
get error msg:

>> con: open [scheme: 'odbc target: "..."] ;try with wrong password 
for example
** Script Error: ODBC error:

>> con: open odbc://sa:[qwe1234-:-mydsn]

** Script Error: ODBC error: [Microsoft][SQL Server Native Client 
10.0][SQL Server]Login failed for user 'sa'.  <<< now I got error 
msg.

Any idea how to get "real" error message?

For instance, this is a common connection I use at work (server name 
changed):

conn: [scheme: 'odbc target: "driver=SQL Server Native Client 10.0;server=SERVER1;database=db1;Trusted_Connection=Yes;"]

I've just updated the R3 ODBC Extension to work with more SQL types 
and, with the support of BrianH, fixed and improved the boot code 
OPEN and COPY actors.


The extension is available over at Github in both source form as 
well as a DLL compiled for windows, see https://github.com/gurzgri/r3-odbc

Very nice work Chris.  It would be nice to have that for MS-SQL too.

By the looks of it (I've not used MS SQL) it wouldn't be too different. 
Same for Sqlite. Just a matter of hacking http://reb4.me/r/mysql-schema
to include the most appropriate datatypes.


Of course, that's only for the 'create statement. I imagine detecting 
differences in schemas between SQL dialects might be more nuanced. 
But then I haven't got that far with any dialect of SQL, so...

You first check that the value is acceptable for what can be expected. 
This is to prevent SQL injections and other malicious input from 
hackers/innocent users and monkeys using your application. Than you 
insert a SQL command to insert or update  the mysql database just 
like you did when you did with your select statement before.

 mijnquery: "INSERT INTO cms_artikel (titel, tagregel, sectie, toegevoegd, 
 artikel_tekst) VALUES ('"

 mijnquery: append mijnquery rejoin [titel "', '" tagregel "', '" 
 desectie "', '" toegevoegd "', '" artikel-tekst "')"]
 insert db mijnquery

 The names after INTO are the fieldnames of the table cms_artikel 
 the ones after VALUES are the REBOL variables that get replaced by 
 their values

Yes. Do a probe of the query to see if it generates the sql you expected.

if so, you have to add your mysql connection parameters to your script., 
open a mysql port and do an sql insert to your table.

You are missing the actual values to insert. Put those in the block 
after the SQL string.

insert db ["insert into sql-tablename (sql-fieldname) values (?)" 
cgi-values/cgi-fieldname]

TomBon, don't encourage people to use rejoin for SQL queries. Definitely 
use parameterized queries. Building your own queries with rejoin 
is a sure recipe for SQL injection.

checking for proper values and a corerct sql syntax should be always 
done even when parameterized.

well better first to make him clear whats going up, then make the 
final.
I think he is confused by this examples.

btw, how parameterized queries preventing sql injection if not serverside?

With parameterized queries (even in REBOL) the SQL and the parameters 
are sent separately and combined in the server. The query plan is 
generated only once per query, with the parameter placeholders being 
in the plan. Then the actual parameters are plugged into the plan. 
The next time the parameterized query is called (maybe with differe3nt 
parameter values) the same plan is used and the new parameter values 
are plugged in.

isn't this execution optimation?. in this case a stored procedure 
will help also. how will this prevent from sql injection? compared 
to a concatenated server side sql string?

I've seen data front-ends that don't use parametrized queries when 
talking to SQL servers that support them. They need work.

I use the sql dialect like this:

sql [select count [ID title post date] from archive group by [ID 
title post] where [find post "t"]]


The trick with this particular query is the that the "count" selector 
must have exactly one more column than the "group by" selector.  
The first three elements [ID title post] are used to sort the output 
and the last element [date] is counted.

output will be organized:
	ID	title	post	count

SQLite version 4 announced/proposed. The default built-in storage 
engine is a log-structured merge database instead of B-tree in SQlite3. 
As far as I understand the docs This store could be usable standalone 
or use SQL frontend. Google to SQLite4.

For instance, most developers need to have support for either SQL-like 
databases, or NoSQL databases (according to many different data models), 
or both, but they are not compatible with each other even in theory 
once you get out of SQL world.

The question of what to include by default is never easy, but can 
be guided to some extent by what is not just popular, but also has 
a well-defined standard that is useful. e.g. HTTP and FTP are standards, 
No-SQL is not. SQL92 is a standard, but is a strict SQL92 dialect 
useful?

SQL is more than the language, it's a storage and execution model. 
As long as we support the storage and execution model we can let 
the SQL servers/libraries process their own scripts. Our own model 
can just translate the R3-style port model (if we decide to do this 
as a port) into SQL operations, with the ability to send SQL code 
directly as a fallback.

Doc.   my very competent db admin, told me that it could simply be 
an issue that the client is expecting a reply from the sql server 
too quickly and that it is likely a simple timeout issue.  HTH.  
 if there is a way to let the tcp wait a bit more, it could be that 
the fraction of a second more needed might be all that is required.

my current app is so threaded because of the async core that its 
impressed everyone at the office... I can be scrolling a list view, 
reading files doing sql queries and reading directory lists which 
buildup view menus in real time... everything is fluding and strangely, 
having sql and a loop browsing through several hundred directories 
scanning their content is almost unnoticeable !!!!!!    scrolling 
speed goes down by maybe 20%... but network access remains pretty 
steady... congrats to RT...

@Gabrielle: AFAIK the scroller events just  fire the SQL requests 
when the scroller is moved. So the request can overlap in time. The 
simple trick above solved the problem.

Dummy question: is it possible to insert more than one SQL statement 
in one time ?
ie:

insert mysql-db {INSERT INTO table VALUES (1, "USA"); INSERT INTO 
table VALUES (2, "FRANCE"); INSERT INTO table VALUES (3, "ITALY")}

But I can't do that for UPDATE !!

So I need to know it the Rebol MySql protocol allow to pass several 
SQL statements in one time.

Huu, I need to learn SQL more deeply !!

I think I mean 
   where prod-code in
not NOT in
(the code is not tested, and may not be possible in mySQL)

Joe Celko's SQL for Smarties is probably the best book for getting 
up to speed.

I need to learn SQL more deeply

 A broad introduction can be found here: http://riki-lb1.vet.ohio-state.edu/mqlin/computec/tutorials/SQLTutorial.htm

even being able to issue more than one sql statement would help some

insert db {insert  into tab(tab_id,tab_name) values(NULL,'alt_key'); 
select LAST_INSERT_ID()}
tab-id: copy db

but if you just need an sql engine... why not sqlite?

So if your code looks like this:
	insert port sql-query
	result: copy port
What does result contain ?

Ahh... also show me what the function looks like. Is it like this:
	func [
		/local result
	][
		insert port sql-query
		result: copy port
	]

dockimbel: I think it only returned an incorrect block after doing 
an SQL insert operation. when doing normal select queries it would 
return an empty block! where it should, but after using SQL insert, 
empty blocks contained old values

I'm getting the Error 1043 Bad Handshake problem accessing My-SQL 
5.0.18 on Windows/XP using mysql-protocol.r via  localhost: that 
Alain Goyé reported on the mailing list:

>> db: open mysql://root:?@localhost/fundamentals
Password: *********
connecting to: localhost
>> insert db "SELECT * FROM books"
** User Error: ERROR 1043 : Bad handshake
** Near: insert db "SELECT * FROM books"

It works fine from another machine over the LAN.

What do you all use to check if a port is open? Instead of getting 
a port not open when trying to read the "inserted" SQL results. RIght 
now I'm using error? try [ a: copy db] where db is the object with 
the results. I don't know, it doesn't seem to work all that well.

Good news : I've finally reached a very stable version of the mysql 
driver with v5+ servers ! The stability garanted only if using a 
database created with a v5+ server, copying old 3.x files in v5 server 
works but results in a big mess and unstable behaviour. Exporting/Importing 
v3.x data in v5 server using SQL flat files works very well (except 
for user with passwords, better recreate them in v5 directly).

ah, I got syntax error in my sql statement, sorry. Now CGI runs in 
linux console by pressing enter in midnight commander, but not from 
the browser :-)

well, it is really handy ... it is SQL, that is importand ... and 
it is completly portable - imagine having small apps, you just need 
cgi-bin site and you can have your rebol app wherever .... no need 
for admin to configure mysql for you, etc., I like it. For small 
to middle stuff it is pretty ok ...

actually i rarelly looked in any sql, as for my simple uses files 
are enough.

I've set up mediawiki at http://compkarori.com/rebolwiki/index.php/Main_Page
and will see how it goes.

Currently there's a sql error on updating or creating a page, but 
you can ignore that it seems as the page changes are made.

basically, what sql's select is for relational data, xpath is for 
xml data.

I like SAX model, because IIRC it allows to work on things in a "streamed" 
way, whereas DOM requires you load everything in memory? Sorry if 
I oversimpilifed it :-) IIRC Doc used such aproach in his Postgress 
SQL driver, in opposite to his mySQL one ...

Just wanted to say, that I needed async postge SQL protocol, so I 
used the Nenad's non-async version located here: http://rebol.softinnov.org/pgsql/
and made async my own version, which is available here: http://box.lebeda.ws/~hmm/rebol/a-pgsql.r

I have PostSQL 8.2

It seems that I can connect and send SQL commands and they are executed, 
but whenever in the SQL script/string appears word TABLE I am getting 
error:
>> insert db {CREATE TABLE my_table (
{    prod_no integer, name text, price numeric);}
** Script Error: Invalid argument: TABLE
** Where: forever
** Near: to integer! trim pos

Any workaround?
Thanks!

or let the sql server know, what encoding you are using (that it's 
not UTF8)

I've been using  pgsql-protocol (r090)  to store HTML reports into 
the DB. 


It can write a large compressed string into a text column ok. Trying 
to read the data back causes the program to hang. The length of the 
stored string returns 182514 chars. (In psql: SELECT length(html) 
FROM reports....)

Test Code;

sql: {SELECT html FROM reports.html_reports WHERE ticket = '2007-756-fcw'}
insert db-port sql
html: first db-port
.... rest of test program never gets executed.

Does anyone know if this a known behaviour? Thanks

For information, if I select any other column, the program runs as 
expected. The data is these columns is a lot smaller in length.

SQLite is not memory based imo. It was RebDB. SQLite is the coolest 
thing on earth for the purpose it is supposed to serve :-) I mean 
- small, embeddable SQL engine. MySQLLib is not good solution imo, 
as it does not use SQL syntax and is not even free for embedded stuff.

There is one defficiency with SQLite - it is one user solution. Well, 
you can lock iirc, on file level (yes, even records), but SQL requests 
are not serialised, so you have to think twice, when more than one 
client would connect to database.

i have experienced a sort of problem : i needed a native driver for 
sql server in rebol because odbc wasnt suitable.  Does this exist 
or its not possible ?

I think sql server is only odbc

do you mean MS sql server?

Galt Barber wrote an SQL -Proxy for MS-SQL7 a few years ago. Modifying 
his script I was able to snoop the connections to the Caché DB too.

There is a native non-odbc client library for ms sql that is fast. 
There is also a TCP line protocol to access it.

By the way, ODBC and OLEDB are the native access methods of MS SQL 
Server - they are not an add-on layer. The tcp line protocol is the 
same protocol that the client libraries use. I think there is a few 
open source libraries that support the tcp connections (such as FreeTDS), 
though they may not be very current. You might be able to look at 
their code and docs to figure out the protocol.

Doc,   PgSQL does not have same 'send-sql' and /flat options.   Is 
there a reason they can not parallel the mySQL use?

No particular reason, this driver wasn't much used by me or others, 
so I haven't work on it  as much as on the MySQL one. For SEND-SQL 
support with options, it should be quite easy to add IIRC (just a 
matter of copy/pasting some code from MySQL driver).

It comes on CREATE Table as well as SQL without table references 
in it ... e.g. simple SELECT

Thanks Doc.  I can work around that TABLE message using the pgAdmin. 
 The Send-SQL and /FLAT protocol updates work exactly the same as 
on MySQL for me.

Does the ado stuff work with this new beastie? http://www.microsoft.com/sql/ctp_sqleverywhere.mspx
.

A better question is whether COMlib supports OLEDB, which is COM-based. 
SQL Everywhere works with OLEDB too.

.... I wonder what they mean by new sql connection support ....

Also note that many join operations can be rewritten as sub-selects, 
as in:

	sql compose/deep [
		select * from a where [
			col < (
				sql [select max [col] from b]
			)
		]
	]

or:

	sql compose/deep/only [
		select * from a where [
			find (sql [select [col] from b]) col
		]
	]

Memory DB: even in memory if you count the overhead of a SQL layer 
+ storage layer, it would still be much slower than direct storing 
of data in a REBOL block! or hash!.

Can you make the session storage server a RebService, called from 
the various processes that need to read/write the data? That overhead 
might be less than the SQL overhead.

the only thing it needs is someone to adapt it for cgi use... I have 
too little CGI practice and no real need, atm... so its hard to put 
time on this... but its a rebol script, so its easy to adapt.  all 
it would need is to check if its been started as cgi and call a different 
startup, which only prints out one file.  for static pages (which 
CAN include dynamic conent like sql queries) then its a very good 
solution which already supports site magamenent and ftp xfer.