Hi Paolo,
these patches depend on the CObject patch you were creating earlier. My
problem is that I want to do queries with parameters that are not controlled
by me. I started to implement a Statement class (but without them being
prepared statements).
So the code is a bit better than before but still inferior to the SQLite
support. This code lacks prepared statements, lacks better binding support.
The last patch attempts to fix a type conversion of a NULL value.
regards
holger
>From 5960104e23f6ad1b2eeb5ee7f44da83a047791f9 Mon Sep 17 00:00:00 2001
From: Holger Hans Peter Freyther <[email protected]>
Date: Fri, 8 Apr 2011 11:06:52 +0200
Subject: [PATCH 1/4] dbi: Refactor and move methods to DBI.Statement
Move code from DBI.SQLite.Statement to DBI.Statement so it can
be used by a new DBI.PostgreSQL.Statement class as well.
---
packages/dbd-sqlite/ChangeLog | 4 ++++
packages/dbd-sqlite/Statement.st | 9 +--------
packages/dbi/ChangeLog | 4 ++++
packages/dbi/Statement.st | 13 ++++++++++++-
4 files changed, 21 insertions(+), 9 deletions(-)
diff --git a/packages/dbd-sqlite/ChangeLog b/packages/dbd-sqlite/ChangeLog
index 1ee4723..31efe6c 100644
--- a/packages/dbd-sqlite/ChangeLog
+++ b/packages/dbd-sqlite/ChangeLog
@@ -1,3 +1,7 @@
+2011-04-08 Holger Peter Freyther <[email protected]>
+
+ * Statement.st: Refactor code and use DBI.Statement class>>#getCommand:.
+
2010-12-04 Paolo Bonzini <[email protected]>
* package.xml: Remove now superfluous <file> tags.
diff --git a/packages/dbd-sqlite/Statement.st b/packages/dbd-sqlite/Statement.st
index 2f455fc..68cecda 100644
--- a/packages/dbd-sqlite/Statement.st
+++ b/packages/dbd-sqlite/Statement.st
@@ -94,13 +94,6 @@ Statement subclass: SQLiteStatement [
getCommand [
<category: 'private'>
- | readStream writeStream aCharacter |
- writeStream := WriteStream on: String new.
- readStream := ReadStream on: queryString.
- readStream skipSeparators.
- [readStream atEnd
- or: [aCharacter := readStream next. aCharacter isSeparator]]
- whileFalse: [writeStream nextPut: aCharacter asUppercase].
- ^writeStream contents
+ ^ self class getCommand: queryString.
]
]
diff --git a/packages/dbi/ChangeLog b/packages/dbi/ChangeLog
index 024e8fb..67b1647 100644
--- a/packages/dbi/ChangeLog
+++ b/packages/dbi/ChangeLog
@@ -1,3 +1,7 @@
+2011-04-08 Holger Hans Peter Freyther <[email protected]>
+
+ * Statement.st: Add Statement class>>#getCommand:.
+
2010-12-04 Paolo Bonzini <[email protected]>
* package.xml: Remove now superfluous <file> tags.
diff --git a/packages/dbi/Statement.st b/packages/dbi/Statement.st
index ed216b7..e11a57b 100644
--- a/packages/dbi/Statement.st
+++ b/packages/dbi/Statement.st
@@ -8,7 +8,7 @@
"======================================================================
|
| Copyright 2006 Mike Anderson
-| Copyright 2007, 2008 Free Software Foundation, Inc.
+| Copyright 2007, 2008, 2011 Free Software Foundation, Inc.
|
| Written by Mike Anderson
|
@@ -49,6 +49,17 @@ Object subclass: Statement [
yourself
]
+ Statement class >> getCommand: queryString [
+ | readStream writeStream aCharacter |
+ writeStream := WriteStream on: String new.
+ readStream := ReadStream on: queryString.
+ readStream skipSeparators.
+ [readStream atEnd
+ or: [aCharacter := readStream next. aCharacter isSeparator]]
+ whileFalse: [writeStream nextPut: aCharacter asUppercase].
+ ^writeStream contents
+ ]
+
connection [
"Return the connection for which the statement was prepared."
--
1.7.4
>From cdafb34a2518651763b3c213bc126a41ebb24edd Mon Sep 17 00:00:00 2001
From: Holger Hans Peter Freyther <[email protected]>
Date: Fri, 8 Apr 2011 11:09:58 +0200
Subject: [PATCH 2/4] dbd-postgres: Prepare to have prepared statements in postgres
Like the SQLite driver implement Connection>>#do: and #select:
on top of the Connection>>#prepare: method. There is no functional
change right no.
---
packages/dbd-postgresql/ChangeLog | 4 ++
packages/dbd-postgresql/Connection.st | 24 ++++------
packages/dbd-postgresql/Makefile.frag | 2 +-
packages/dbd-postgresql/Statement.st | 79 +++++++++++++++++++++++++++++++++
packages/dbd-postgresql/package.xml | 1 +
5 files changed, 95 insertions(+), 15 deletions(-)
create mode 100644 packages/dbd-postgresql/Statement.st
diff --git a/packages/dbd-postgresql/ChangeLog b/packages/dbd-postgresql/ChangeLog
index 18a1e97..ea768b5 100644
--- a/packages/dbd-postgresql/ChangeLog
+++ b/packages/dbd-postgresql/ChangeLog
@@ -1,3 +1,7 @@
+2011-04-08 Holger Hans Peter Freyther <[email protected]>
+
+ * Statement.st: New.
+
2011-01-26 Stefan Schmiedl <[email protected]>
* Table.st: Take current domain into account.
diff --git a/packages/dbd-postgresql/Connection.st b/packages/dbd-postgresql/Connection.st
index 25694b8..f23d3f2 100644
--- a/packages/dbd-postgresql/Connection.st
+++ b/packages/dbd-postgresql/Connection.st
@@ -113,24 +113,20 @@ Connection subclass: PGConnection [
]
do: aSQLQuery [
- <category: 'implementations'>
- ^(PGResultSet new: (handle exec: aSQLQuery))
- checkStatusForDo;
- yourself
+ <category: 'querying'>
+ ^(self prepare: aSQLQuery) execute
]
- prepare: aSQLQuery [
- "FIXME"
-
- <category: 'implementations'>
- self notYetImplemented
+ select: aSQLQuery [
+ <category: 'querying'>
+ ^(self prepare: aSQLQuery) execute
]
- select: aSQLQuery [
- <category: 'implementations'>
- ^(PGResultSet new: (handle exec: aSQLQuery))
- checkStatusForSelect;
- yourself
+ prepare: aSQLQuery [
+ <category: 'querying'>
+ ^(PGStatement on: self)
+ dbHandle: handle;
+ queryString: aSQLQuery.
]
close [
diff --git a/packages/dbd-postgresql/Makefile.frag b/packages/dbd-postgresql/Makefile.frag
index 6991183..3479b44 100644
--- a/packages/dbd-postgresql/Makefile.frag
+++ b/packages/dbd-postgresql/Makefile.frag
@@ -1,5 +1,5 @@
DBD-PostgreSQL_FILES = \
-packages/dbd-postgresql/Connection.st packages/dbd-postgresql/ResultSet.st packages/dbd-postgresql/Row.st packages/dbd-postgresql/ColumnInfo.st packages/dbd-postgresql/Table.st packages/dbd-postgresql/TableColumnInfo.st packages/dbd-postgresql/FieldConverter.st
+packages/dbd-postgresql/Connection.st packages/dbd-postgresql/ResultSet.st packages/dbd-postgresql/Row.st packages/dbd-postgresql/ColumnInfo.st packages/dbd-postgresql/Statement.st packages/dbd-postgresql/Table.st packages/dbd-postgresql/TableColumnInfo.st packages/dbd-postgresql/FieldConverter.st
$(DBD-PostgreSQL_FILES):
$(srcdir)/packages/dbd-postgresql/stamp-classes: $(DBD-PostgreSQL_FILES)
touch $(srcdir)/packages/dbd-postgresql/stamp-classes
diff --git a/packages/dbd-postgresql/Statement.st b/packages/dbd-postgresql/Statement.st
new file mode 100644
index 0000000..c9cfaa7
--- /dev/null
+++ b/packages/dbd-postgresql/Statement.st
@@ -0,0 +1,79 @@
+"======================================================================
+|
+| PostgreSQL bindings, Statement class
+|
+|
+ ======================================================================"
+
+
+"======================================================================
+|
+| Copyright 2011 Free Software Foundation, Inc.
+| Written by Holger Hans Peter Freyther
+|
+| This file is part of the GNU Smalltalk class library.
+|
+| The GNU Smalltalk class library is free software; you can redistribute it
+| and/or modify it under the terms of the GNU Lesser General Public License
+| as published by the Free Software Foundation; either version 2.1, or (at
+| your option) any later version.
+|
+| The GNU Smalltalk class library is distributed in the hope that it will be
+| useful, but WITHOUT ANY WARRANTY; without even the implied warranty of
+| MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU Lesser
+| General Public License for more details.
+|
+| You should have received a copy of the GNU Lesser General Public License
+| along with the GNU Smalltalk class library; see the file COPYING.LIB.
+| If not, write to the Free Software Foundation, 59 Temple Place - Suite
+| 330, Boston, MA 02110-1301, USA.
+|
+ ======================================================================
+"
+
+
+Statement subclass: PGStatement [
+ | dbHandle queryString isSelect |
+
+ <category: 'DBI-Drivers'>
+ <comment: 'I represent a Postgres prepared statement. Or actually right
+now the ability to execute commands with binding.'>
+
+ SelectQueries := #('EXPLAIN' 'SELECT') asSet.
+
+ dbHandle: aHandle [
+ <category: 'private'>
+ dbHandle := aHandle.
+ ]
+
+ queryString [
+ <category: 'accessing'>
+ ^queryString
+ ]
+
+ queryString: aSQLQuery [
+ <category: 'accessing'>
+ queryString := aSQLQuery.
+ ]
+
+ isSelect [
+ <category: 'accessing'>
+ isSelect isNil
+ ifTrue: [isSelect := SelectQueries includes: (self class getCommand: queryString)].
+ ^isSelect
+ ]
+
+ execute [
+ <category: 'querying'>
+ | res |
+ res := PGResultSet new: (dbHandle exec: queryString).
+ self isSelect
+ ifTrue: [res checkStatusForSelect]
+ ifFalse: [res checkStatusForDo].
+ ^ res
+ ]
+
+ executeWithAll: aParams [
+ self notYetImplemented
+ ]
+]
diff --git a/packages/dbd-postgresql/package.xml b/packages/dbd-postgresql/package.xml
index b1a336e..36d6a29 100644
--- a/packages/dbd-postgresql/package.xml
+++ b/packages/dbd-postgresql/package.xml
@@ -8,6 +8,7 @@
<filein>ResultSet.st</filein>
<filein>Row.st</filein>
<filein>ColumnInfo.st</filein>
+ <filein>Statement.st</filein>
<filein>Table.st</filein>
<filein>TableColumnInfo.st</filein>
<filein>FieldConverter.st</filein>
--
1.7.4
>From 20aeb9470d783dd827147136e530fca23cd9810d Mon Sep 17 00:00:00 2001
From: Holger Hans Peter Freyther <[email protected]>
Date: Fri, 8 Apr 2011 14:09:37 +0200
Subject: [PATCH 3/4] dbd-postgres: Allow to execute queries with parameters
This only supports the PostgreSQL way of specifying parameters
using the $1 $2 $3 parameters. It does not support named bindings
or such. This makes going from SQLite to Postgres a lot harder. The
parameters must be provided as string, there is no type conversion
done on this level.
---
packages/dbd-postgresql/ChangeLog | 5 ++++
packages/dbd-postgresql/Connection.st | 38 +++++++++++++++++++++++++++++++++
packages/dbd-postgresql/Statement.st | 21 +++++++++++++----
3 files changed, 59 insertions(+), 5 deletions(-)
diff --git a/packages/dbd-postgresql/ChangeLog b/packages/dbd-postgresql/ChangeLog
index ea768b5..0ca6d3b 100644
--- a/packages/dbd-postgresql/ChangeLog
+++ b/packages/dbd-postgresql/ChangeLog
@@ -1,5 +1,10 @@
2011-04-08 Holger Hans Peter Freyther <[email protected]>
+ * Statements.st: Implement executeWithAll:
+ * Connection.st: Invoke PQexecParams.
+
+2011-04-08 Holger Hans Peter Freyther <[email protected]>
+
* Statement.st: New.
2011-01-26 Stefan Schmiedl <[email protected]>
diff --git a/packages/dbd-postgresql/Connection.st b/packages/dbd-postgresql/Connection.st
index f23d3f2..202e604 100644
--- a/packages/dbd-postgresql/Connection.st
+++ b/packages/dbd-postgresql/Connection.st
@@ -188,6 +188,44 @@ CObject subclass: PQConnection [
exec: aSqlStatement [
<cCall: 'PQexec' returning: #{PQResultSet} args: #(#self #string)>
]
+
+ "Executing SQL with params"
+ exec: aSqlStatement with: params [
+ | par |
+
+ "Sanity check before we do memory allocations in C"
+ (params allSatisfy: [:el | el isString]) ifFalse: [
+ ^ self error: 'All parameters must be strings here'].
+
+ "Convert the params into an array of C-Strings."
+ par := CStringType new: params size.
+ 1 to: params size do: [:each |
+ par at: each - 1 put: (params at: each)].
+
+ [^ self
+ exec_params:aSqlStatement
+ n_par: params size
+ types: nil
+ values: par
+ lengths: nil
+ formats: nil
+ res: 0
+ ] ensure: [
+ "Free the memory we allocated"
+
+ 0 to: params size - 1 do: [:each |
+ (par derefAt: (each * CStringType sizeof) type: CObjectType) free
+ ].
+
+ par free.
+ ]
+ ]
+
+ exec_params: cmd n_par: parAms types: types values: vals lengths: l formats: f res: r [
+ <category: 'private'>
+ <cCall: 'PQexecParams' returning: #{PQResultSet}
+ args: #(#self #string #int #cObject #cObject #cObject #cObject #int)>
+ ]
]
diff --git a/packages/dbd-postgresql/Statement.st b/packages/dbd-postgresql/Statement.st
index c9cfaa7..9335989 100644
--- a/packages/dbd-postgresql/Statement.st
+++ b/packages/dbd-postgresql/Statement.st
@@ -53,6 +53,8 @@ now the ability to execute commands with binding.'>
queryString: aSQLQuery [
<category: 'accessing'>
+ "In PostgreSQL one can use $1 for binding parameters with the
+ executeWithAll:. The parameters must be all strings."
queryString := aSQLQuery.
]
@@ -63,17 +65,26 @@ now the ability to execute commands with binding.'>
^isSelect
]
+ checkResult: aRes [
+ <category: 'private'>
+ self isSelect
+ ifTrue: [aRes checkStatusForSelect]
+ ifFalse: [aRes checkStatusForDo].
+ ^ aRes
+ ]
+
execute [
<category: 'querying'>
| res |
res := PGResultSet new: (dbHandle exec: queryString).
- self isSelect
- ifTrue: [res checkStatusForSelect]
- ifFalse: [res checkStatusForDo].
- ^ res
+ ^ self checkResult: res.
]
executeWithAll: aParams [
- self notYetImplemented
+ | res |
+ "In PostgreSQL one can use $1 for binding parameters with the
+ executeWithAll:. The parameters must be all strings."
+ res := PGResultSet new: (dbHandle exec: queryString with: aParams).
+ ^ self checkResult: res.
]
]
--
1.7.4
>From 85de709a47474634524c3492a9927a53065b1eae Mon Sep 17 00:00:00 2001
From: Holger Hans Peter Freyther <[email protected]>
Date: Fri, 8 Apr 2011 10:35:13 +0200
Subject: [PATCH 4/4] dbd-postgres: Do not attempt to the NULL value
If the value of a column is NULL the closest Smalltalk type
is nil. Do not attempt to create any instance from a NULL.
---
packages/dbd-postgresql/ChangeLog | 4 ++++
packages/dbd-postgresql/ColumnInfo.st | 3 ++-
2 files changed, 6 insertions(+), 1 deletions(-)
diff --git a/packages/dbd-postgresql/ChangeLog b/packages/dbd-postgresql/ChangeLog
index 0ca6d3b..295b594 100644
--- a/packages/dbd-postgresql/ChangeLog
+++ b/packages/dbd-postgresql/ChangeLog
@@ -1,3 +1,7 @@
+2011-04-07 Holger Hans Peter Freyther <[email protected]>
+
+ * ColumnInfo.st: Do not attempt to convert a NULL value.
+
2011-04-08 Holger Hans Peter Freyther <[email protected]>
* Statements.st: Implement executeWithAll:
diff --git a/packages/dbd-postgresql/ColumnInfo.st b/packages/dbd-postgresql/ColumnInfo.st
index 436ccbd..74c5343 100644
--- a/packages/dbd-postgresql/ColumnInfo.st
+++ b/packages/dbd-postgresql/ColumnInfo.st
@@ -213,7 +213,8 @@ ColumnInfo subclass: PGColumnInfo [
PGColumnInfo class >> convert: aRaw type: aType [
<category: 'type conversions'>
- TypeConversions at: aType ifPresent: [:sel | ^self perform: sel with: aRaw].
+ aRaw isNil ifFalse: [
+ TypeConversions at: aType ifPresent: [:sel | ^self perform: sel with: aRaw]].
^aRaw
]
--
1.7.4
_______________________________________________
help-smalltalk mailing list
[email protected]
http://lists.gnu.org/mailman/listinfo/help-smalltalk
