Xojo Developer Conference
25/27th April 2018 in Denver.
MBS Xojo Conference
6/7th September 2018 in Munich, Germany.

Re: RealDatabase Quirks (Real Studio network user group Mailinglist archive)

Back to the thread list
Previous thread: Re: Checking for string output duplicity...
Next thread: Chasing arrows don't chase


RE: official stance on Windows compatibility and is it worth it?   -   Triola, Marc
  Re: RealDatabase Quirks   -   Norman Palardy
    Re: RealDatabase Quirks   -   Norman Palardy
     Re: RealDatabase Quirks   -   Peter Savas
      Re: RealDatabase Quirks   -   Norman Palardy
     Re: RealDatabase Quirks   -   M. Bastin
      Re: RealDatabase Quirks   -   Norman Palardy
       Re: RealDatabase Quirks   -   M. Bastin
        Re: RealDatabase Quirks   -   Norman Palardy
    RealDatabase Quirks   -   Brad Bennett
     Re: RealDatabase Quirks   -   Brad Rhine
      Re: RealDatabase Quirks   -   Robert Pollard
       Re: RealDatabase Quirks   -   Joseph J. Strout
        Re: RealDatabase Quirks   -   Kevin Ballard
       Re: RealDatabase Quirks   -   Brad Rhine
     Re: RealDatabase Quirks   -   Stéphane Pinel <
     Re: RealDatabase Quirks   -   Rick Cross
     Re: RealDatabase Quirks   -   Lorin Rivers
    Re: RealDatabase Quirks   -   Brad Bennett
     Re: RealDatabase Quirks   -   Stéphane Pinel <
     Re: RealDatabase Quirks   -   Kevin Ballard
    Re: RealDatabase Quirks   -   Brad Bennett

Re: RealDatabase Quirks
Date: 01.08.03 04:26 (Thu, 31 Jul 2003 21:26 -0600)
From: Norman Palardy
Using just standard SQL will help. But there are things that you will want to do in Sybase /Oracle That are not possible in the builtin DB.
Group by... having is one
certain other issues will also come up.

---
A searchable archive of this list is available at:
<http://support.realsoftware.com/listarchives/search.php>

Unsubscribe or switch delivery mode:
<http://support.realsoftware.com/listmanager/>

Re: RealDatabase Quirks
Date: 01.08.03 04:26 (Thu, 31 Jul 2003 21:26 -0600)
From: Norman Palardy
There is no standard SQL way to specify the number of rows you want returned so it is vendor specific. The builtin RBDB does not have a way to do this.

There is no way to use variables
as bind variables Like you're asking for.

---
A searchable archive of this list is available at:
<http://support.realsoftware.com/listarchives/search.php>

Unsubscribe or switch delivery mode:
<http://support.realsoftware.com/listmanager/>

Re: RealDatabase Quirks
Date: 01.08.03 19:13 (Fri, 1 Aug 2003 12:13:18 -0600)
From: Peter Savas
> There is no standard SQL way to specify the number of rows you want
returned so it is vendor specific. The builtin RBDB does not have a way to
do this.

Norman,

Wouldn't "SELECT TOP x * FROM..." (where x = number of rows you want) handle
that?

That's what I use most of the time when I'm working with SQL Server. I was
under the impression that the TOP keyword was standard SQL.

pete
<email address removed>

-----"No matter where you go, there you are." - Buckaroo Banzai



---
A searchable archive of this list is available at:
<http://support.realsoftware.com/listarchives/search.php>

Unsubscribe or switch delivery mode:
<http://support.realsoftware.com/listmanager/>

Re: RealDatabase Quirks
Date: 02.08.03 04:49 (Fri, 01 Aug 2003 21:49:15 -0600)
From: Norman Palardy
I don't believe it is part of SQL 92 or SQL 98.
That's not to say vendors don't have extensions to these standards.
Oracle, Sybase and others certainly do.
That will not work in Sybase Adaptive Server & Openbase and I also
expect a variety of others.

On Friday, August 1, 2003, at 12:13 PM, Peter Savas wrote:

>> There is no standard SQL way to specify the number of rows you want
> returned so it is vendor specific. The builtin RBDB does not have a
> way to
> do this.
>
> Norman,
>
> Wouldn't "SELECT TOP x * FROM..." (where x = number of rows you want)
> handle
> that?
>
> That's what I use most of the time when I'm working with SQL Server.
> I was
> under the impression that the TOP keyword was standard SQL.
>
> pete
> <email address removed>
> -----"No matter where you go, there you are." - Buckaroo Banzai
>
> ---
> A searchable archive of this list is available at:
> <http://support.realsoftware.com/listarchives/search.php>
> Unsubscribe or switch delivery mode:
> <http://support.realsoftware.com/listmanager/>

---
A searchable archive of this list is available at:
<http://support.realsoftware.com/listarchives/search.php>

Unsubscribe or switch delivery mode:
<http://support.realsoftware.com/listmanager/>

Re: RealDatabase Quirks
Date: 01.08.03 20:03 (Fri, 1 Aug 2003 21:03:34 +0200)
From: M. Bastin
> > There is no standard SQL way to specify the number of rows you want
>returned

That's not exact. The SQL92 standard describes the FETCH command
which allows you to do that.

For example fetching rows 25 to 50 from a query which would otherwise
return 76 rows in total.

Marc

---
A searchable archive of this list is available at:
<http://support.realsoftware.com/listarchives/search.php>

Unsubscribe or switch delivery mode:
<http://support.realsoftware.com/listmanager/>

Re: RealDatabase Quirks
Date: 02.08.03 04:51 (Fri, 01 Aug 2003 21:51:16 -0600)
From: Norman Palardy
Assuming you have selected things into a cursor which is the only thing
a FETCH can be applied to.
There's no standard SQL way to say "select rows 10 through 15 from
these tables where ....."

On Friday, August 1, 2003, at 01:03 PM, M. Bastin wrote:

>> > There is no standard SQL way to specify the number of rows you want
>> returned
>
> That's not exact. The SQL92 standard describes the FETCH command
> which allows you to do that.
>
> For example fetching rows 25 to 50 from a query which would otherwise
> return 76 rows in total.
>
> Marc
>
> ---
> A searchable archive of this list is available at:
> <http://support.realsoftware.com/listarchives/search.php>
> Unsubscribe or switch delivery mode:
> <http://support.realsoftware.com/listmanager/>

---
A searchable archive of this list is available at:
<http://support.realsoftware.com/listarchives/search.php>

Unsubscribe or switch delivery mode:
<http://support.realsoftware.com/listmanager/>

Re: RealDatabase Quirks
Date: 02.08.03 15:34 (Sat, 2 Aug 2003 16:34:01 +0200)
From: M. Bastin
>Assuming you have selected things into a cursor which is the only
>thing a FETCH can be applied to.
>There's no standard SQL way to say "select rows 10 through 15 from
>these tables where ....."

OK, we might have misunderstood each other. There is no single
command, but there is a method to do it.

Here's how in SQL92 (to the best of my knowledge, I don't have the
standard's docs, nor a compliant db to test it against.):

BEGIN WORK;
DECLARE mycursor CURSOR FOR SELECT * FROM mytable;
FETCH ABSOLUTE 10 FROM mycursor;
FETCH NEXT FROM mycursor;
//write above line 5 times in total.
CLOSE mycursor;
COMMIT WORK;

This returns rows 10 to 15, row by row. Your db must of course
support this part of SQL92, which isn't likely to be the case.

(PostgreSQL, which I'm using, allows you to fetch a range of records
in one command, but doesn't support "ABSOLUTE" -- that's not ABSOLUT,
the vodka :)

Marc

>
>On Friday, August 1, 2003, at 01:03 PM, M. Bastin wrote:
>
>>> > There is no standard SQL way to specify the number of rows you want
>>>returned
>>
>>That's not exact. The SQL92 standard describes the FETCH command
>>which allows you to do that.
>>
>>For example fetching rows 25 to 50 from a query which would
>>otherwise return 76 rows in total.
>>
>>Marc

Re: RealDatabase Quirks
Date: 02.08.03 18:10 (Sat, 02 Aug 2003 11:10:25 -0600)
From: Norman Palardy
You are quite right (and I agreed with you) that IF the database
supports SQL 92 then there is SUPPOSED to be a way to do this.

However, the RBDB is not SQL 92, Sybase only lets you declare cursors
in a stored procedure, Oracle I'm not up on since version 8 but it
would not let you do this then, and Openbase does not let you do it
this way either.

Each vendor seems to have a different way of doing this standard thing.

So much for standards.

On Saturday, August 2, 2003, at 08:34 AM, M. Bastin wrote:

> Assuming you have selected things into a cursor which is the only
> thing a FETCH can be applied to.
> There's no standard SQL way to say "select rows 10 through 15 from
> these tables where ....."
>
> OK, we might have misunderstood each other.  There is no single
> command, but there is a method to do it.
>
> Here's how in SQL92 (to the best of my knowledge, I don't have the
> standard's docs, nor a compliant db to test it against.):
>
> BEGIN WORK;
> DECLARE mycursor CURSOR FOR SELECT * FROM mytable;
> FETCH ABSOLUTE 10 FROM mycursor;
> FETCH NEXT FROM mycursor; 
>    //write above line 5 times in total.
> CLOSE mycursor;
> COMMIT WORK;
>
> This returns rows 10 to 15, row by row.  Your db must of course
> support this part of SQL92, which isn't likely to be the case.
>
> (PostgreSQL, which I'm using, allows you to fetch a range of records
> in one command, but doesn't support "ABSOLUTE" -- that's not ABSOLUT,
> the vodka :)
>
> Marc
>
> On Friday, August 1, 2003, at 01:03  PM, M. Bastin wrote:
>
>  > There is no standard SQL way to specify the number of rows you want
> returned
>
> That's not exact.  The SQL92 standard describes the FETCH command
> which allows you to do that.
>
> For example fetching rows 25 to 50 from a query which would otherwise
> return 76 rows in total.
>
> Marc
>

---
A searchable archive of this list is available at:
<http://support.realsoftware.com/listarchives/search.php>

Unsubscribe or switch delivery mode:
<http://support.realsoftware.com/listmanager/>

RealDatabase Quirks
Date: 31.07.03 14:46 (Thu, 31 Jul 2003 09:46:35 -0400)
From: Brad Bennett

I am new to programming with Real Basic, and have a few simple questions
regarding use of the RealDatabase. (tried to query the listserver archive,
but it does not appear to work).

1. How can you get the number of records returned by a SELECT query? I
note that the documentation states that the RecordCount parameter is not set
for RealDatabase queries (or rather this parameter is only set for a few
other types of Databases, but not the RealDatabase). Seems a bit odd.

2. You can INSERT data into the RealDatabase with a SQL command when the
data is directly contained within the SQL statement, e.g.:

