SQLite new table help

[jobyid]

Hi I am new to rev and SQL so please excuse my lack of knowledge.

what i have done is created a developmet card for my application which has a group of buttons on it which I can use to create new tables, edit tables, etc.

for the button to create a table i have put in a group of ask features which as me to input the table names, column names etc as I go along. The idea being that the table is the created using the names I want and I can use the same button to create all my tables as and when I need them.
The trouble I am getting is that the table it creates when I use the button dose not use the names I enter.
here is the code i have written for the button can anyone see where I am going wrong?

on mouseUp
global mydbid
local tSQL, tResult, tablename, col1, col2, col3,col4,col5,col6,col7,col8
ask "What is the table called?"
put it into tablename
ask "Name col 1"
put it into col1
ask "name col2"
put it into col2
ask "name col3"
put it into col3
ask "name col4"
put it into col4
ask "name col5"
put it into col5
ask "name col6"
put it into col6
ask "name col7"
put it into col7
ask "name col8"
put it into col8
answer "are tablename, col1, col2, col3, col4, col5, col6, col7, col8 correct?" with "yes" or "no"
if it is not true then
answer warning "wrong information"
else
answer information "lets go"
end if
put revOpenDatabase("SQLite",localhost,,,) into mydbid
if mydbid is not an integer then
answer warning "problem creating or accessing database! Please go back 1 step and connect to the database!"
else
answer information "localhost connected! Your conection ID is: " & mydbid
end if
put "CREATE TABLE tablename(col1,col2,col3,col4,col5,col6,col7,col8)" into tSQL
revExecuteSQL mydbid, tsql
put the result into tResult
if tResult is not empty then
answer warning tResult
end if
answer information "Number of Tables Added: " & tResult
end mouseUp

Any help would be greatly appreciated.

Thanks

Joby

[Klaus]

Hi joybid,

I have no idea of databases , but I can see two possible syntax problems:

1.:

Code: Select all

...
answer "are tablename, col1, col2, col3, col4, col5, col6, col7, col8 correct?" with "yes" or "no" 
if it is "no" then 
  answer warning "wrong information"
  ## you probably want to exit here! 
   exit mouseup
 else 
   answer information "lets go" 
end if 
...

2.:
You have to build the correct string here, Rev will otherwise think you really want to name your tables
col1 to col8!

Code: Select all

...
put "CREATE TABLE tablename(" & col1 & "," & col2 & "," & col3 & "," & col4 & "," & col5 & "," & col6 & "," &col7 & "," &col8 & ")" into tSQL
...

But maybe SQL need single quote ' around the table names?
Like ... & "'" & col1 & "','" & col2 ...

Know what I mean?

Hope that helps.


Best from germany

Klaus

[jobyid]

Klaus
That worked perfectly thanks for your help
Joby

[Janschenkel]

While you got it running with Klaus' excellent advice, I'd like to point out a few things:

1. SQLite is a single-user database driver that doesn't require the installation of a database server. So if you pass 'localhost' as the second parameter to the 'revOpenDatabase' function, it's not going to have the desired effect - what you've got now is an SQLite database file named 'localhost' sitting in whatever happens to be the 'defaultFolder' at the time.

2. SQLite treats all columns as strings (or CHAR/VARCHAR columns, technically speaking) so you can get away with not setting thge column types, but that's not going to work with other databases, and affects any indixes that you define afterwards.

3. Speaking of indexes, if you don't designate a primary key, it will assume that the combination of all column data will be unique - you can't have two records in your database table that have the exact same data.

Let me assure you that I'm not trying to be condescendent - I like it when I see more people using Revolution as database front-ends. But as someone who has been using databases for quite a while, I'd advise that you digest the information avaiable in this W3Schools.com tutorial and get a few good books on relational databases and the SQL language.

For a through explanation of relational databases, I'd recommend Database Processing: Fundamentals, Design, and Implementation by David Kroenke (not cheap, but the content is excellent)

And for learning SQL, I can recommend The Programmer's Guide to SQL by Darie and Watson (covers multiple databases systems as well, but is a little older) or Beginning SQL Queries: From Novice to Professional by Clare Churcher.

Jut in case you really want to go all the way, once you're pretty good with SQL, you might want to move on to The Art of SQL by Faroult and Robson (aimed at advanced developers who want to get the most out of their databases)

Hope this helped,

Jan Schenkel.

[jobyid]

Jan

That is very helpful I will have to work through these and try and learn a good basis in SQL as I am having trouble with all my SQL stuff.

Thanks for the links.

As a quick question when you use rev as a front end to your database do you create the database with rev or create it all then add rev as the front end.

Thanks

joby

[Janschenkel]

Hi Joby,

When I create a database, I tend to build the schema by means of an SQL script that I've created manually. Though if the database comes with a decent modeling tool (MySQL Query Browser is pretty good), I might very well use that

One other thing that Inoticed: you've defined a global 'MyDBID' in which you store the database connection ID. Good practice, but you're opening a new connection every time you click the button, and not closing it afterwards.
Depending on the database driver, your connection may or may not be 'pooled' (meaning that it doesn't attempt to open a new connection to a database that you're already connected to, and that you actually get the existing connection back) but if you're not careful, you may end up with a dozen open connections to the same database.
See the doc entry for the 'revOpenDatabases' function and the 'revCloseDatabase' command. And once you start using the 'revQueryDatabase' or 'revQueryDatabaseBLOB' functions, which return a result set ID, you should also clean those up with 'revCloseCursor' command.

Revolution comes with an example stack 'SQLite Sampler', which you can find in the Revolution installation folder > Resources > Examples > SQLite Sampler.rev
And there is also a database tutorial created at RunRev HQ, that you can download here.

Glad to see you're persevering,

Jan Schenkel.