Hi,
I'm trying to insert NULL for a column into SQL DB through a formating a string, but not through insert ...values (?) and i don't see a NULL in the DB., the column value appears blank, but i expect a NULL.
When i explictly assign NULL to that variable, it treats the values as a string, not a NULL and isnull is not returning the ROW.
$_data will have an address of the row fetched.
Program: -
while ($_data = $_src_dbh->fetch_row())
-
{
-
$i++;
-
#write to datafile
-
-
if (not defined $_data->{'level_3'} )
-
$_data->{'level_3'} = undef;
-
-
my $string = "insert into ".$_db_name."..hierarchy_buffer (client_scheme_code,
-
client_level_1_code,
-
client_level_2_code,
-
client_level_3_code,
-
client_security_id)
-
values (
-
'". $_data->{'scheme'} ."',
-
'". $_data->{'level_1'} ."',
-
'". $_data->{'level_2'} ."',
-
'". $_data->{'level_3'} ."',
-
'". $_data->{'client_security_id'}."') \n";
-
-
-
If we insert the values thru values ( ? ), then undef is working properly and has a NULL in the DB, since i'm formating the string, the undef value appears as a blank in the DB, but not NULL. So when is use isnull() it is not returning the row.
Thanks
Atif
4 2532 eWish 971
Recognized Expert Contributor
Which database are you using? Here is a sample of how you can handle the undef / null values with MySQL using the DBI. - my $var1 = 'Some Name';
-
my $var2 = '';
-
-
my $check_var = $var2 eq '' ? undef : $var2;
-
-
my $sql = $dbh->prepare(qq{INSERT INTO test_table (col1, col2) VALUES(?,?)});
-
$sql->execute($var1, $check_var);
-
$sql->finish();
When you check the database it should have the 'null' in lieu of the word "undef" or the field being empty.
Which database are you using? Here is a sample of how you can handle the undef / null values with MySQL using the DBI. - my $var1 = 'Some Name';
-
my $var2 = '';
-
-
my $check_var = $var2 eq '' ? undef : $var2;
-
-
my $sql = $dbh->prepare(qq{INSERT INTO test_table (col1, col2) VALUES(?,?)});
-
$sql->execute($var1, $check_var);
-
$sql->finish();
When you check the database it should have the 'null' in lieu of the word "undef" or the field being empty.
Hi,
I'm using MS SQL Db. My issue is i'm not using insert...values ( ? ?) syntax, if I use values along with ?, then undef is working properly. But I'm formatting a string, as shown below -
my $string = "insert into ".$_db_name."..pre_bcp_hierarchy_buffer (client_scheme_code,
-
client_level_1_code,
-
client_level_2_code,
-
client_level_3_code,
-
client_security_id)
-
values (
-
'". $_data->{'scheme'} ."',
-
'". $_data->{'level_1'} ."',
-
'". $_data->{'level_2'} ."',
-
'". $_data->{'level_3'} ."',
-
'". $_data->{'client_security_id'}."') \n";
-
-
print " String Val $string \n";
-
-
$allstring = $allstring.$string;
-
# Check if $i is n * 1000
-
if ( !($i % 1000 ))
-
{
-
#write to pre_bcp_option_buffer
-
$retval = $_trg_dbh->exec( $allstring );
-
-
When the count reaches 1000, i.e. when i've formatted an insert string of 1000 rows i'm inseritng at one shot, before formatting the insert string i'm checking whether i've any null values in any of the columns. if it is there then i've to assign NULL that is undef (per PERL) to that column in the insert statement/string i'm formatting, coz its a string the undef is assigning ' ' into the db, but not a [NULL]. I expect a [NULL] in the DB so that firther my query can use isnull() to check for NULL values.
Since my program is inserting the ' ' the isnull() couldn't identify the NULL rows.
Thanks
Atif
Hi,
I'm using MS SQL Db. My issue is i'm not using insert...values ( ? ?) syntax, if I use values along with ?, then undef is working properly. But I'm formatting a string, as shown below -
my $string = "insert into ".$_db_name."..pre_bcp_hierarchy_buffer (client_scheme_code,
-
client_level_1_code,
-
client_level_2_code,
-
client_level_3_code,
-
client_security_id)
-
values (
-
'". $_data->{'scheme'} ."',
-
'". $_data->{'level_1'} ."',
-
'". $_data->{'level_2'} ."',
-
'". $_data->{'level_3'} ."',
-
'". $_data->{'client_security_id'}."') \n";
-
-
print " String Val $string \n";
-
-
$allstring = $allstring.$string;
-
# Check if $i is n * 1000
-
if ( !($i % 1000 ))
-
{
-
#write to pre_bcp_option_buffer
-
$retval = $_trg_dbh->exec( $allstring );
-
-
When the count reaches 1000, i.e. when i've formatted an insert string of 1000 rows i'm inseritng at one shot, before formatting the insert string i'm checking whether i've any null values in any of the columns. if it is there then i've to assign NULL that is undef (per PERL) to that column in the insert statement/string i'm formatting, coz its a string the undef is assigning ' ' into the db, but not a [NULL]. I expect a [NULL] in the DB so that firther my query can use isnull() to check for NULL values.
Since my program is inserting the ' ' the isnull() couldn't identify the NULL rows.
Thanks
Atif
Hi,
Thanks very much for you updated code.
Here is the way i've updated my code
code: -
-
my $a = "TEST A";
-
my $b = "TEST B";
-
my $c = "TEST C";
-
my $d = undef;
-
my $e = "TEST E";
-
-
-
my $string = sprintf("insert into %s..pre_bcp_hierarchy_buffer (client_scheme_code,
-
client_level_1_code,
-
client_level_2_code,
-
client_level_3_code,
-
client_security_id)
-
values (
-
%s,
-
%s,
-
%s,
-
%s,
-
%s) \n",
-
$_db_name,
-
(defined($a) ? "'" . $a . "'" : 'null'),(defined($b) ? "'" . $b . "'" : 'null'),(defined($c) ? "'" .
-
$c . "'" : 'null'),(defined($d) ? "'" . $d . "'" : 'null'),(defined($e) ? "'" . $e . "'" : 'null'));
-
-
my $retval = $_trg_dbh->exec( $string );
-
Thanks
ATIF
numberwhun 3,509
Recognized Expert Moderator Specialist
Hi,
Thanks very much for you updated code.
Here is the way i've updated my code
code: -
-
my $a = "TEST A";
-
my $b = "TEST B";
-
my $c = "TEST C";
-
my $d = undef;
-
my $e = "TEST E";
-
-
-
my $string = sprintf("insert into %s..pre_bcp_hierarchy_buffer (client_scheme_code,
-
client_level_1_code,
-
client_level_2_code,
-
client_level_3_code,
-
client_security_id)
-
values (
-
%s,
-
%s,
-
%s,
-
%s,
-
%s) \n",
-
$_db_name,
-
(defined($a) ? "'" . $a . "'" : 'null'),(defined($b) ? "'" . $b . "'" : 'null'),(defined($c) ? "'" .
-
$c . "'" : 'null'),(defined($d) ? "'" . $d . "'" : 'null'),(defined($e) ? "'" . $e . "'" : 'null'));
-
-
my $retval = $_trg_dbh->exec( $string );
-
Thanks
ATIF
Atif,
When you are posting to TSDN, please use code tags around your code. A series of "---" do not suffice to surround your code as they do not have the same effect.
In the "REPLY GUIDELINES" box to the right of the message window when replying contains the code tags that you should use. They have a beginning and end tag structure, just like html.
I have fixed all of your posts in this thread.
Regards,
Jeff
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Philip Boonzaaier |
last post by:
I want to be able to generate SQL statements that will go through a list of
data, effectively row by row, enquire on the database if this exists in the
selected table- If it exists, then the colums must be UPDATED, if not, they
must be INSERTED.
Logically then, I would like to SELECT * FROM <TABLE>
WHERE ....<Values entered here>, and then IF FOUND
UPDATE <TABLE> SET .... <Values entered here> ELSE
INSERT INTO <TABLE> VALUES <Values...
|
by: Carl |
last post by:
Hi,
I hope someone can share some of their professional advice and help me out
with my embarissing problem concerning an Access INSERT query. I have never
attempted to create a table with one-to-one relationship but on this
occasion
I must keep username/password details within a seperate table.
Here's the basic specs and database schema:
-------------------------------------------
|
by: authorking |
last post by:
I use the following code to insert a data record in to a datatable of an
access database.But every time I execute the command, there will rise an
exception and the insert operation can't be completed.
here's the code:
System.Data.OleDb.OleDbConnection hist_cnn = new
System.Data.OleDb.OleDbConnection();
System.Data.OleDb.OleDbCommand hist_command = new
System.Data.OleDb.OleDbCommand();
|
by: kosta |
last post by:
hello! one of my forms communicates with a database, and is
supposed to add a row to a table using an Insert
statement... however, I get a 'oledb - syntax error'
exception... I have double checked, and the insert works
fine (tried to use it from access)... im using visual C#
express 2k5... what could be wrong? thanks!
|
by: Shapper |
last post by:
Hello,
I have created 3 functions to insert, update and delete an Access
database record. The Insert and the Delete code are working fine.
The update is not. I checked and my database has all the necessary
records in it when testing it. I get the error "No value given for one
or more required parameters." when I try to update the database.
Can you tell me what am I doing wrong?
| |
by: David Bear |
last post by:
I have a dictionary that contains a row of data intended for a data base.
The dictionary keys are the field names. The values are the values to be
inserted.
I am looking for a good pythonic way of expressing this, but I have a
problem with the way lists are represented when converted to strings.
Lets say my dictionary is
|
by: Bonzol |
last post by:
Hello, PHP n00b here.
Using SQL just working off some examples, I have no problem selecting
data, but I cant seem to be able to insert. If someone could see where
im going wrong
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
|
by: Ben |
last post by:
I don't know whether anyone can help, but I have an odd problem. I have
a PSP (Spyce) script that makes many calls to populate a database. They
all work without any problem except for one statement.
I first connect to the database...
self.con = MySQLdb.connect(user=username, passwd =password)
self.cursor = self.con.cursor()
self.cursor.execute("SET max_error_count=0")
|
by: radiodes |
last post by:
I am very new to SQL/Access, and only use it very limitedly, so
apologies
ahead of time.
I have a website that inserts data into an access database, using the
code
below. Basically, I've got one INSERT INTO statement that inserts
one
line of data into one database, and and then a php for loop that
inserts multiple lines into a database with successive INSERT INTO
|
by: =?Utf-8?B?RXJpYyBGYWxza2Vu?= |
last post by:
We’re storing our main entity in an insert only table which stores the
history of past revisions, but we’re facing problems with storing this
history as LINQ will only update the entity, and not reinsert it with a
different revision number.
Compounding the issue, we’ve also got an associated table storing properties
for our entities which is not revisioned, but we still want changes to the
children of our entity (additions, changes...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |