Parameter default value
pere...@jetemail.net
PROCEDURE a.k.a Parameter Query (PARAMETERS declaration)?
I created the proc/Query using this SQL:
CREATE PROCEDURE Proc5 (
argdata_col VARCHAR(10) = 'Hello'
) AS
SELECT key_col, data_col
FROM Test
WHERE data_col =
IIF(argdata_col IS NULL, data_col, argdata_col);
It's Jet 4.0 syntax so you need to put Access XP/2003 into ANSI-92
query mode or use ADO e.g.
CurrentProject.Connection.Execute _
"CREATE PROCEDURE Proc5 (" & _
"argdata_col VARCHAR(10) = 'Hello' " & _
") AS " & _
"SELECT key_col, data_col " & _
"FROM Test " & _
"WHERE data_col = " & _
"IIF(argdata_col IS NULL, data_col, argdata_col); "
I presumed using the respective ADOX.Procedure object's Command object
that it would pre-fill the Parameter object's Value property for me
using the default, but no luck (in the VBE Immediate Window):
Set oCat = CreateObject("ADOX.Catalog")
oCat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\Tempo\TestProcs.mdb"
Set oComm = oCat.Procedures("Proc5").Command
? oComm.Parameters("argdata_col").Value = vbEmpty
True
DAO 3.6 doesn't appear to help me either:
Set db = DBEngine.OpenDatabase("C:\Tempo\TestProcs.mdb")
? db.QueryDefs("Proc5").Parameters(0).Value = vbEmpty
True
I hope I'm not faced with parsing the SQL because my next proc/Query
looks like this:
? db.QueryDefs("Proc6").SQL
PARAMETERS
argdata_col1 Text ( 10 ) = ';:,'',:;',
argdata_col2 Text ( 10 ) = 'Yes';
SELECT key_col, data_col
FROM Test
WHERE data_col =
IIF(argdata_col1 IS NULL, argdata_col2, argdata_col1);
Any ideas? Many thanks.
MGFoster
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Problems: Your 1st "query" is an incorrect T-SQL stored procedure
(incorrect 'cuz you're using IIf() function, which is a VBA function -
not a T-SQL function). The correct stored procedure would look like
this:
CREATE PROCEDURE Proc5 (
argdata_col VARCHAR(10) = 'Hello'
) AS
SELECT key_col, data_col
FROM Test
WHERE data_col =
CASE WHEN argdata_col IS NULL
THEN data_col
ELSE argdata_col
END
You've explicitly set the default value, "Hello". Why are you trying to
read a known value?
Your final query is neither a stored procedure nor a valid Access query.
Parameters in Access queries do not set the default values in the
PARAMETERS clause. You can't do this:
PARAMETERS
argdata_col1 Text ( 10 ) = ';:,'',:;',
argdata_col2 Text ( 10 ) = 'Yes';
Only this:
PARAMETERS
argdata_col1 Text ( 10 ), argdata_col2 Text ( 10 );
You can set a "default" value when the parameter is NULL like this:
WHERE
column = Nz(argdata_col1,";:,:;")
AND column2 = Nz(argdata_col2,"Yes")
I don't know what you're trying to do w/ a default of ';:,'',:;' Do you
mean this:
column In (';:', ':;')
???
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBQzrQQ4echKqOuFEgEQIzXwCg1L3kDccHl1UIeezS7/e3jJfhmKYAn0IH
UUwOLlVQHc8Ez8FtoDAC19/c
=tAtb
-----END PGP SIGNATURE-----
pere...@jetemail.net
MGFoster wrote:
> > Any ideas how to retrieve a parameter's default value for an Access/Jet
> > PROCEDURE a.k.a Parameter Query (PARAMETERS declaration)?
> >
Sorry, I thought I'd made it clear this is an Access/Jet PROCEDURE. I
gave the (I hope) Access-friendly name 'parameter Query'. I posted code
to create the proc but perhaps this wasn't explicitly MS Access enough
(could potentially apply to an Access ADP MSDE/MSSQL project perhaps,
hence your T-SQL assumption?)
> You've explicitly set the default value, "Hello". Why are you trying to
> read a known value?
The data is meaningless. It's just a test :)
> Your final query is neither a stored procedure nor a valid Access query.
What do you mean by 'valid Access query' here? This could just be
semantics i.e. is what results from a Jet CREATE PROCEDURE statement a
'stored procedure'?
Here is some more explicit code to create an .mdb Access/Jet database
with a test table and data, create the proc, re-read the definition
and, to demonstrate it is a valid Jet PROCEDURE, execute it:
Sub Test_Access_Jet()
On Error Resume Next
Kill "C:\Test.mdb"
On Error GoTo 0
Dim cat As Object
Set cat = CreateObject("ADOX.Catalog")
With cat
.Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Jet OLEDB:Engine Type=4;" & _
"Data Source=C:\Test.mdb"
With .ActiveConnection
.Execute _
"CREATE TABLE Test (" & _
" key_col INTEGER NOT NULL," & _
" data_col VARCHAR(10));"
.Execute _
"INSERT INTO Test VALUES" & _
" (1, 'Plenty');"
.Execute _
"INSERT INTO Test VALUES" & _
" (2, NULL);"
.Execute _
"INSERT INTO Test VALUES" & _
" (3, 'N/A');"
.Execute _
"CREATE PROCEDURE TestProc " & _
"(arg_data_col VARCHAR(10) = 'N/A') AS " & _
" SELECT Key_col, data_col" & _
" FROM Test" & _
" WHERE data_col = " & _
" IIF(arg_data_col IS NULL," & _
" data_col, arg_data_col);"
Dim rs As Object
Set rs = .OpenSchema(16)
MsgBox rs!PROCEDURE_DEFINITION
Set rs = .Execute("EXECUTE TestProc")
MsgBox rs.Source & vbCr & vbCr & rs.GetString
Set rs = .Execute("EXECUTE TestProc NULL")
MsgBox rs.Source & vbCr & vbCr & rs.GetString
Set rs = .Execute("EXECUTE TestProc 'Plenty'")
MsgBox rs.Source & vbCr & vbCr & rs.GetString
End With
End With
End Sub
I've also opened Test.mdb in Access95 (EngineType=4 creates a Jet 3.x
version .mdb) and TestProc opens OK, i.e. prompts for the parameter
then returns the correct data (but does not apply the default), so I
hope that satisfies the test for 'valid Access query'. (BTW for 'Query
design mode' it open in GUI mode by default, not SQL, and in converting
to SQL it drops the parameter's default value).
I hope this helps you to help me get at this parameter default value.
Thanks again.
pere...@jetemail.net
MGFoster wrote:
> I don't know what you're trying to do w/ a default of ';:,'',:;'
Again, the data is meaningless. My point is, it would be tricky to
parse the SQL to extract the parameter's value because the parameter
value, held in single quotes in the definition, could itself contain
single quotes (the two quotes represents a one escaped quote), commas
(the delimiting character for parameter definitions), semicolons (the
delimiting character between PARAMTERS declaration and main SQL
definition), equals signs (the delimiting character between the main
parameter definition and the parameter default value definition) etc
etc.
pere...@jetemail.net
MGFoster wrote:
> CREATE PROCEDURE Proc5 (
> argdata_col VARCHAR(10) = 'Hello'
> ) AS
> SELECT key_col, data_col
> FROM Test
> WHERE data_col =
> CASE WHEN argdata_col IS NULL
> THEN data_col
> ELSE argdata_col
> END
Now that I think about it, your parameter name is illeagal. Also,
COALESCE is elegant here:
CREATE PROCEDURE Proc5 (
@data_col VARCHAR(10) = 'Hello'
) AS
SELECT key_col, data_col
FROM Test
WHERE data_col = COALESCE(@data_col, data_col)
Back in Access/Jet, any ideas about getting that default parameter
value?
Michel Walsh
iif( myParam Is Null, default_value, myParam )
assuming that if the parameter has a Null value, it is to be considered that
the parameter is not supplied,
Vanderghast, Access MVP
<pere...@jetemail.net> wrote in message
news:1128005723.9...@g14g2000cwa.googlegroups.com...
MGFoster
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
You're correct about the syntax for my parameter in the procedure, I
just forgot the @ at the beginning (I was just blindly following your
example); and, yes, COALESCE() does work well in that situation.
What I was trying to get at about Access vs. T-SQL queries/stored
procedures (SPs). When using an .adp file you are actually using the MS
SQL Server (or, the desktop edition MSDE) DB engine. Your post example
was showing a T-SQL SP (not an Access query) which was using the VBA
function IIf(); this is incorrect in T-SQL.
Concerning retrieving the default value of a SP: There isn't any easy
way to do this. The only way I can think of is to parse the SP's source
by using the following to retrieve the SP text:
SELECT ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = 'MyStoredProcedureName'
The ROUTINE_DEFINITION column holds 4,000 characters of the SP
definition. You'd have to parse out your parameters' defaults from
that.
What I was getting at when I stated "why are you trying to read a known
value" was - for someone writing a query that uses a SP, that person
should know the defaults of the SP parameters; either thru documentation
or 'cuz that person wrote the SP. ;-) If you're doing it on the fly,
then something very strange, and, therfore, probably very wrong is going
on.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBQzwVWYechKqOuFEgEQK5BgCfSQkfTRpNVXIbp4bdH+Cvmkl+R5kAnRk5
PwW+IaCIESWc9qUY/m/fai47
=IUuH
-----END PGP SIGNATURE-----
pere...@jetemail.net
MGFoster wrote:
> What I was trying to get at about Access vs. T-SQL queries/stored
> procedures (SPs). When using an .adp file you are actually using the MS
> SQL Server (or, the desktop edition MSDE) DB engine. Your post example
> was showing a T-SQL SP (not an Access query) which was using the VBA
> function IIf(); this is incorrect in T-SQL.
Please take another at my code. This is not T-SQL, not an .adp file,
not SQL Server, not MSDE. It really is Jet/Access. Honest. Please try
running my VBA code: it creates a Access/Jet database, table, data,
PROC/Query and uses it.
MGFoster
I suggest you read the documentation.
pere...@jetemail.net
MGFoster wrote:
> I suggest you read the documentation.
Thank you. Which documentation? The Microsoft Jet SQL Reference for
Data Definition Language:
http://office.microsoft.com/en-us/assistance/HP010322191033.aspx
doesn't even mention the use of default values.
Thanks again.
pere...@jetemail.net
Michel Walsh wrote:
> iif( myParam Is Null, default_value, myParam )
>
> assuming that if the parameter has a Null value, it is to be considered that
> the parameter is not supplied,
I'm not sure I understand you point.
In my example (upthread) the call
EXECUTE TestProc
i.e. *omitting* the parameter returns a different resultset to the call
EXECUTE TestProc NULL
i.e. *explicitly* setting the parameter value to NULL. The behaviour
is, when the parameter is omitted the default value bites. This
demonstrates that Access/Jet will use the default value (unless it has
been explicitly, set even to null).
What I need to be able to do is the extract that default value from the
schema. You seem to be suggesting that by running the PROC/Query
without the parameter I will be able to see the effect that the omitted
parameter has had and therefore be able to ascertain what the value is.
I can't see how this could work. For one, the parameter value may not
be revealed at all e.g.
CREATE PROC TestProc2 (arg1 INTEGER = 99) AS
SELECT * FROM Test
WHERE key_col BETWEEN 1 AND arg1
For another, it may be a SQL command e.g.
CREATE PROC TestProc3 (arg1 INTEGER = 1) AS
DELETE FROM Test
WHERE key_col = arg1
Any further thoughts? Thanks again.
Michel Walsh
EXECUTE TestProc
won't run, you will get an error, if some arguement is defined in the
argument list.
If you use a parameter without declaring it:
===============
CurrentProject.Connection.Execute "CREATE PROC jojova(x Integer ) AS SELECT
iota FROM iotas WHERE iota <=param"
? CurrentProject.Connection.Execute("EXECUTE jojova").Fields(0).Value
-- error about Too few parameters, 2 expected.
? CurrentProject.Connection.Execute("EXECUTE jojova 5 ").Fields(0).Value
-- error about not having a DEFAULT value
CurrentProject.Connection.Execute "DROP PROC jojova"
=============
you get a different error, from ADO, about the parameter param not having a
DEFAULT value. It seems that Jet reports the error differently to ADO.
So it sounds that the concept is considered, but may be not fully
implemented. I try to get more details, and if I find any, I will post them
here.
Hoping it may help,
Vanderghast, Access MVP
<pere...@jetemail.net> wrote in message
news:1128065736.5...@g43g2000cwa.googlegroups.com...
pere...@jetemail.net
Michel Walsh wrote:
>
> EXECUTE TestProc
>
> won't run, you will get an error, if some arguement is defined in the
> argument list.
It runs for me without error. Did you try running my code to create the
db, proc, etc?
> If you use a parameter without declaring it
> you get a different error, from ADO, about the parameter param not having a
> DEFAULT value.
Did you try it with a param that *does* exist? Suggested example for
you:
CurrentProject.Connection.Execute "CREATE PROC jojova(x INTEGER = 1) AS
SELECT
iota FROM iotas WHERE iota <= x"
? CurrentProject.Connection.Execute("EXECUTE jojova").Fields(0).Value
This should execute OK and render as WHERE iota <= 1 because the
parameter default value will bite. Are you seeing this?
Michel Walsh
It was not working, initially, for some reason, but now, with you, that
was making two people reporting it was working.... SO.... I tried it over,
on a new database, and indeed, it works. I don't know what went wrong when I
initially did try it. :-(
Vanderghast, Access MVP
<pere...@jetemail.net> wrote in message
news:1128081310.5...@g47g2000cwa.googlegroups.com...
MGFoster
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
In the Access Help hierarchy:
Microsoft Jet SQL Reference
Data Definition Language
CREATE PROCEDURE Statement - look at the example link
(at the bottom of the page)
The example is in the Access Help file not on the Microsoft page you
cited above.
Note that in Jet a default can't be assigned to the parameters in the
parameters declaration line.
In reality the Jet CREATE PROCEDURE statement is the same as a Jet query
w/ a PARAMETERS line. E.g.:
A Jet "procedure":
CREATE PROCEDURE this Date, that Text(2);
SELECT * FROM table_name WHERE col1 = this AND col2 = that;
A Jet select query:
PARAMETERS this Date, that Text(2);
SELECT * FROM table_name WHERE col1 = this AND col2 = that;
See? The same, except for the 1st line.
Then read the MS SQL Server Books on Line (the SQL Server Help file).
Compare the CREATE PROCEDURE statement to the Jet CREATE PROCEDURE
statement. A default can be assigned to the SQL Server (T-SQL)
parameters. As I've been saying in previous posts you are mixing the 2
together. Learn the proper syntax for each version.
Good luck.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBQz1zoIechKqOuFEgEQIetwCfcz84QsGw7HYLFoLIBJo69TYhNtoAnibQ
e15C1HhsDEocTFoCy25Fn5Rp
=k+9c
-----END PGP SIGNATURE-----
pere...@jetemail.net
MGFoster wrote:
> Note that in Jet a default can't be assigned to the parameters in the
> parameters declaration line.
Where does it say this in the help file or documentation? If my link is
wrong (and it seem to be correct to me) then can you please supply the
correct link or a direct quote from the documentation that I can google
for it (I only have Access95 installed at the moment, which I need, and
if I install Access2003 it will automatically uninstall Access95, so
I'm relying on online resources at the moment).
The truth is, in Jet a default *can* be assigned in the parameters
declaration. My example code demonstrates this. Please try it. I'll
post it again here, slightly modified:
Sub Test_Access_Jet3()
"(arg_data_col VARCHAR(10) = 'N/A') AS " & _
" SELECT Key_col, data_col" & _
" FROM Test" & _
" WHERE data_col = " & _
" IIF(arg_data_col IS NULL," & _
" data_col, arg_data_col);"
Dim rs As Object
Set rs = .Execute("EXECUTE TestProc")
MsgBox rs.Source & vbCr & vbCr & rs.GetString
End With
End With
End Sub
Note that in the parameters declaration I have assigned the default as
'N/A' i.e.
arg_data_col VARCHAR(10) = 'N/A'
I've then executed the proc and omitted an explicit parameter value
from the call:
EXECUTE TestProc
One row is returned, being the row where data_col = 'N/A'.
My conclusion is that, in absence of an explicit value, Jet has
recognized, honored and applied the default value. How else would you
explain the fact that just one row is returned and it is the row where
data_col = 'N/A'?
> As I've been saying in previous posts you are mixing the 2
> together. Learn the proper syntax for each version.
And as I keep replying, this has nothing to do with SQL Server and I am
not mixing my syntax. If you could test my code and post back your
conclusions I would be extremely grateful. Many thanks for your help.
MGFoster
Hash: SHA1
You've created some hybrid ADO/DAO query in Access. After you create
your Test.mdb open it & look at the query you've created. It looks like
an Access DAO QueryDef, but acts like a Stored Procedure - the default
gets set, which I can't find any documentation on why it does that.
The following is the result of your CREATE PROCEDURE statement - a
typical Access query.
PARAMETERS arg_data_col Text ( 255 );
SELECT Test.key_col, Test.data_col
FROM Test
WHERE (((Test.data_col)=IIf([arg_data_col] Is
Null,[data_col],[arg_data_col])));
One that I had posted in one of my earlier posts.
The WHERE clause is equivalent to this:
WHERE data_col = Nz(arg_data_col, data_col)
Which means if the parameter is NULL, then compare the data_col to the
data_col (a True evaluation), which will return all rows.
BUT, your WHERE clause doesn't work like the Nz() function WHERE clause.
It appears to fill the parameter w/ the default value "N/A"; therefore,
something "underneath" is going on that I don't understand. Apparently,
by using ADO, you've created some hidden Default value for the parameter
-- in Access 95 format, no less! Good grief....
Back to your original Q: How to read the default value? Since it
doesn't show in the SQL view AND it appears to be undocumented, I
haven't the slightest....
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBQ0Hf4IechKqOuFEgEQIIdQCdEf+KQfn5Ewnkc6n9085Eq59l6+EAoKEl
GW+BQqsyQEZpwZIB6tw5Ns/q
=MEmI
-----END PGP SIGNATURE-----
pere...@jetemail.net
MGFoster wrote:
> something "underneath" is going on that I don't understand. Apparently,
> by using ADO, you've created some hidden Default value for the parameter
> -- in Access 95 format, no less! Good grief....
>
> Back to your original Q: How to read the default value? Since it
> doesn't show in the SQL view AND it appears to be undocumented, I
> haven't the slightest....
Oh well. Thanks for sticking with me, though.