dbNew.SQLExecute("INSERT INTO general
(max_layers,winX,winY,winToolX,winToolY) VALUES (1,0,20,20,40)")

but it does NOT seem that you can INSERT data into the RealDatabase with the
INSERT SQL statement when data is contained within declared variables, e.g.:

dbNew.SQLExecute("INSERT INTO general
(max_layers,winX,winY,winToolX,winToolY) VALUES
(my_max_layers,my_winX,my_winY, my_winToolX, my_winToolY)")

Is this correct?

Rather, you must use several different statements to individually assign
values to a new record, then add the record to the RealDatabase.

thanks for any advice.

regards,
--- Brad Bennett

---
A searchable archive of this list is available at:
<http://support.realsoftware.com/listarchives/search.php>

Unsubscribe or switch delivery mode:
<http://support.realsoftware.com/listmanager/>

Re: RealDatabase Quirks
Date: 31.07.03 15:11 (Thu, 31 Jul 2003 10:11:51 -0400)
From: Brad Rhine
You can use the SQL statement with variables, but you have to build the
string yourself. For example:

dim sql as string
sql="insert into general (max_layers,winX,winY,winToolX,winToolY) "
sql=sql+"values("
sql=sql+str(max_layers)+", "
sql=sql+str(winX)+", "
//etc...
dbNew.SQLExecute(sql)

On Thursday, July 31, 2003, at 09:46 AM, Brad Bennett wrote:

> 2. You can INSERT data into the RealDatabase with a SQL command when
> the
> data is directly contained within the SQL statement, e.g.:
>
> dbNew.SQLExecute("INSERT INTO general
> (max_layers,winX,winY,winToolX,winToolY) VALUES (1,0,20,20,40)")
>
> but it does NOT seem that you can INSERT data into the RealDatabase
> with the
> INSERT SQL statement when data is contained within declared variables,
> e.g.:
>
> dbNew.SQLExecute("INSERT INTO general
> (max_layers,winX,winY,winToolX,winToolY) VALUES
> (my_max_layers,my_winX,my_winY, my_winToolX, my_winToolY)")
>
> Is this correct?
>
> Rather, you must use several different statements to individually
> assign
> values to a new record, then add the record to the RealDatabase.

Re: RealDatabase Quirks
Date: 31.07.03 18:00 (Thu, 31 Jul 2003 10:00:06 -0700)
From: Robert Pollard
Pardon my ignorance but assuming REALDatabase can store binary values
how do you insert values that cannot be translated to literals? I
guess the better question would be are there any data types in
REALDatabase that cannot be inserted literally?

In other environments you bind a variable to a column and as long as
they are translatable types there are no problems.

Just curious. I am about to start development a database front end
with REALBasic and using Sybase/Oracle as the backend. I would
probably start with REALDatabase and switch later. This may be
important to me since I don't want to build a foundation on something
that will be incompatible when I switch to Sybase or Oracle.

Thanks,

Robert Pollard

On Thursday, July 31, 2003, at 07:11 AM, Brad Rhine wrote:

> You can use the SQL statement with variables, but you have to build
> the string yourself. For example:
>
> dim sql as string
> sql="insert into general (max_layers,winX,winY,winToolX,winToolY) "
> sql=sql+"values("
> sql=sql+str(max_layers)+", "
> sql=sql+str(winX)+", "
> //etc...
> dbNew.SQLExecute(sql)
>
> On Thursday, July 31, 2003, at 09:46 AM, Brad Bennett wrote:
>
>> 2. You can INSERT data into the RealDatabase with a SQL command when
>> the
>> data is directly contained within the SQL statement, e.g.:
>>
>> dbNew.SQLExecute("INSERT INTO general
>> (max_layers,winX,winY,winToolX,winToolY) VALUES (1,0,20,20,40)")
>>
>> but it does NOT seem that you can INSERT data into the RealDatabase
>> with the
>> INSERT SQL statement when data is contained within declared
>> variables, e.g.:
>>
>> dbNew.SQLExecute("INSERT INTO general
>> (max_layers,winX,winY,winToolX,winToolY) VALUES
>> (my_max_layers,my_winX,my_winY, my_winToolX, my_winToolY)")
>>
>> Is this correct?
>>
>> Rather, you must use several different statements to individually
>> assign
>> values to a new record, then add the record to the RealDatabase.
>
> --
> <email address removed>
> http://truetech.org
> <><
>
> ---
> A searchable archive of this list is available at:
> <http://support.realsoftware.com/listarchives/search.php>
> Unsubscribe or switch delivery mode:
> <http://support.realsoftware.com/listmanager/>

---
A searchable archive of this list is available at:
<http://support.realsoftware.com/listarchives/search.php>

Unsubscribe or switch delivery mode:
<http://support.realsoftware.com/listmanager/>

Re: RealDatabase Quirks
Date: 31.07.03 18:35 (Thu, 31 Jul 2003 10:35:24 -0700)
From: Joseph J. Strout
At 10:00 AM -0700 7/31/03, Robert Pollard wrote:

>Pardon my ignorance but assuming REALDatabase can store binary
>values how do you insert values that cannot be translated to
>literals? I guess the better question would be are there any data
>types in REALDatabase that cannot be inserted literally?

That's a good question. I think SQL can represent any data in some
way, but that's certainly not always the easiest way to do it.

> In other environments you bind a variable to a column and as long
>as they are translatable types there are no problems.

Yes, you'd probably do that in RB too if your data is hard to
represent as text. Just fill out a DatabaseCursor, then call
Database.InsertRecord.

Best,
- Joe

Re: RealDatabase Quirks
Date: 31.07.03 19:30 (Thu, 31 Jul 2003 13:30:58 -0500)
From: Kevin Ballard
DatabaseCursor is deprecated. Use RecordSet instead.

On Thursday, July 31, 2003, at 12:35 PM, Joseph J. Strout wrote:

> Yes, you'd probably do that in RB too if your data is hard to
> represent as text. Just fill out a DatabaseCursor, then call
> Database.InsertRecord.

Re: RealDatabase Quirks
Date: 31.07.03 18:42 (Thu, 31 Jul 2003 13:42:08 -0400)
From: Brad Rhine
On Thursday, July 31, 2003, at 01:00 PM, Robert Pollard wrote:

> Pardon my ignorance but assuming REALDatabase can store binary values
> how do you insert values that cannot be translated to literals? I
> guess the better question would be are there any data types in
> REALDatabase that cannot be inserted literally?

I've had to store any binary data yet, but I imagine I would then use
the DatabaseRecord class instead.

Re: RealDatabase Quirks
Date: 31.07.03 15:16 (Thu, 31 Jul 2003 16:16:29 +0200)
From: Stéphane Pinel <

Le jeudi, 31 jul 2003, à 15:46 Europe/Paris, Brad Bennett a écrit :

>
> I am new to programming with Real Basic, and have a few simple
> questions
> regarding use of the RealDatabase. (tried to query the listserver
> archive,
> but it does not appear to work).
>
> 1. How can you get the number of records returned by a SELECT query?
> I
> note that the documentation states that the RecordCount parameter is
> not set
> for RealDatabase queries (or rather this parameter is only set for a
> few
> other types of Databases, but not the RealDatabase). Seems a bit odd.
>
> 2. You can INSERT data into the RealDatabase with a SQL command when
> the
> data is directly contained within the SQL statement, e.g.:
>
> dbNew.SQLExecute("INSERT INTO general
> (max_layers,winX,winY,winToolX,winToolY) VALUES (1,0,20,20,40)")
>
> but it does NOT seem that you can INSERT data into the RealDatabase
> with the
> INSERT SQL statement when data is contained within declared variables,
> e.g.:
>
> dbNew.SQLExecute("INSERT INTO general
> (max_layers,winX,winY,winToolX,winToolY) VALUES
> (my_max_layers,my_winX,my_winY, my_winToolX, my_winToolY)")
>
Not really :

query = "INSERT INTO general (max_layers, winX, winY, winToolX,
winToolY) VALUES .../...
("+ str(my_max_layers)+", "+str(my_winX)+", "+ str(my_winY)+", "+
str(my_winToolX)+", "+str(my_winToolY)+")"

dbNew.SQLExecute(query)

This should works.

Regards.

------------------------------------------------------------------------
---
Stéphane Pinel
Information System
Equinox Partners Paris
iChat/AIM: s.pinel
------------------------------------------------------------------------
---

---
A searchable archive of this list is available at:
<http://support.realsoftware.com/listarchives/search.php>

Unsubscribe or switch delivery mode:
<http://support.realsoftware.com/listmanager/>

Re: RealDatabase Quirks
Date: 31.07.03 17:52 (Thu, 31 Jul 2003 09:52:52 -0700)
From: Rick Cross
At 06:46 AM 7/31/2003, you wrote:
>1. How can you get the number of records returned by a SELECT query? I

Do it using two queries. The first using the count() function which will
give you the number of records that matched, the second query the one that
you originally intended to run.

dim sqlprefix1 as string
dim sqlprefix2 as string
dim sqlq as string
dim result as recordset
dim matchingrecords as integer
' Assume that the database is called "database" and holds a table called
mytable containing at least one field called fname

sqlprefix1 = "select count(*) "
sqlprefix2 = "select * "
sqlq = "from mytable where fname like 'jo%'"

' Query the database with the query to find all first names starting with
"jo", but instead of asking for the records, we use the count() function to
simple tell us how many records matched. We then assign that value to the
matchingrecords variable.

result = database.sqlselect(sqlprefix1+sqlq)
matchingrecords = result.idxfield(1).value

' Now do the real query knowing in advance how many records we're going to
get back.
result = database.sqlselect(sqlprefix2+sqlq)

>2. You can INSERT data into the RealDatabase with a SQL command when the
>data is directly contained within the SQL statement, e.g.:
>
>dbNew.SQLExecute("INSERT INTO general
>(max_layers,winX,winY,winToolX,winToolY) VALUES
>(my_max_layers,my_winX,my_winY, my_winToolX, my_winToolY)")

Your SQLExecute is passing a string. If you want variables in your string,
you'll have to build the string like you would for pretty much any other
purpose.

Yes, you could build a recordset and insert it, or you can build the
string. I.E. (using your example)

dim sqlq as string

sqlq = "INSERT INTO general (max_layers,winX,winY,winToolX,winToolY) VALUES "
sqlq = sqlq + "(" + my_max_layers + "," + my_winX + "," + my_winY + "," +
my_winToolX + "," + my_winToolY + ")"

dbNew.SQLExecute(sqlq)

---
A searchable archive of this list is available at:
<http://support.realsoftware.com/listarchives/search.php>

Unsubscribe or switch delivery mode:
<http://support.realsoftware.com/listmanager/>

Re: RealDatabase Quirks
Date: 31.07.03 18:39 (Thu, 31 Jul 2003 12:39:54 -0500)
From: Lorin Rivers
Make sure to keep the database closed except when you are actively
reading or writing data to it. It's counter-intuitive, but it
dramatically reduces the risk of database corruption...

Re: RealDatabase Quirks
Date: 31.07.03 15:37 (Thu, 31 Jul 2003 10:37:49 -0400)
From: Brad Bennett
Thanks for the feedback Brad and Stéphane. This definitely clarifies the
way a SQL INSERT statement must be constructed with the RealDatabase (a bit
different than my Perl programming experience).

Any advice on how to get the number of records returned from a SQL SELECT
statement when using the RealDatabase?

thanks again,
--- Brad Bennett

----------
>From: Brad Rhine <<email address removed>>
>To: "REALbasic Network Users Group" <<email address removed>>
>Subject: Re: RealDatabase Quirks
>Date: Thu, Jul 31, 2003, 10:11 AM
>

> You can use the SQL statement with variables, but you have to build the
> string yourself. For example:
>
> dim sql as string
> sql="insert into general (max_layers,winX,winY,winToolX,winToolY) "
> sql=sql+"values("
> sql=sql+str(max_layers)+", "
> sql=sql+str(winX)+", "
> //etc...
> dbNew.SQLExecute(sql)
>
> On Thursday, July 31, 2003, at 09:46 AM, Brad Bennett wrote:
>
>> 2. You can INSERT data into the RealDatabase with a SQL command when
>> the
>> data is directly contained within the SQL statement, e.g.:
>>
>> dbNew.SQLExecute("INSERT INTO general
>> (max_layers,winX,winY,winToolX,winToolY) VALUES (1,0,20,20,40)")
>>
>> but it does NOT seem that you can INSERT data into the RealDatabase
>> with the
>> INSERT SQL statement when data is contained within declared variables,
>> e.g.:
>>
>> dbNew.SQLExecute("INSERT INTO general
>> (max_layers,winX,winY,winToolX,winToolY) VALUES
>> (my_max_layers,my_winX,my_winY, my_winToolX, my_winToolY)")
>>
>> Is this correct?
>>
>> Rather, you must use several different statements to individually
>> assign
>> values to a new record, then add the record to the RealDatabase.
>
> --
> <email address removed>
> http://truetech.org
> <><
>
> ---
> A searchable archive of this list is available at:
> <http://support.realsoftware.com/listarchives/search.php>
> Unsubscribe or switch delivery mode:
> <http://support.realsoftware.com/listmanager/>

---
A searchable archive of this list is available at:
<http://support.realsoftware.com/listarchives/search.php>

Unsubscribe or switch delivery mode:
<http://support.realsoftware.com/listmanager/>

Re: RealDatabase Quirks
Date: 31.07.03 15:50 (Thu, 31 Jul 2003 16:50:27 +0200)
From: Stéphane Pinel <

Le jeudi, 31 jul 2003, à 16:37 Europe/Paris, Brad Bennett a écrit :

> Any advice on how to get the number of records returned from a SQL
> SELECT
> statement when using the RealDatabase?
>
No way (I think)...

Just increment an integer variable in the While loop ;-)

Regards.

------------------------------------------------------------------------
---
Stéphane Pinel
Information System
Equinox Partners Paris
iChat/AIM: s.pinel
------------------------------------------------------------------------
---

---
A searchable archive of this list is available at:
<http://support.realsoftware.com/listarchives/search.php>

Unsubscribe or switch delivery mode:
<http://support.realsoftware.com/listmanager/>

Re: RealDatabase Quirks
Date: 31.07.03 19:31 (Thu, 31 Jul 2003 13:31:36 -0500)
From: Kevin Ballard
It works in Perl, AIUI, because Perl translates the variable names in
the string and replaces them with the variable values before passing
the string to the database.

On Thursday, July 31, 2003, at 09:37 AM, Brad Bennett wrote:

> Thanks for the feedback Brad and Stéphane. This definitely clarifies
> the
> way a SQL INSERT statement must be constructed with the RealDatabase
> (a bit
> different than my Perl programming experience).

-

Re: RealDatabase Quirks
Date: 31.07.03 19:34 (Thu, 31 Jul 2003 14:34:54 -0400)
From: Brad Bennett
You hit the nail on the head...while the RealDatabase does support some form
of the SQL command set, how these commands need to be formatted (as string
commands) for SQL INSERT statements does not seem to make it easy to port to
other databases.

It appears that if the data cannot be converted to a string literal, then
this data must be assigned to individual record fields, then an entire
record must be added to the DB table. Hence, the one single SQL INSERT
statement becomes several record assignment statements, followed by the
InsertRecord command. See the "Database Record" class for more info.

I can see where this could cause some problems when first developing code
for the internal RealDatabase, then wanting to use another database.

--- Brad

----------
>From: Robert Pollard <<email address removed>>
>To: REALbasic Network Users Group <<email address removed>>
>Subject: Re: RealDatabase Quirks
>Date: Thu, Jul 31, 2003, 1:00 PM
>

> Pardon my ignorance but assuming REALDatabase can store binary values
> how do you insert values that cannot be translated to literals? I
> guess the better question would be are there any data types in
> REALDatabase that cannot be inserted literally?
>
> In other environments you bind a variable to a column and as long as
> they are translatable types there are no problems.
>
> Just curious. I am about to start development a database front end
> with REALBasic and using Sybase/Oracle as the backend. I would
> probably start with REALDatabase and switch later. This may be
> important to me since I don't want to build a foundation on something
> that will be incompatible when I switch to Sybase or Oracle.
>
> Thanks,
>
> Robert Pollard
>
> On Thursday, July 31, 2003, at 07:11 AM, Brad Rhine wrote:
>
>> You can use the SQL statement with variables, but you have to build
>> the string yourself. For example:
>>
>> dim sql as string
>> sql="insert into general (max_layers,winX,winY,winToolX,winToolY) "
>> sql=sql+"values("
>> sql=sql+str(max_layers)+", "
>> sql=sql+str(winX)+", "
>> //etc...
>> dbNew.SQLExecute(sql)
>>
>> On Thursday, July 31, 2003, at 09:46 AM, Brad Bennett wrote:
>>
>>> 2. You can INSERT data into the RealDatabase with a SQL command when
>>> the
>>> data is directly contained within the SQL statement, e.g.:
>>>
>>> dbNew.SQLExecute("INSERT INTO general
>>> (max_layers,winX,winY,winToolX,winToolY) VALUES (1,0,20,20,40)")
>>>
>>> but it does NOT seem that you can INSERT data into the RealDatabase
>>> with the
>>> INSERT SQL statement when data is contained within declared
>>> variables, e.g.:
>>>
>>> dbNew.SQLExecute("INSERT INTO general
>>> (max_layers,winX,winY,winToolX,winToolY) VALUES
>>> (my_max_layers,my_winX,my_winY, my_winToolX, my_winToolY)")
>>>
>>> Is this correct?
>>>
>>> Rather, you must use several different statements to individually
>>> assign
>>> values to a new record, then add the record to the RealDatabase.
>>
>> --
>> <email address removed>
>> http://truetech.org
>> <><
>>
>> ---
>> A searchable archive of this list is available at:
>> <http://support.realsoftware.com/listarchives/search.php>
>>
>> Unsubscribe or switch delivery mode:
>> <http://support.realsoftware.com/listmanager/>
>>
> ---
> A searchable archive of this list is available at:
> <http://support.realsoftware.com/listarchives/search.php>
> Unsubscribe or switch delivery mode:
> <http://support.realsoftware.com/listmanager/>

---
A searchable archive of this list is available at:
<http://support.realsoftware.com/listarchives/search.php>

Unsubscribe or switch delivery mode:
<http://support.realsoftware.com/listmanager/>