How to insert 'empty' or 'default' values when SQL is first prepared and then executed

------_=_NextPart_001_01C6E1A5.2CB4F066
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

In my project I first prepare an INSERT statement 'INSERT INTO table
(col1, col2, col3, ...) VALUES (?, ?, ?, ...)' once, then insert
multiple records using 'execute(@values)'. The problem is that not all
values of all columns are available. So I should either leave them empty
or set them as default values. I try to use 'NULL' or 'DEFAULT' for
those unavailable values in '@values', but it didn't work. I know I
probably can set '0' for numerical columns and 'space' for varchar
columns. But is there a simpler way so I don't need to worry about data
types?

=20

Thanks,

Pai


------_=_NextPart_001_01C6E1A5.2CB4F066--
0
PPeng
9/26/2006 7:51:35 PM
perl.dbi.users 11098 articles. 1 followers. Follow

2 Replies
487 Views

Similar Articles

[PageSpeed] 28

It depends on the database you connect to. Often, you either INSERT NULL 
or you simply do not specify the column, sometimes, both methods are 
allowed.

To insert NULL into the database, you use undef as value (which is also 
what you get back when reading NULL values).

Alexander

Pai Peng wrote:
> In my project I first prepare an INSERT statement 'INSERT INTO table
> (col1, col2, col3, ...) VALUES (?, ?, ?, ...)' once, then insert
> multiple records using 'execute(@values)'. The problem is that not all
> values of all columns are available. So I should either leave them empty
> or set them as default values. I try to use 'NULL' or 'DEFAULT' for
> those unavailable values in '@values', but it didn't work. I know I
> probably can set '0' for numerical columns and 'space' for varchar
> columns. But is there a simpler way so I don't need to worry about data
> types?
>
>  
>
> Thanks,
>
> Pai
>
>
>   

-- 
Alexander Foken
mailto:alexander@foken.de  http://www.foken.de/alexander/

0
alexander
9/26/2006 10:38:27 PM
--Nq2Wo0NMKNjxTN9z
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On 2006-09-26 12:51:35 -0700, Pai Peng wrote:
> In my project I first prepare an INSERT statement 'INSERT INTO table
> (col1, col2, col3, ...) VALUES (?, ?, ?, ...)' once, then insert
> multiple records using 'execute(@values)'. The problem is that not all
> values of all columns are available. So I should either leave them empty
> or set them as default values. I try to use 'NULL' or 'DEFAULT' for
> those unavailable values in '@values', but it didn't work.

That would insert the literal string 'NULL' or 'DEFAULT'. To insert a
null value, use undef.

	hp

--=20
   _  | Peter J. Holzer    | If I wanted to be "academically correct",
|_|_) | Sysadmin WSR       | I'd be programming in Java.
| |   | hjp@wsr.ac.at      | I don't, and I'm not.
__/   | http://www.hjp.at/ |   -- Jesse Erlbaum on dbi-users

--Nq2Wo0NMKNjxTN9z
Content-Type: application/pgp-signature
Content-Disposition: inline

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)

iQDQAwUBRRpMsFLjemazOuKpAQJ27gXTB9/jatInJvxgzm4r1fRfnj6ou6DO4+lk
9kmP9EziFBqXt1lMuqmwtUfYx4BdX/Vu6ypE1oQQWXzGaEQfKOVhU1rvVn2GqH/h
6Am+nmrkzP5dQt/Ec5pAurJYtMxYKH15O+ZNlH5Y6wTJjJbPwoUuAZWyjnKavPhP
NLoiAyITF7UeDEOOnIoctQdz5bLqWqmYz0SdrEh7TgDGlHR5eDpRgKrJ6/2VTWF1
mu/BjiDWIzV5kc6SyDEcIekgqg==
=+IAM
-----END PGP SIGNATURE-----

--Nq2Wo0NMKNjxTN9z--
0
hjp
9/27/2006 10:04:32 AM
Reply:

Similar Artilces:

''''''''''''''''''''
Name: haznen Email: haznenatyahoodotcom Product: Gran Paradiso Alpha 8 Summary: '''''''''''''''''''' Comments: '''''''''''''''''''''''''''''''''''' Browser Details: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9a1) Gecko/20061204 UGES/1.7.2.0 GranParadiso/3.0a1 From URL: http://www.mozilla.org/projects/granparadiso/ Note to readers: Hendrix gives...

'''''
Name: mario Email: ramar17atfastwebnetdotit Product: Gran Paradiso Alpha 2 Summary: ''''' Comments: K: Browser Details: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9a2) Gecko/20070206 GranParadiso/3.0a2 ...

'Value' should be between 'minimum' and 'maximum'.
I'm attempting to invoke with parameters:  Dim thing2 As New mydelsubPBStep(AddressOf pbStep) Me.Invoke(thing2, "setMax", dt.Rows.Count)      <---- this is where the error occurs   the delegate looks like this: Private Delegate Sub mydelsubPBStep(ByVal mode As String, ByVal value As Integer)   the function it's calling looks like this:Private Sub pbStep(ByVal mode As String, ByVal value As Integer) Select Case mode.ToLower Case "clear" ProgressBar1.Value = 0 Case "step" ProgressBar1.PerformStep() L...

'do' won't 'do' if '/'
Greetings to All from Au, Have a NetWare Perl 5.8.4 and wanted to tweak File\Spec\NW.pm to try and standardise on '/' separators. If I run a test script (t/uni/lower.t) with an unmodified NW.pm, it calls t/uni/case.pl, that, in case.pl, (when the path separators are '\'), the $file is '..\lib\unicore\To\Lower.pl' and the following code portion works, with $simple getting a returned table: sub casetest { my ($base, $spec, $func) = @_; my $file = File::Spec->catfile(File::Spec->catdir(File::Spec->updir, "lib", &qu...

'''
Name: L Lachowsky Email: e2brutus_10atyahoodotcom Product: Firefox Summary: ''' Comments: why duddn this surprise me... I change default server from IE to Mozilla, and then i get error messages. well, shud I decide to not get on my computer with Mozilla..or shud I risk security breeches with IE....hmmm at least i can get on the internet with IE Browser Details: Mozilla/5.0 (Windows; U; Windows NT 6.0; en-US; rv:1.9.0.5) Gecko/2008120122 Firefox/3.0.5 From URL: http://hendrix.mozilla.org/ Note to readers: Hendrix gives no expectation of a response to this fee...

Cast from type ''''DBNull'''' to type ''''String'''' is not valid.
How do I avoid getting this error: Cast from type ''''DBNull'''' to type ''''String'''' is not valid. I am trying to get values from the database into a form for updating using a Datareader, SQL is set to allow nulls for certain fields as they are not required but the only way I can get the form to display is by adding a space in SQL. Is there another way around this? Check if it is equal to DBNull.Value first and if it is, don't do the cast.Stanley Tan theSpoke Blog Where in the code does it need to go? I am using the following and it's the profile that isn't always requi...

table_info('','','','%') cannot return any types
I was recently confirming table_info special cases and discovered the case for getting table_types cannot work. table_info('','','','%') should return a list of table types but it returns a list of empty strings instead: my @types = $h->tables('', '', '', '%'); print "all types:\n", join("xxx\n", @types), "\n"; # should output something like: # "dbo" # "INFORMATION_SCHEMA" # "sys" # and actually outputs: xxx xxx It seems to be down to the following ...

SQL replace '-' by ''
Hi all i have a question regarding sql, i want to replace some characters...   any knows simply how to do this?   I want to replace "999-25000-69" by "9992500069"   grtzDon't forget to click "Mark as Answer" on the post that helped you. SELECT Replace('999-25000-69','-', '') or SELECT Replace(col1,'-', '') as col1 FROM yourTableLimno...

'or' or 'union'
Hello I was just wondering, in general what is better to use, an 'or' clause in a select or a 'union' to join two selects together. Do both statements create work tables? Many thanks Alex I think OR will be better than union. because suppose u have 3 tables and using OR u can join table a and table b and table c so each table will have only one read. but using union you will join table a and table b and in another query of union u will use table (a or b) and table c so ur one read is more in union . Ramdas Alex Cheung wrote: > Hello > >...

'IN' Clause or 'OR'
Hello, [1]: select * from TABLEA where COL1 IN('value1','value2''valu3'....) [2]:select * from TABLEA where COL1 = 'value1' OR COL1= 'value2' OR COL1='valu3'. TABLEA is a huge table and it has non-clustered index on COL1. Among the above 2 queries, which query will give me the better performance or fast response and WHY? What is the difference between 'IN' and 'OR' clauses as for as Sybase Optimization is concerned. Which is the better one to be used on huge tables. Thanks. Mac An IN list is treated ...

'b'..'a'
Hello. I'm using defferent 5.6.0's for Win32. I wonder wheter following behaviour is intentional or not: d:\>perl -e "print 'b'..'c'" bc d:\>perl -e "print 'b'..'a'" bcdefghijklmnopqrstuvwxyz I expected empty list in latter case, like in perl -e "print 'bb'..'a'" <!ENTITY Vadim REALLIFE "Vadim V.Konovalov, St.Petersburg, Russia"> &Vadim; On Wed, Sep 20, 2000 at 03:14:41PM +0400, Konovalov, Vadim wrote: > Hello. > > I'm using defferent 5.6.0'...

Why 'and' not '&&' ? (was Cleaning up 'uninitialized value')
> Use: > > if (@ARGV and -T $ARGV[0]) { ... } Just wondering if there is a reason for using the lower precedence 'and' here instead of '&&'? I haven't been finding many uses for 'and' which tells me I might not fully understand it. Thanks, Peter C. On May 30, Peter Cornelius said: >> if (@ARGV and -T $ARGV[0]) { ... } > >Just wondering if there is a reason for using the lower precedence 'and' >here instead of '&&'? I haven't been finding many uses for 'and' which >tells...

how to specify 'near to' value and 'from-to' value
Hi, I have a property search  with a ability to enter minimum and maximum price and also a sq. m. area size. How do I implement this in my sql query  - 'from-to' value and 'near to' value ThanksAndy Current project: Cycle round the world  Hi,you could use the BETWEEN statement. SELECT PropertyName, Price WHERE Price BETWEEN 100 AND 500  for your "NEAR TO" search you also could use the BETWEEN statement. SELECT PropertyName, Price WHERE Price BETWEEN @SearchValue - 100 AND @SearchValue + 100 Hope this helps :-) ...

value of variable switches from ' ' to 'value'
--------------000108090907030800060105 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit I'm trying to figure out what exactly a script I wrote (see the attachment) is doing by stepping through it in the debugger. I have an array of dates in %Y-%m-%d format for each of the last 7 days. So, for instance, the range of dates for today (June 17, 2007) is from the 10th to the 16th. I then take these dates and use them to find work tickets in our ticketing system which were last updated on the day currently being checked. So, the first date would be 200...

Web resources about - How to insert 'empty' or 'default' values when SQL is first prepared and then executed - perl.dbi.users

Resources last updated: 12/21/2015 2:49:04 AM